” How can I enter a duration in Excel with the format days:hours:minutes:seconds ?
If I enter 5:32 Excel converts that to 5:32am but I want to record 5 hours and 32 minutes.”
Time durations aren’t very clear in Excel, but they are possible.
The time features are mostly aimed towards date and time so the duration options aren’t obvious.
Custom Formatting
The secret to durations in Excel is in the Custom Formatting. Home | Number | Format Cells.
Down in the Custom formatting are the duration options:
- h:mm
- h:mm:ss
- mm:ss
- mm:ss.0
- [h]:mm:ss
And there’s some others you might need to add (we’ll explain below)
- d:hh:mm
- d:hh:mm:ss
- [h]:mm;@
No, they aren’t in a separate section of Format Cells, nor are they labelled in any way. You’re forgiven for not understanding what they are for.
Now when you type 5:32 into a cell with the h:mm formatting it will show as a duration, though the function line will still show as a time.
With duration in that format you can do time arithmetic like addition or subtraction:
Days or Hours?
You can see a problem in Column C with a total of 00:38 that’s clearly wrong. The problem is that the total is over 24 hours and the current format only shows hours (h:mm).
There’s two possible solutions using Custom Formatting.
One is to add days to the custom format: d:h:mm so results appear as 1:00:38
The other is to let Excel show hours above 24 using this format [h]:mm;@ with the result 24:38
Here’s the difference in practice:
Entering durations
To enter a duration, type the hours, minutes and seconds with a colon separating each. If you have the duration formatting current, the value will be formatted to the right of the cell.
If the formatting or typing is wrong, the cell will be left aligned.
Hours, minutes and seconds only
Excel will only accept data entry in hours:minutes:seconds. While it can display days (using the d:h:mm format) it won’t accept a day entry value.
To enter the days you have to multiply by 24 and add to the hours before typing in.
If you want to enter 1 day, 2 hours and 30 minutes, you have to type 26:30 then, using the right format it will show up as 1:02:30
If you have many long durations to enter, brush up on your ’24 times table’.
Dates, time and duration, the truth behind them in Excel
Text to Excel Date conversion by adding Zero with Paste Special
Simple text with day, month and year to Excel date conversion
Converting Text with month and year into Excel dates
Date formatted charts in Excel