Converting 4-digit number to time format

Posted by Yosef on July 16, 2000 3:08 PM

I have a table of numbers that represent times of the day. The numbers are currently in the format of 0720 representing 7:20am or 1350 representing 1:50pm. I want to change them to read 7:20 or 13:50 so that I can compute differences between them in actual minutes (i.e. the midway point between 850 and 910 should be 900 as in 9:00, not 880). I attempted to record a macro in which I pressed F2 to edit, pressed the left arrow twice, typed a colon and then pressed enter. This used to do the trick on Quattro, but it doesn't work on Excel. Instead of recording my keystrokes, excel's Visual Basic editor recorded that I wanted the cell to read "7:20" so when I pressed Ctrl-T (the designated macro-activation sequence) it changed the current cell to "7:20" (no matter what currently existed in the cell) and moved the cursor to the cell at which I recorded the macro. To illustrate, do the following and you'll see what I mean.

Enter the following numbers in the following cells: Cell A1 - 720, Cell B1 - 1350, Cell C1 - 1240, Cell D1 - 2230. Move the cursor to Cell A1 and creat a macro that will change 720 into 7:20. Now move the cursor onto cell B1 and execute the macro. Does the cell read 13:50 or 7:20? Where is the cursor after you execute the macro?

Suggestions?

Posted by Ivan Moala on July 17, 0100 1:56 AM

Yosef
The above will work if your string lenght is 4
In your example you could have 3 eg 730.
Therefore try this adj.

=CHOOSE(LEN(B1),,,LEFT(B1,1) & ":" & RIGHT(B1,2),LEFT(B1,2) & ":" & RIGHT(B1,2))

This assumes B1 has your times

Or from code;

Sub ChangeTime()
Select Case Len(ActiveCell)
Case 4
ActiveCell.FormulaR1C1 = Left(ActiveCell, 2) & ":" & Right(ActiveCell, 2)
Case 3
ActiveCell.FormulaR1C1 = Left(ActiveCell, 1) & ":" & Right(ActiveCell, 2)
Case Else
MsgBox "Not Valid!"
End Select
End Sub

Ivan

Posted by Yosef on July 18, 0100 6:30 AM

Thank you Ryan and Ivan. I used Ivan's coding method because of the 3-digit/4-digit issue.

I attempted to convert all the cells en masse by highlighting them and executing the macro, but it only converted the first cell.

Suggestions?

Yosef

Posted by Ryan on July 18, 0100 11:27 AM

The code provided by Ivan only works on one cell. If you want to highlight the cells and then run the macro change it to this:

Sub ChangeTime()
For Each Cell in Selection

Select Case Len(Cell)
Case 4
Cell.FormulaR1C1 = Left(Cell, 2) & ":" & Right(Cell, 2)
Case 3
Cell.FormulaR1C1 = Left(Cell, 1) & ":" & Right(Cell, 2)
Case Else
MsgBox "Not Valid!"
End Select
Next Cell
End Sub

Posted by Ryan on July 16, 0100 3:30 PM

Yosef,

You can either enter in extra columns and use this formula, and copy it down the cells.
=LEFT(A1,2) & ":" & RIGHT(A1,2)
or use this procedure:

If you highlight the cells in a column and then run this macro. Then go to the next column and do the same thing, it will all work out!
Let me know how it turns out!

Ryan

Sub ChangeToTime()
For Each Cell In Selection
Cell.Value = Left(Cell.Value, 2) & ":" & Right(Cell.Value, 2)
Next Cell
End Sub