How can I insert a colon into a column of existing numbers

[Guest]

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

 

[Pete_UK]

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

 

[miketordoff]

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

Try:

=LEFT(A1,2) & ":" & RIGHT(A1,2)

This puts a colon between the left two numbers and right two numbers

Mike in Philadelphia

 

[Dave Peterson]

If 1845 is 00:18:45 (18 minutes, 45 seconds):
=--TEXT(A1,"00\:00\:00")

If 1845 is 18:45:00 (18 hours, 45 minutes):
=--TEXT(A1,"00\:00\:\0\0")

Format each as time.

 

[Ron Rosenfeld]

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

One way to convert to time:

=--TEXT(A1,"00\:00")

Format as Time

Then do a Edit/Copy Paste special/Values in place.
--ron

 

[ruchie]

well theres a tedious 'quick-fix' way out, if i may say

example:
Time
1200 =LEFT(A2,2) =RIGHT(A2,2) =CONCATENATE(B2, ":", C2)
1820 =LEFT(A3,2) =RIGHT(A3,2) =CONCATENATE(B3, ":", C3)
1500 =LEFT(A4,2) =RIGHT(A4,2) =CONCATENATE(B4, ":", C4)
1730 =LEFT(A5,2) =RIGHT(A5,2) =CONCATENATE(B5, ":", C5)

the answer will be:
Time
1200 12 00 12:00
1820 18 20 18:20
1500 15 00 15:00
1730 17 30 17:30