Skip to Content

How to Calculate Time in Google Sheets

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.

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

Format as time-1

2. In the top menu select Format>Number

Format as time-2

3. Select Time

Format as time-3

4. You can now enter values into your cells and it will be formatted as time

Format as time-4

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:

time difference-1

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

time difference-2

2. In the top menu select Format>Number

time difference-3

3. Select Duration format

time difference-4

Once you have your cells calculated as duration, you can begin calculating the difference between two times with a simple subtraction formula:

calculate time difference

The formula I have used in this example is:

=B2-A2

This gives me the hours worked for a given start time and end time by subtracting the start time from the end time.

Adding 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.

Adding Time

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.

Subtracting Time

You can make additional time subtractions as long as the cell you are subtracting is formatted as duration.

Subtracting time

In this example, I have a start time, lunch break, and end time.

The formula in the example is:

=C2A2B2

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:

Time Sheet

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

time sheet-1

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:

=D2-B2-C2

time sheet-2

Once we have the hours worked we can calculate the total hours worked by adding all these values together:

time sheet-3

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.

Closing Thoughts

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