How to Calculate Time in Google Sheets

Whether you’re looking to throw together a quick financial spreadsheet or you want to work together with a co-worker on an Excel-like document, Google Sheets is a great web-based, free alternative to Excel.

One of the most valuable aspects of spreadsheet programs is their flexibility. A spreadsheet can serve as a database, calculation engine, platform for statistical modeling, text editor, media library, to-do list, and more. The possibilities are nearly endless. One everyday use for spreadsheets, including Google Sheets, is to track hourly employee time schedules or billable hours.

If you are using Google Sheets to track time in this way, you will frequently find yourself needing to calculate the difference between two timestamps (the amount of time passed between two different time events). For example, if someone clocked in at 9:15 AM and then clocked out at 4:30 PM, they were on the clock for 7 hours, 15 minutes. If you need to use Sheets for something like this, you’ll quickly notice that it didn’t get designed to handle these kinds of tasks.

Still, while Google Sheetshandles timing log functions, it is easy to persuade it to do with some preparation. This article shows you how to calculate the difference between two timestamps using Google Sheets automatically.

How to Add Times and Calculate Worked Time in Google Sheets

Measuring the difference between two cells containing time data requires that Google Sheets understands that the cells’ data is time data. Otherwise, it calculates the difference between 9:00 AM and 10:00 AM as 100 rather than 60 minutes or one hour. To correctly count time differences, the time columns require formatting as “Time” and the duration column as “Duration.”

Furthermore, The calculation is intentionally backward (time out – time in) because it has to account for AM/PM transitions, so you don’t get negative numbers. Therefore, 2:00 PM – 9:00 AM = 5.00 Hours whereas 9:00 AM – 2:00 PM = -5.00 Hours. Test it out if you like.

To make a formatted timesheet showing the time the person started work, the time they left, and the calculated duration worked can be done as follows:

  1. Open the specific “Google sheet.”
  2. Select your “Time In:” column and click the “123″ format drop-down in the menu, then select “Time” as the format.
  3. Select the “Time Out:” column, then click on the “123” drop-down menu item, and then select “Time.”
  4. Select the “Hours Worked:” column. Click on the “123” drop-down menu item and choose “Duration” as the format.
  5. To activate the formula in the “Hours Worked” column, type “=(C2-A2)” where “C2” represents the “Time Out” cell, and “A2” represents the “Time In” cell.

That’s all there is to it. Following the steps listed above and using the provided formula is relatively easy to calculate time in Google Sheets. What if you want to add breaks into the calculations? Keep reading to find out.

How to Add Time Gaps or Work Breaks When Calculating Time in Google Sheets

Unless paid lunches or short-term leave are a benefit in the business, you may need to calculate breaks in the worked hours. Even if breaktimes are unpaid time, it works out best to include separate entries versus using “Time In” and “Time Out” for breaks. Here’s how to do it.

Note: Just like basic time in and time out calculations, you need to calculate time in reverse as follows: “Time out” – “Time In,” except you’ll have breaktiume entries in between the formula.

  1. Select your “Break Out:” column and click the “123″ format drop-down in the menu, then select “Time” as the format.
  2. Select your “Break In:” column, click on the “123″ format drop-down entry in the menu, and then choose “Time” as the format.
  3. Calculate the hours for the “Hours Worked” Column. Type “=(C2-A2)+(G2-E2),” which translates to [Break Out (C2) – Time In (A2)] + [Time Out (E2) – Break In (G2)].
  4. Use the calculation for every row so that your Hours Worked Column looks like this.

How to Add Dates to Your Timesheets in Google Sheets

If you prefer to add dates to the worktime entries, the process is the same as it is for adding time, except you choose “Date time” as the format instead of “Time.” Your cells display “MM/DD/YYYY HH/MM/SS” when you choose “Date time” as the format.

How to Convert Minutes to Decimals in Google Sheets

When dealing with increments of time, it might be helpful to convert them into decimals instead of minutes, i.e., “1 hour and 30 minutes = 1.5 hours.” Converting minutes to decimals is easy; there are several ways to accomplish this.

  1. Select the “Worked Time” column, click on the “123” menu entry at the top, then change the format from “Duration” to “Number.” Ignore all the weird characters that appear in the cells.
  2. In the first “Worked Time” cell, copy/type “=(C2-A2)*24+(G2-E2)*24” without quotes. Be sure to change the formula to the correct cell IDs, such as “C2-A2.”
  3. Copy the formula you created in the first “Worked Time” cell and paste it into all other “Worked Time” cells in the column. Google autoformats the cells with the correct cell IDs.

In closing, Google Sheets wasn’t explicitly designed to produce timesheets, but it can easily get configured to do just that. This simple setup means you can track hours worked quickly and easily. When timespans cross over the 24-hour mark, things become a little more complicated, but Sheets can still pull it off by changing from Time to Date format.

You can also read our article on calculating how many days have passed between two dates in Sheets.

Google Sheets Time Calculation FAQs

How to find the shortest or highest amount of time worked in Google Sheets?

If you quickly need to locate the least amount of time worked, this formula should help. The MIN() function is a built-in function that allows you to find the minimum value in a list of numbers.

1. Create a new cell (I2 in this example) and set the format to “Duration.” Copy/type the function “=MIN(E2:E12)” without quotes and paste it into the cell. Be sure to change the cell IDs, such as “E2.”

Now, the “Min. Hours Worked” column should show the lowest amount of hours worked, such as “5:15:00.”

You can easily apply the MIN() or MAX() function to a column or group of cells. Give it a try for yourself.

How to calculate the total hours worked in Google Sheets?

