Entering time durations 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.

entering time durations in excel 4864 - Entering time durations in Excel

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.

entering time durations in excel 4865 - Entering time durations in Excel

With duration in that format you can do time arithmetic like addition or subtraction:

entering time durations in excel 4866 - Entering time durations in Excel

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

entering time durations in excel 4867 - Entering time durations in Excel

Here’s the difference in practice:

entering time durations in excel 4868 - Entering time durations in Excel

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.

entering time durations in excel 4869 - Entering time durations in Excel

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

entering time durations in excel 4871 - Entering time durations in Excel

If you have many long durations to enter, brush up on your ’24 times table’.

subs profile e1563205311409 - Entering time durations 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