How do I enter elapsed time in a cell?

[Cliff Young]

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.

[#2]

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.

[#3]

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!

[#4]

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...