Oct
19

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

When we're working on some projects, it's important to control how much time we spend. Google Sheets can help calculate time difference.

In other words, if we have start time and end time in our table, it's not a problem to find out the time spent. Google even offers a special number format for cases like this - Duration.

Let's try and calculate the duration in various time units: hours, minutes, and days.

  1. We'll start with the easiest part. Input start time to column A and end time to column B. Using a simple subtraction formula in C2, we will find how much time this or that task took:

    =B2-A2
    Calculate duration in hours

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

  2. We have a few options of getting the result in minutes.
    • Try putting the following formula into D2:

      =HOUR(C2)*60+MINUTE(C2)

      The formula looks correct, but as soon as you copy it down the column, you'll see the #NUM! error.The thing is that we can calculate the difference in minutes only when both, start and end time, belong to one 24-hour period. If the 12:00 AM point is crossed (a new day begins), the results will be inconsistent.

      Our example shows the correct values in C2:C4, but we can't perform any arithmetic calculations with them since they are stored as negative time values. That's why we get an error when trying to get the result in minutes.

      So, I suggest using this method only when time difference is calculated from two units within 1 day.

    • Enter full date and time to A2:B4. The formulas remain the same in other cells:
      Calculate duration in minutes

      And again, we get the incorrect result. If the difference between time stamps exceeds 24 hours, you won't get the needed result.To avoid such failures, select column C and go to Format > Number > Duration. You will get a completely different result:
      Apply Duration format to see correct results

      Tip. Apply custom date and time format to column D as well. Otherwise, the results in minutes will also be incorrect. More on how to do that right below.

Sum time in Google spreadsheet

To find 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.

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

To create a custom time format, select your cells and go to Format > Number > More Formats > More date and time formats. Choose a necessary format from the list or enter your own. If you click the down arrow in the right corner of the text field, you'll be able to choose between additional date and time units:
Create various custom date and time formats

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

Note.If you subtract the latest time point from the earliest, you will get a negative value. That's not a problem for Google, since it recognizes and works with negative time. To make cells return negative time, set the Duration format or a custom one using elapsed time units:
Negative time in Google Sheets
Note.Google Sheets doesn't support negative day format and you won't be able to create one.

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

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

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

However, if you'd like to do math with these values in the future (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 we do?

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

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

    =ROUNDDOWN(A2,0)
    ROUNDDOWN function to separate the units

    The formula rounds the value down and casts the fractional part away.

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

    =A2=B2

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

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

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

You may also like:

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard