Search code examples
vbaexcelexcel-2007

Excel VBA to force numeric value into time format

There are two columns copied into a workbook from a Access database.

I want to ensure that the data is formatted correctly so I added this code:

'DateTime Column
Sheets("Sheet1").Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy hh:mm"

'Time Column
Sheets("Sheet1").Columns("B:B").Select
Selection.NumberFormat = "hh:mm"

The datetime column formats correctly.
The time column is initially copied as a numeric equivalent (ie 0.595277777777778) instead of the time value (14:17).

Running the macro code does nothing to the visual display and it isn't until I hit F2 and enter on the cell that the format applies.

Is there a method (short of a loop) to force Excel to apply the formatting?

Solution

  • If you copy the column and paste as values (through a macro if needs be) then Excel should re-interpret the data type and the above should work correctly.