Time durations are possible in Excel

Office-Watch.comLast updated: 15 May 2021

Microsoft 365,Microsoft Excel,Microsoft Office for Mac,Office 2007,Office 2010,Office 2013,Office 2016,Office 2019,Office 2021 / Office LTSC,Office 365

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