Excel VBA to force numeric value into time format

Question

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 1

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.

Solution 2

You can use VBA to accomplish the same effect as Steve Homer's answer by setting the range's value property to itself:

Dim r As Range
Set r = Sheets("Sheet1").Columns("B:B")
r.Value = r.Value

Solution 3

You can do this with out declare TMP as DATE:

TMP = CDate(Range("A1").Value) ' we suppose A1 value is 12:45:00
TMP = "" & TMP ' convert to char
TMP = Left(TMP, 5) ' extract the first 5 char
MsgBox TMP ' for displaying 12:45