reformating date
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()).
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
" wrote:
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
|