How can I insert a colon into a column of existing numbers
If the numbers are always 4 digits you can use this:
=VALUE(LEFT(A1,2)&":"&RIGHT(A1,2)&":00")
and copy down as required.
However, I would expect you to have 3-digit times (eg 930 to represent
9:30) and even 2-digit times for those before 1:00 am, so a safer bet
would be the following:
=VALUE(LEFT(A1,LEN(A1)-2)&":"&RIGHT(A1,2)&":00")
and even this won't work if you have one-digit times, eg 5 to
represent 00:05, or 5 minutes past midnight, so the following will
cope with all these situations.
=VALUE(INT(A1/100)&":"&MOD(A1,100)&":00")
where A1 contains your value - adjust as necessary. Format the cell as
time and then copy down.
Hope this helps.
Pete
On Jun 7, 5:00 pm, jcmonzon
wrote:
I have two columns of numbers which are suppose to represent times, but were
entered as a number format. Example: "1845" instead of 18:45. I'm looking
for a way to insert the colon into the numbers so I can do a time calculation.
Note: formating the columns as Time does not work because of the way Excel
handles date & time in a serial manner.
Thanks
|