reformating date

[kieranish]

Excel has misinterpretted the date values from a web query - so Feb-08
is 01/02/08 rather than 08/02/07. The problem is that if I use VBA to
correct this it uses the underlying excel date value (ie 39479) rather
than the cell contents.

I want to do something like this - but on the cell contents:

Public Sub fixdates()
Application.ScreenUpdating = False
For Each Cell In ActiveSheet.UsedRange.Columns("h:h").Cells
Cell.Value = DateValue(Right(Cell.Value, 2) & "/02/07")
Next Cell
Application.ScreenUpdating = True
End Sub

Any suggestions?

Thanks in advance,

Kieran

 

[Guest]

Hi Kieran:

Try this:

cell.Value = DateSerial(2007, 2, Right(cell.Text, 2))

You may also want to automate the fixdates a little with checking for the
year and the month.

as in month is month(datevalue("1/" & left(cell.text,3) & "/2006"))
and that can fit in the above in place of the 2 after 2007.
For year just use year(now()).