Date Problem

[Guest]

I am using the following formula:

With ActiveSheet
intMaxRow = .UsedRange.Rows.Count
Set rngStart = .Range("E1")
For intCtr = 1 To (intMaxRow - 1)
Set rngCell = rngStart.Offset(RowOffset:=intCtr)
If IsNumeric(rngCell) And rngCell.Value > 0 Then
On Error Resume Next
rngCell.Value = DateValue(Int(rngCell / 100) & "/1/" & rngCell Mod
100)
rngCell.NumberFormat = "mmm - yy"
On Error GoTo 0
End If
Next intCtr
End With
Set rngStart = Nothing
Set rngCell = Nothing
End Sub

Now it works great for what It was created for. However, there is something
fishy that is making it react differently then I want in another instance.

It takes data in a cell that is imported from elsewhere as a number EX: 406
and turns it into the proper date Apr - 06, or 1205 to Dec - 05 SO The
values are either 3 characters OR 4 and it works perfectly.

Now I used it on another column to try for the same results, this was my
result. The current data within the cells are all 4 characters SO ex:
1004 is supposed to be Oct - 04 or 0186 is supposed to be Jan - 86
Now I thought that formula would work the same on that column, BUT my end
result was this:

0186 turned into 1/1/1986
1004 into 10/1/2004

What's up? I want it to look like the other.....Oct - 04 and Jan - 86, etc.


Thanks in advance for the help!!!

 

[Bob Phillips]

It worked for me, but try this less ambiguous way

With ActiveSheet
intMaxRow = .UsedRange.Rows.Count
Set rngStart = .Range("E1")
For intCtr = 1 To (intMaxRow - 1)
Set rngCell = rngStart.Offset(RowOffset:=intCtr)
If IsNumeric(rngCell) And rngCell.Value > 0 Then
On Error Resume Next
rngCell.Value = DateSerial(rngCell Mod 100, Int(rngCell / 100), 1)
rngCell.NumberFormat = "mmm - yy"
On Error GoTo 0
End If
Next intCtr
End With
Set rngStart = Nothing
Set rngCell = Nothing

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[Guest]

Strange how it keeps coming up like that for me. It must be something with
how the data is imported. I'll continue to play around, see what is up.
Thanks for the tip!!! Any ideas as to why it may be doing that?

 

[Bob Phillips]

I thought that it was not building a proper date string, but as I didn't get
it, I couldn't prove my idea.

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[Guest]

I got it. Simple, all I have to do is format the column into a General
format, then apply the formula and presto change-o !!! So I only had to add
two little lines to the top of that formula. Thanks for the less ambiguous
formula though, HUGE help!!!