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

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

**Excel:** `=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Incident | Opened | Closed | Duration |
| INC-71 | 46083.34375 | 46085.489583333336 |  |
| INC-72 | 46084.92361111111 | 46085.04513888889 |  |
| INC-73 | 46086.375 | 46093.38888888889 |  |
| INC-74 | 46087.604166666664 | 46087.631944444445 |  |
| INC-75 | 46090.739583333336 | 46092.260416666664 |  |

Formula in D2, filled to D6: `=INT(C2-B2)&"d "&TEXT(C2-B2,"h""h ""m""m""")`

Result: 2d 3h 30m; 0d 2h 55m; 7d 0h 20m; 0d 0h 40m; 1d 12h 30m

## 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".

## Other ways

- `=INT(C2-B2)&"d "&INT(MOD(C2-B2,1)*24)&"h "&ROUND(MOD((C2-B2)*1440,60),0)&"m"`: Builds 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.

## 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""")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/show-a-duration-as-days-hours-and-minutes