How to Calculate Time Differences in Excel and Google Sheets

How to Calculate Time Differences in Excel and Google Sheets

While developers swear by code, the business world runs on spreadsheets. One of the most common yet frustrating tasks in Excel or Google Sheets is calculating the time difference (duration) between two cells. Whether you're tracking hours worked, project delays, or shipping times, this guide covers the essential formulas.

1. The Basic Formula: Subtracting Dates

Excel stores dates and times as numbers. The number 1.0 represents one full day (24 hours). This makes basic math incredibly simple.

If A2 contains `Start Time` (e.g., 9:00 AM) and B2 contains `End Time` (e.g., 5:00 PM):

Result: `0.33` (which is 8 hours as a fraction of a day).

Fix format: Press `Ctrl + 1` (Format Cells), choose Time, and select a format like `13:30` (h:mm).

2. The "Negative Time" Error (######)

If you subtract a later time from an earlier time (e.g., ending a night shift the next morning), Excel might show a row of hash marks `######`. This is because Excel handles dates as positive serial numbers by default.

Solution for Night Shifts (Overnight calculation)

If you work from 10:00 PM to 6:00 AM, the simple subtraction `6:00 AM - 10:00 PM` gives a negative result. Use this formula to automatically handle the day wrap:

How it works: The logic `(B2<A2)` returns TRUE (1) if the end time is smaller (overnight), adding 1 full day (24 hours) to the result. It returns FALSE (0) otherwise.

3. Calculating Total Hours (Greater than 24h)

If you are summing up a timesheet and the total exceeds 24 hours (e.g., 35 hours worked in a week), Excel's default formatting might "roll over" and show `11:00` instead of `35:00`.

The Fix: Use the Custom Format [h]:mm.

• Select the total cell.
• Right Click > Format Cells.
• Select "Custom".
• Type [h]:mm in the box. Biackets `[]` tell Excel not to roll over after 24 hours.

4. Advanced: NETWORKDAYS function

Need to calculate the number of business days between two dates, excluding weekends and holidays?

• A2: Start date
• B2: End date
• [holidays_range]: (Optional) A range of cells containing holiday dates to exclude.

Conclusion

You don't need complex VBA scripts to handle time in Excel. By mastering the [h]:mm format and the "night shift formula", you can build robust timesheets and project trackers in seconds.