Forum Discussion
How do I enter elapsed time in a cell?
I'm trying to compare various elapsed times in a spreadsheet. Excel keeps interpreting this as a date.
For example, I have a time of 43 minutes and 0 seconds. If I set the cell number format to Time, "43:00" gets displayed as "1/1/1900 7:00:00 PM".
I tried creating a custom format of [mm]:ss, based on Office Support: "Elapsed time in minutes; for example, 63:46 - [mm]:ss". but this converts "43:00" to "2580:00".
I understand that I could convert all my times to decimal and use a number format, but isn't the whole point of Excel that it does this sort of thing for you?
Seems like this should be easy, but I'm not getting it.
Most complex thing for me is to remember what 12:00AM is midnight and 12:00PM is the noon, not vice versa. Even with Fahrenheits is easier when I adjust air conditioning in the hotel...
I was having the same trouble finding a solution. I wanted to enter one minute and 42 seconds of elapsed time. I formatted the cell to [mm]:ss and entered 1:42. The resulting display was 102:00. Not what I was looking for.
I found that by entering 0:1:42 I would get my desired result of 1:42.
I can't tell you how much time I wasted trying to find this answer...
I hope you found it already!
Hello Cliff
Everything is correct.
43:00 is shown in the cell.
1/1/1900 7:00:00 PM is shown in the formula bar. The date is shown when the time is 24 hours or greater.
1/1/1900 12:00 AM is the start of Excels date and time.
43 hours after this is 1/1/190 7:00 PM.
Changing the format to [mm]:ss changes the cell value to 2580:00.
43 hours x 60 minutes = 2580 minutes.
