Forum Discussion
Time Formatting in Excel
I'm trying to create a simple spreadsheet to log times in Excel.
I select the cells to be formatted and apply a TIME format e.g. 13:30
I would would like to be able to type 1330 and get the 13:30 mentioned above.
The problem I have is that excel places DATE before TIME in the cell so I when I type in 1330 the result I get is 00:00:00.
How can I remove the date section of this format and then in return the correct Time format?
I will also need to calculate durations between Times to give me an hours and minutes result.
Any information you can give would be much appreciated.
4 Replies
I am not sure if you want it like this:
change the number format as custom0#":"##
but this is not a time format to Excel
- Thanks for the response guys.
Will both of these options allow me to calculate the duration in hours and minutes between a time start cell and time finish cell?=IF(AND(<StartTime><=TIMEVALUE(TEXT(<TheCell>,"0#"":""##")),<EndTime>>=TIMEVALUE(TEXT(<TheCell>,"0#"":""##"))),TRUE,FALSE)
This is just the workaround.
In fact, AFAIK, there is no solution to manually input a integer value in a cell to be treated as a time value in excel.
If you type 130, it shows as 0130. However, if you do
=LEFT(<TheCell>,2)
It come out 13.
You must do it like
=LEFT(TEXT(<TheCell>,"0000"),2)
or
=TEXT(INT(<TheCell>/100),"00")
to make it come out with 01.
