Spreadsheet Practice

How to show a duration as days, hours and minutes in Excel

Formula (Excel and Google Sheets)

=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")

Take the whole days with INT and format the rest with TEXT: =INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""").

Result on the example
one result per row in D2, filled down to D6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.

How it works

INT(C2-B2) is the whole days between the two date-times. TEXT then formats the leftover fraction with hour and minute codes, where the doubled quotes put literal letters into the pattern: 2.15 days becomes "2d 3h 30m".

=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")

Result"2d 3h 30m"

  1. C23/4/2026 11:45

    The value in C2.

  2. B23/2/2026 8:15

    The value in B2.

  3. C2-B22.145833333

    The - operator subtracts.

  4. INT(C2-B2)2

    INT: Rounds down to the nearest integer.

    • numberC2-B22.145833333
  5. INT(C2-B2)&"d ""2d "

    The & operator joins as text.

  6. C2-B22.145833333

    The - operator subtracts.

  7. TEXT(C2-B2,"h""h ""m""m""")"3h 30m"

    TEXT: Formats a number or date as text, e.g. "mmm" or "$#,##0".

    • valueC2-B22.145833333
    • format_text"h""h ""m""m"""
  8. INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")"2d 3h 30m"

    The & operator joins as text.

In Google Sheets

The same formula works in Google Sheets: =INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m"""). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INT(C2-B2)&"d "&INT(MOD(C2-B2,1)*24)&"h "&ROUND(MOD((C2-B2)*1440,60),0)&"m"2d 3h 30m, 0d 2h 55m, 7d 0h 20m…Different resultBuilds the parts with arithmetic instead of a format code. It rounds the minutes, where TEXT drops the seconds. Can be a minute higher when the times include seconds.

Common mistakes

  • The "d" code in TEXT is a day of the month, not a count of days, so it breaks past 31 days. Use INT for the days, as here.
  • The result is text. Keep the raw difference in another column for sorting and averaging.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Show a duration as days, hours and minutes

In D2, show the time from opened to closed as text like "2d 3h 30m", and fill down to D6.

Questions

How do I show total hours and minutes instead, like 51:30?

Use the custom format [h]:mm on the difference, or =INT((C2-B2)*24)&":"&TEXT(MOD((C2-B2)*1440,60),"00").

How do I leave out zero days?

=IF(INT(C2-B2)=0,"",INT(C2-B2)&"d ")&TEXT(C2-B2,"h""h ""m""m""").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.