The easiest way that I can think of to do this is with a VBA macro with this
command line:
time_entry = mid(("" & time_entry+10000),2,2) & ":" & right(("" &
time_entry+10000),2)
The reason for adding the 10000 is to insure a "0" for times that are
earlier than 10:00. The "" & is a cheap way to convert the number into a
string. The mid() function is then used to extract the 2nd and third digits
of the number string 1xxxx, which would give you a two digit hour value in
text form. The right() function extracts the two digit minutes value.
Suppose you had these time values in cells E5 to E11 (six time values).
This macro will give you the correct results:
Sub Time_Format()
Dim R As Object
For Each R In Range("e5:e11")
R = Mid(("" & R + 10000), 2, 2) & ":" & Right(("" & R + 10000), 2)
Next R
End Sub
I tried it and it works great. Excel even thinks they are time values when
done.
If you are unfamiliar with how to create a VBA macro and then execute it,
try this:
- With Excel running, hit ALT-F11. This opens the VBA editor.
- Open up any module that has a code area (i.e. a place to write code.). If
there are no modules, use the top menu to insert a new module.
- Copy this exact code into the module (except use your own range...).
- Flip back to the open Excel worksheet and hit ALT-F8.
- Select the Time_Format macro and execute it.
I advise you save your worksheet at least once before trying this.
I hope this helps.
shjco
