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

To insert a colon into a column of existing numbers, follow these steps:

1. Insert a new column next to the column of numbers you want to format.
2. In the first cell of the new column, enter the formula
   Code:

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

   (assuming your original numbers are in column A). This formula will take the left part of the number (everything except the last two digits), add a colon, and then add the right part of the number (the last two digits).
3. Copy the formula down to the rest of the cells in the new column.
4. Select the new column and copy it.
5. Right-click on the original column of numbers and select "Paste Special".
6. In the "Paste Special" dialog box, select "Values" and click "OK". This will replace the original numbers with the formatted numbers.

That's it! Now you should have a column of numbers with colons inserted between the hours and minutes.

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

On Jun 7, 12: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

Try:

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

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

Mike in Philadelphia

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.

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

--

Dave Peterson

On Thu, 7 Jun 2007 09:00:00 -0700, 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

One way to convert to time:

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

Format as Time

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

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