How to calculate hours between two times
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Get the duration between a start and end time — for timesheets, shifts, or logs.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =TEXT(B2-A2,"h:mm") | Or =(B2-A2)*24 for a decimal number of hours (7.25 for 7h15m). |
| Google Sheets | =TEXT(B2-A2,"h:mm") | Identical. |
| LibreOffice Calc | =TEXT(B2-A2,"H:MM") | Format codes are case-insensitive here. |
How it works
Times are stored as fractions of a day, so subtracting start from end gives the duration directly — 9:00 to 17:15 is 8:15. Multiply by 24 instead when you need decimal hours for pay calculations: 8.25. For shifts that cross midnight, use =MOD(B2-A2,1) so the negative wrap-around comes out right; and if a total might exceed 24 hours, format the cell as [h]:mm rather than using TEXT.
Verified, not just documented
We ran =TEXT(TIME(17,15,0)-TIME(9,0,0),"H:MM") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 8:15 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 8:15 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
TEXT — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to abbreviate large numbers as K and M
- How to add leading zeros to a number
- How to concatenate a date with text (without getting a number)
- How to convert 12-hour text times to 24-hour
- How to convert a date to text