Google Sheets is a powerful spreadsheet program that can be used in many different ways to accomplish some pretty neat things.
One very useful thing that can be done with Google Sheets is calculating time. With Google Sheets, it is possible to add time, subtract time, and even create a timesheet to calculate hours worked.
In this tutorial, I will show you how to calculate time in Google Sheets. I will show how to add and subtract time to make different time calculations, and I will show how to create a timesheet using these skills.
Table of Contents
Calculating Time in Google Sheets
Formatting as Time
The first thing we want to do before doing any kind of time calculation in our spreadsheet is make sure our cells are formatted as time.
To format your cells as time:
1. First, highlight the cells that you want to format as time
2. In the top menu select Format>Number
3. Select Time
4. You can now enter values into your cells and it will be formatted as time
Calculating Time Difference
Now that we have our cells formatted as time, we can start to make some calculations with time to calculate the time difference.
Let’s say I have this example data:
I want to calculate the hours worked if I have the given start times and end times.
To do this, we need to first format the cells in column C as duration format, other was a time difference calculation will not work properly.
Here is how to format as duration:
1. Highlight the cells you want to format as duration
2. In the top menu select Format>Number
3. Select Duration format
Once you have your cells calculated as duration, you can begin calculating the difference between two times with a simple subtraction formula:
The formula I have used in this example is:
This gives me the hours worked for a given start time and end time by subtracting the start time from the end time.
Once you have your time difference in duration format, you can start adding these time values together with either the SUM function or using the addition sign to add the time values together.
In this example to calculate the total hours worked, I have two formulas that calculate this:
=SUM(C2:C5) or =C5+C4+C3+C2
Both of these formulas make the same calculation by adding these time values together in the spreadsheet.
You can make additional time subtractions as long as the cell you are subtracting is formatted as duration.
In this example, I have a start time, lunch break, and end time.
The formula in the example is:
This subtracts the start time from the end time to get the duration, and then subtracts the lunch break to get the total hours worked.
How to Make a Timesheet
We can now use all of the skills we have developed so far to make a timesheet in Google Sheets.
To make a timesheet, first, we will make a basic template with all of the columns we need to fill out:
The key is going to be making sure each column is formatted as the right type.
Here is what each column should be formatted as:
- Start Time (column B) – Time
- Lunch Break (column C) – Duration
- End Time (column D) – Time
- Hours Worked (column E) – Duration
After your cells are formatted properly, you can begin filling in your data
Then once your clock in and clock out times are entered into your spreadsheet, you can set up your formulas to calculate the hours worked and total hours worked.
In this example to calculate the hours worked our formula will be “End Time – Start Time – Lunch Break”. In our spreadsheet this will be:
Once we have the hours worked we can calculate the total hours worked by adding all these values together:
You now have created a basic timesheet in Google Sheets that you can use to calculate payroll and keep track of hours worked.
You can add additional weeks, months, and change the formatting as desired to have a functional timesheet.
Making time calculations with Google Sheets is pretty easy once you understand how your cells need to be formatted.
The key is to know which cells need to be time format, and which cells need to be duration formatted. If your cells are not formatted properly, the time calculations will not work the way you want them to.
More Google Sheets Tutorials:
How to Use the WORKDAY Function
How to Convert Text to Date
How to Use Calculated Fields in Pivot Tables
How to Convert Time to Military Time