Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reformating date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Reformating Question | Excel Discussion (Misc queries) | |||
Pivot tables reformating | Excel Discussion (Misc queries) | |||
Reformating a column and getting it to apply | New Users to Excel | |||
How can I defeat Excel's auto-reformating into date format? | Excel Discussion (Misc queries) | |||
reformating worksheet | Excel Programming |