Time durations are possible in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

” 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

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address