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?