Software

Ask something
Discussion Locked

Time format in excel

0 helpful
9 replies

by michaeldanielwinn

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Does anyone know how to format a cell in excel for TIME so that we do not have to add the colon in the time I.E. 1045 for 10:45? Keep in mind that we must add up the time so doing it under the GENERAL format will not work.

9 replies

by mslizny

The colon separates hours from minutes, even with "Date" formatting. If you want to use hours and minutes, you will have to type in the colon to separate the minutes from the hours.

by d. brinson

You could try to enter it as 1045 in General format, and then convert the general format to time format to do your summation.

=CONCATENATE(IF(LEN(A1)=4,LEFT(A1,2),LEFT(A1,1)),":",RIGHT(A1,2))

will take a general text screen in A1
and convert it to a text string
1045 = 10:45
945 = 9:45
0945 = 09:45

You can then use the value function to convert that to a decimal value which is how Excel thinks of time.

value(A2)

Hope it helps.

by dklippert

You can further refine the formula by combining them:
=VALUE(CONCATENATE(IF(LEN(A1)=4,LEFT(A1,2),LEFT(A1,1)),":",RIGHT(A1,2)))
You will then need to format the cell as Time.

by andy1957

If you just want the time format to be HHMM without the colon, why not just "Format Cell Number Custom HHMM"? I've just tried this and it works.

This discussion is closed to new replies.