/ How to

How to Calculate Durations Between Times Like a Pro in Google Sheets

Calculating durations between times is an essential skill for anyone using Google Sheets for scheduling, time tracking, billing, or any other time-based tasks. While the basic skills are simple, truly mastering time calculations requires understanding Google Sheets’ date and time features at an advanced level. In this comprehensive guide, you’ll learn pro tips for calculating durations, formatting times, handling times after midnight, totaling hours across days, and more.

The Basics: Subtract Start Time from End Time

The most basic way to find the duration between two times is to simply subtract the later time from the earlier time.

For example, if you have a start time of 8:30 AM in cell A2 and an end time of 11:45 AM in B2, use this formula:

=B2-A2

This gives you the duration in terms of fraction of a day. To see the duration in hours, minutes and seconds:

  1. Select the cell with the formula
  2. Click Format > Number > Duration

You’ll now see the duration formatted as 3:15:00 (3 hours 15 minutes 0 seconds).

The same method works if your times span midnight. For example, if you have a start time of 11:00 PM and an end time of 2:30 AM, the duration will show correctly as 3:30:00.

Pro Tip 1: Use Absolute References for Copy/Paste

To use the same duration formula across multiple rows, use absolute references for the start and end times:

=B$2-A$2

You can now copy this formula to multiple rows, and the cell references will stay fixed instead of changing.

Pro Tip 2: Handle Durations > 24 Hours

The duration format in Google Sheets works for durations under 24 hours. For longer durations, you need to take an extra step:

  1. Calculate the raw duration using =B2-A2
  2. Format the cell as Number (instead of Duration)
  3. Multiply by 24 to convert from days to hours

For example, if your start date is June 1 and end date is June 3, the formula would be:

=(B2-A2)*24

This displays the duration as 72 hours.

Pro Tip 3: Total Hours Across Multiple Days

To total up time entries across multiple days, such as for weekly time sheets:

  1. Format the cells as Duration
  2. Use SUM to add up the individual durations

For example, if you have durations in E2 through E12, use this formula:

=SUM(E2:E12)

This provides the total hours worked across those rows.

Pro Tip 4: Handle Times After Midnight

When times span midnight, Google Sheets handles the duration correctly when you apply the Duration format. But you can also break out the before and after midnight durations separately:

=IF(A2<B2, B2-A2, (1-A2) + B2)

This checks if the start time is before the end time. If yes, it calculates the straightforward duration. If no, it splits into two durations: before and after midnight.

Date and Time Formatting Refresher

Since time calculations depend so much on formatting, let’s review the key date and time formatting options in Google Sheets:

Date Formatting

  • Date: 1/1/2023
  • Date & Time: 1/1/2023 12:00:00 PM
  • Custom Date/Time: MMM d, yyyy hh:mm:ss (any elements in any order)

Time Formatting

  • Time: 12:00:00 PM
  • Duration: 12:34:56 (hours, minutes, seconds)

Tips

  • Use Format > Number menu to access formats
  • Select format first, then enter times
  • Times are actually decimal numbers representing fraction of day

Advanced Time Functions

In addition to subtracting times, Google Sheets includes functions to extract time components:

=HOUR(A2) // Returns just the hour 
=MINUTE(A2) // Returns just the minute
=SECOND(A2) // Returns just the second

You can combine these to reconstruct times:

=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

This recreates the full time from its parts.

Putting It All Together

With the basics, pro tips, formatting knowledge, and advanced functions covered above, you now have all the tools to handle time calculations like a pro!

You can efficiently calculate durations, handle midnight crossovers, total hours across days, extract time components, reformat times, and more.

The key is understanding how Google Sheets stores dates and times internally so you can apply the right formats and formulas. With practice, you’ll be able to model even the most complex scheduling and time tracking scenarios.

So grab a sample spreadsheet and start testing out these time calculation techniques today!

Related Posts