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