How do I enter elapsed time in a cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-63740%22%20slang%3D%22en-US%22%3EHow%20do%20I%20enter%20elapsed%20time%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-63740%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20compare%20various%20elapsed%20times%20in%20a%20spreadsheet.%20Excel%20keeps%20interpreting%20this%20as%20a%20date.%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20a%20time%20of%2043%20minutes%20and%200%20seconds.%20If%20I%20set%20the%20cell%20number%20format%20to%20Time%2C%20%2243%3A00%22%20gets%20displayed%20as%20%221%2F1%2F1900%26nbsp%3B%207%3A00%3A00%20PM%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20creating%20a%20custom%20format%20of%20%5Bmm%5D%3Ass%2C%20based%20on%20Office%20Support%3A%20%22Elapsed%20time%20in%20minutes%3B%20for%20example%2C%2063%3A46%20-%20%5Bmm%5D%3Ass%22.%20but%20this%20converts%20%2243%3A00%22%20to%20%222580%3A00%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20that%20I%20could%20convert%20all%20my%20times%20to%20decimal%20and%20use%20a%20number%20format%2C%20but%20isn't%20the%20whole%20point%20of%20Excel%20that%20it%20does%20this%20sort%20of%20thing%20for%20you%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20like%20this%20should%20be%20easy%2C%20but%20I'm%20not%20getting%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-63740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-75143%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20enter%20elapsed%20time%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-75143%22%20slang%3D%22en-US%22%3E%3CP%3EMost%20complex%20thing%20for%20me%20is%20to%20remember%20what%2012%3A00AM%20is%20midnight%20and%2012%3A00PM%20is%20the%20noon%2C%20not%20vice%20versa.%20Even%20with%20Fahrenheits%20is%20easier%20when%20I%20adjust%20air%20conditioning%20in%20the%20hotel...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-75126%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20enter%20elapsed%20time%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-75126%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20having%20the%20same%20trouble%20finding%20a%20solution.%20I%20wanted%20to%20enter%20one%20minute%20and%2042%20seconds%20of%20elapsed%20time.%20I%20formatted%20the%20cell%20to%20%5Bmm%5D%3Ass%20and%20entered%201%3A42.%20The%20resulting%20display%20was%20102%3A00.%20Not%20what%20I%20was%20looking%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20found%20that%20by%20entering%200%3A1%3A42%20I%20would%20get%20my%20desired%20result%20of%201%3A42.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20tell%20you%20how%20much%20time%20I%20wasted%20trying%20to%20find%20this%20answer...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20found%20it%20already!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-63752%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20enter%20elapsed%20time%20in%20a%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-63752%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Cliff%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEverything%20is%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E43%3A00%20is%20shown%20in%20the%20cell.%3C%2FP%3E%3CP%3E1%2F1%2F1900%26nbsp%3B%207%3A00%3A00%20PM%20is%20shown%20in%20the%20formula%20bar.%20The%20date%20is%20shown%20when%20the%20time%20is%2024%20hours%20or%20greater.%3C%2FP%3E%3CP%3E1%2F1%2F1900%2012%3A00%20AM%20is%20the%20start%20of%20Excels%20date%20and%20time.%3C%2FP%3E%3CP%3E43%20hours%20after%20this%20is%201%2F1%2F190%207%3A00%20PM.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChanging%20the%20format%20to%20%5Bmm%5D%3Ass%20changes%20the%20cell%20value%20to%202580%3A00.%3C%2FP%3E%3CP%3E43%20hours%20x%2060%20minutes%20%3D%202580%20minutes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.

 

3 Replies

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.

 

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!

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