If you’re not familiar with programming or Excel, then some of the built-in functions for Google Sheets may seem strange. Luckily, it doesn’t take much to calculate the total hours worked. In this example, we’ll calculate the total hours worked by all the employees in a day.

1. Create a new cell and assign it as “Duration.”

2. In the Formula (fx) Bar: type “=SUM(E2:E12)” without quotes, which provides the total hours worked from cells E2 through E12. This formula is standard syntax for Excel and various programming languages.

The total should appear in the format” 67:20:00″ and look like this:

26 thoughts on “How to Calculate Time in Google Sheets”

kathy says:
Thank you for this. Help tremendously with a time tracker for staff
Balajinaik says:
We need formula for if working hours is 6 and 1 person working 6.5 hours and we need to show he worked 0.5 hour extra (fixed time 10:00AM to 04:00PM) (Work time 10:00 AM to 4:30:00PM) actual extra work 0.5 hour

what is the formula for that

ab says:
Cannot make this work. Column C (minus) says “waiting for number, but is text” whatever I choose for formats. Could you share your sheet?
emma says:
How do i take away breaks – ie time started, break, time finished on a google sheet?
Anna says:
When I format the column as Time it changes all the numbers to something weird and unhelpful. What am I doing wrong?
Christopher Meeker says:
I have an issue similar to this but not exactly. I want to track swim times inside Google Sheets. Specifically, I would like to have an array of past swim times for each event with format (mm:ss.ms) and use the ‘MIN’ function to pull out the minimum value. I have tried to type the values and create a custom ‘duration’ number for the fields to no avail. I can’t get Google Sheets to see the typed in values as numbers for the MIN function to work properly.
Example (mm:ss.ms) format:
B2 = 0:32.06, C2 = 0:31.60, D2 = 0:31.78
=MIN(B2:D2) >> Returns 0
Am unable to get Google Sheets to parse the “duration” cells properly or I do not understand how it does parse them more likely.
Morgan says:
is there a way to do this the other way around? So say something starts at 6 and lasts 30 mins, is there a way to have the next time say 6:30 because of that?
Jennifer says:
Is there a way to change the totals to fractions? Instead of 15 minutes to give you .25?
Kevin James Bowman says:
I know you left this over a month ago, so I am leaving it for the next person. This will give you the

=(HOUR(C2)+(Minute(C2)/60)-(HOUR(A2)+((Minute(A2)/60))))

Jennifer Duff says:
It worked! Thanks so much. I had just been writing them into a column all month.
Appreciate the answer 🙂
Eileen says:
I have an issue with adding up a months total hours, I have for instance 4 weeks where some people have worked 9 hours per day, that adds up correctly. But then i have someone else for the 4 weeks that has worked 9 hours a day, but on 4/5 days he worked 14 hours, but his hours add up to less than the guy that worked 9 hours straight????? the total hours is setup as duration
Harry Wilcock says:
Hi there Sheets Genius’s, so ive managed to get the formula to work but how do I say multiply my hours worked figure by an hourly rate, because ie: 0.45.00 minutes multiplied by say $30.00/h just gives me $0.94, kinda stuck on this not sure how to get it to work so I get 0.75 of an hour x by hourly rate, I need the hours worked to be in a percentage in the formula argh spreedsheets! Any help would be great thanks
Lacey says:
Hi Harry,

I had the same problem as you and I think I figured it out. If you change your total hours worked cell to a number format and then multiply the number in that cell by 24, it should give you the correct hours in a plain number format that you can then multiply by the hourly rate.

Hopefully this works for you!

Lacey

Terence says:
Hi, How can I find the shortest time in a google sheets time table ?
Steve says:
After setting duration of time worked between Start and End times, is there a way to Subtract 30 minutes (for lunch) in the code? or will that need to be done seperately?
C says:
I’m trying to do the same…
Eileen says:
I add a lunch colomn and put the lunch hour in as 01:00 and it deducts the 1 hour.
Randy says:
Is there a way to have timestamps from a google form automatically populate the spreadsheet side by side much like the example above:
Time In Time out Hours worked
1/12/2019 16:00:42 1/12/2019 17:00:42 1:00

Please include formulas in your answers

subhradip sinha says:
HOW to calculation total of working hours in Docs sheet
Alyssa says:
How can I add another column to calculate a lunch break?
Vanessa Plett says:
I put in 3:15 pm until 4:10 pm and it always clocks it as 12:55 min If I go 3:15-4:15 it is 1 hour Why is that?
Vanessa Plett says:
I figured it out. I have to change the formula to duration format.
Dillon says:
I want it to display the percentage of the hour, for instance :15 = .25 hours. so instead of having the “duration” display “8:15:00” I want it to display “8.25”. Anyone figure out how to do this?
David says:
The delta value is in units of days: 24 hours = 1.0. So format the delta cells not as time but as numbers with 2 digits past the decimal, and change the formula to divide by 24, e.g., (B1-A1)/24.
David says:
Oops, that should be *24. not /24!
L says:
I have a similar setup but instead of calculating duration, I have a known start time and want to add an expected (given) duration to that to come up with an expected end time. I can’t seem to get Sheets to add a given duration to a time. Anyone know how?
David Olsson says:
If you have the duration in a cell, format that cell as Duration (>Format>Number>Duration) and enter the duration as h:m:s. You can then use that cell in arithmetic formulas with date/time values. Alternatively you could I think you could use the TIMEVALUE function to get the fraction of a day equivalent to a give time string and use that value in your formula. I didn’t do it that way.

Leave a Reply

Your email address will not be published.


Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.

Todays Highlights
How to See Google Search History
how to download photos from google photos