Calculating the time difference between two timestamps in Google Sheets is a fundamental skill for project management, billing, and data analysis. Whether you are tracking hours worked, measuring task duration, or analyzing event intervals, mastering these formulas ensures accuracy and efficiency. The core of these calculations relies on understanding how Sheets stores date and time values as serial numbers.
Understanding Date-Time Serial Numbers
Before diving into specific formulas, it is essential to grasp how Google Sheets interprets dates and times. In the background, every date is stored as an integer representing the number of days since December 30, 1899. Time is stored as a decimal fraction of a 24-hour day. For example, 6:00 AM is represented as 0.25. Consequently, a datetime like 2023-10-27 18:00:00 is stored as a serial number like 45212.75. When you subtract an earlier datetime from a later one, the result is the difference in these serial numbers, which you can then format as duration.
Basic Subtraction for Time Difference
The most straightforward method to find the difference between two times is simple subtraction. If cell A2 contains a start time and cell B2 contains an end time, the formula `=B2-A2` will yield the elapsed time. However, the result will likely appear as a decimal or an unreadable date format. To display the result correctly, you must change the cell formatting to either Duration or Time. Right-click the result cell, choose "Format cells," and select "Duration" to see the result in hours, minutes, and seconds.
Handling Cross-Midnight Durations
When the end time is later than the start time but occurs on the next day (e.g., start 10:00 PM, end 2:00 AM), a simple subtraction may yield a negative value or an incorrect assumption that the event ended before it started. To handle this, you can force the calculation to assume the end time is on the next day. Use the formula `=IF(B2<A2, B2+1, B2)-A2`. This checks if the end time is smaller (earlier) than the start time; if so, it adds one day (1.0) to the end time before subtracting, ensuring a positive duration.
Extracting Specific Units: HOURS, MINUTES, SECONDS
While the duration format is useful for displaying total time, you often need to extract specific integer units for calculations or reporting. Google Sheets provides dedicated functions for this purpose. Use the `HOUR`, `MINUTE`, and `SECOND` functions to get the specific unit from a time value. Note that `HOUR` returns the hour of the day (0-23), not the total duration in hours. For total duration, use `INT` to get days and multiply the remainder by 24.
Calculating Total Hours and Minutes
Total Hours: `=(B2-A2)*24` — Multiplies the decimal day difference by 24 to get total hours.
Total Minutes: `=(B2-A2)*1440` — Multiplies by 1440 (24*60) to get total minutes.
Using HOUR/MINUTE: `=HOUR(B2-A2)` and `=MINUTE(B2-A2)` — These work for differences less than 24 hours but may fail for larger spans.
