Calculating time in Google Sheets: subtract, sum and extract date and time units

Now, that we've learnt how to enter dates and time to your spreadsheet, it's time to talk about the ways of calculating time in Google Sheets. We'll discuss the ways of finding time difference in detail, see how to sum dates and time together, and learn to display only date or time units and set them apart completely.

• How to calculate time difference in Google Sheets
• How to add and subtract time in Google Sheets: hours, minutes, or seconds
• Sum time in a Google spreadsheet
• Extract date and time from a full "date-time" record

How to calculate time difference in Google Sheets

When you're working on some projects, it is usually important to control how much time you spend. This is called elapsed time. Google Sheets can help you calculate the time difference in a lot of various ways.

Example 1. Subtract time to get the time duration in Google Sheets

If you have your start time and end time, it's not a problem to find out the time spent:

Let's assume the start time is in column A and the end time is in column B. With a simple subtraction formula in C2, you will find how much time this or that task took:

=B2-A2

The time is formatted as "hh:mm" by default.

To get the results as hours only or as hours, minutes, and seconds, you need to apply a custom format with the corresponding time codes: h and hh:mm:ss. Google even offers a special number format for cases like this - Duration:

Example 2. Calculate time duration in Google Sheets using the TEXT function

Another trick to calculate the time duration in Google Sheets involves the TEXT function:

=TEXT(B2-A2,"h") - for hours
=TEXT(B2-A2,"h:mm") - for hours and minutes
=TEXT(B2-A2,"h:mm:ss") - for hours, minutes, and seconds

Example 3. Time difference in hours, minutes, and seconds

You can track the time spent and get the result in one time unit disregarding other units. For example, count the number of only hours, only minutes, or only seconds.

• To get the number of hours spent, subtract your start time from the end time and multiply the result by 24 (since there are 24 hours in one day):

  =(End time - Start time) * 24

  You will get a time difference as a decimal:

  

  If the start time is greater than the end time, the formula will return a negative number, like in C5 in my example.

  Tip. The INT function will let you see the number of complete hours spent since it rounds numbers down to the nearest integer:

  

• To count minutes, substitute the start time from the end time and multiply whatever you get by 1,440 (since there are 1,440 minutes in one day):

  =(End time - Start time) * 1440

  

• To find out how many seconds passed between two times, the drill is the same: substitute the start time from the end time and multiply the result by 86,400 (the number of seconds in a day):

  =(End time - Start time) * 86400

  

Example 4. Functions to get the time difference in a Google spreadsheet

As always, Google Sheets equips you with three particularly useful functions for this purpose.

• =HOUR(B2-A2) - to return hours only (without minutes and seconds)
• =MINUTE(B2-A2) - to return minutes only (without hours and seconds)
• =SECOND(B2-A2) - to return seconds only (without hours and minutes)

How to add and subtract time in Google Sheets: hours, minutes, or seconds

These operations can also be achieved with two techniques: one involves basic math calculations, another - functions. While the first way always works, the second one with functions works only when you add or subtract units less than 24 hours, or 60 minutes, or 60 seconds.

Add or subtract hours in Google Sheets

• Add less than 24 hours:

  =Start time + TIME(N hours, 0, 0)

  Here's how the formula looks on real data:

  =A2+TIME(3,0,0)

  

• Add more than 24 hours:

  =Start time + (N hours / 24)

  To add 27 hours to the time in A2, I use this formula:

  =A2+(27/24)

  

• To subtract 24 and more hours, use the formulas above as a basis but change the plus sign (+) to the minus sign (-). Here's what I've got:

  =A2-TIME(3,0,0) - to subtract 3 hours
  =A2-(27/24) - to subtract 27 hours

Add or subtract minutes in Google Sheets

The principle of manipulating minutes is the same as with the hours.

• There's the TIME function that adds and subtracts up to 60 minutes:

  =Start time + TIME(0, N minutes, 0)

  If you are to add 40 minutes, you can do it like this:

  =A2+TIME(0,40,0)

  If you are to subtract 20 minutes, here's the formula to use:

  =A2-TIME(0,40,0)

  

• And there's a formula based on simple arithmetic to add and subtract over 60 minutes:

  =Start time + (N minutes / 1440)

  Thus, here's how you add 120 minutes:

  =A2+(120/1440)

  Put the minus instead of plus to subtract 120 minutes:

  =A2-(120/1440)

  

Add or subtract seconds in Google Sheets

Seconds in Google Sheets are calculated in the same manner as hours and minutes.

• You can use the TIME function to add or subtract up to 60 seconds:

  =Start time + TIME(0, 0, N seconds)

  For example, add 30 seconds:

  =A2+TIME(0,0,30)

  Or substitute 30 seconds:

  =A2-TIME(0,0,30)

• To calculate over 60 seconds, use simple maths:

  =Start time + (N seconds / 86400)

  Add 700 seconds:

  =A2+(700/86400)

  Or substitute 700 seconds:

  =A2-(700/86400)

How to sum time in Google Sheets

To find the total time in your table in Google Sheets, you can use the SUM function. The trick here is to choose the correct format to display the result.

By default, the result will be formatted as Duration - hh:mm:ss

But most often the default time or duration format won't be enough, and you will need to come up with your own one.

A7:A9 cells contain the same time value. They are just displayed differently. And you can actually perform calculations with them: subtract, sum, convert to decimal, etc.

Extract date and time from a full "date-time" record

Let's imagine that one cell in Google Sheets contains both, date and time. You want to set them apart: extract only the date to one cell and only time to another.

Split Date time using Number format

In order to display date or time in one cell on your screen or to print it, just select the original cell, go to Format > Number and choose Date or Time.

However, if you'd like to use these values for future calculations (subtract, sum, etc.), this won't be enough. If you don't see the time unit in a cell, it doesn't necessarily mean that it's absent, and vice versa.

So what do you do?

Split Date time using formulas

Google stores dates and time as numbers. For example, it sees the date 8/24/2017 11:40:03 as the number 42971,4861458. The integer part represents the date, the fractional - time. So, your task is down to separating integer from fractional.

1. To extract date (integer part), use the ROUNDDOWN function in cell B2:

   =ROUNDDOWN(A2,0)
   
   The formula rounds the value down and casts the fractional part away.

2. To extract time, place the following subtraction formula into C2:

   =A2-B2

3. Copy the results into the third row and apply Date format to B3 and Time format to C3:
   

Use the Split Date & Time add-on

You may be surprised but there's one special add-on for this job. It's really small and easy but its contribution to Google Sheets cannot be overstated.

Split Date & Time splits all Date time records in your entire column at once. You control the desired outcome with just 4 simple settings:

You tell the add-on:

1. Whether there's a header row.
2. If you want to get the Date unit.
3. If you want to get the Time unit.
4. And if you'd like to replace your original column with the new data.

It literally takes the burden of splitting date and time units off your shoulders:

The add-on is part of the Power Tools collection so you will have more than 30 other useful add-ons at hand. Install it from the Google Sheets store to test everything out.

These are the ways to not only display date or time, but to separate them to different cells. And you can perform various calculations with these records now.

I hope these examples will help you solve your tasks when working with dates and time in Google Sheets.

You may also be interested in

• Calculate days, months and years between two dates in Google Sheets: DATEDIF formulas
• Change Google Sheets date format and convert dates to numbers and text
• SUMIFS in Google Sheets to sum cells with multiple criteria (AND / OR logic)
• Combine duplicate rows, merge values, and add subtotals in Google Sheets
• Back to Basics: How to use formulas in Google Sheets