How To Calculate Time In Google Sheets

How To Calculate Time In Google Sheets

Whether you want to put together a quick financial spreadsheet or work with a colleague on a document like Excel, Google Sheets is a great web-based, free alternative to Excel.

One of the most useful aspects of spreadsheet programs is how flexible they are. A spreadsheet can serve as a database, computing engine, statistical modeling platform, text editor, media library, to-do list, and more. The possibilities are almost endless. A common use for spreadsheets, including Google Sheets, is to track employee schedules or billing hours per hour.

If you use Google Sheets to track time in this way, you will often find yourself having to count the difference between two timestamps, the time elapsed between two different time events. For example, if someone clocked in at 9:15 in the morning and then clocked in at 4:30 PM, they were 7 hours, 15 minutes on the clock. If you need to use a sheet for something like this, you will quickly notice that it is not designed to handle such tasks.

Still, although Google Sheets isn't specifically designed to handle timing log functions, it's easy to agree to do so with some preparation. This article shows you how to automatically calculate the difference between two timestamps using Google Sheets.

How To Calculate Time In Google Sheets
How To Calculate Time In Google Sheets


Calculating time in Google Sheets

To measure the difference between two cells containing time data, Google Sheets understands that cell data is time data. Otherwise, it calculates the difference between 9:00 AM and 10:00 AM as 100 instead of 60 minutes or one hour. In order to accurately calculate the time difference, time columns need to be formatted as time and period as column duration.

A timesheet format, when the person starts work, when they left and a (calculated) period will be used for our examples.


Follow these steps to set up your spreadsheet:

Open your Google Sheets.

Select the first (time in) time column and click 123 format drop-down in the menu, then select time as format.

Repeat the above steps for the second (time out) time column.

Format the Hours worked column in the same way as the duration.

Now, the columns are correctly formatted to calculate the time elapsed between the two recorded timestamps.

You can take this calculation one step further by adding dates. This process is helpful if you have a work shift that lasts more than 24 hours or two days in one shift. To do this, set the time and time out columns in the date time format.

Get over here to get it over here. It is relatively easy to calculate time on Google Sheets by following the steps listed above and using the formulas provided.


How to add time gaps or work breaks when calculating time

When calculating staff hours, it's helpful to know how to add breaks to your timesheet. There are several ways to add lunch break during work hours.


Create a break start column and add all breaks to the cells.

You can set the column layout automatically because Google handles the rest of the sheet

Next, create a break end column and release the format automatically.

Calculate the hours for the hour worked column. Thus, E2 = (B2-A2) + (D2-C2), which translates to "break start" - time start "+" time out - break end "= working hours for the day.

Use counts for each row so that your hourly working column looks like this.


How to convert minutes to fractions in Google Sheets

When working with increasing time, it may be helpful to convert them into fractions instead of minutes, e.g., 30 minutes = 1/2. It's easy to convert minutes to fractions; There are several ways to do this.


You can easily apply it to a group or a column and convert it in seconds.


Frequently Asked Questions

How to find the minimum working time in Google Sheets?

This formula will help if you want to find the minimum time to work fast. The MIN () function is a built-in function that allows you to find the lowest value in a list of numbers.

Create a new cell and set it to Expiration, I2 in this example and set it to the '= MIN (E2: E12)' function.

Assuming you follow the example, the Min Hours worked column should be 5:15:00.

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


How to calculate the total hours worked on Google Sheets?

If you are unfamiliar with programming or Excel, some of the built-in functions for Google Sheets may seem strange. Fortunately, it doesn't take much to calculate the total working hours. In this example, we will calculate the total hours worked by all employees in one day.

Create a new cell and assign it as a period, cell G13 in this example.

In the formula (fx) bar: Type ‘= SUM (E2: E12)’, which gives the total working hours from cell E2 to E12. This formula is the standard syntax for Excel and various programming languages.

The total should be 67:20:00 and should look like this:


Conclusion

Google Sheets isn't specifically designed to create timesheets, but it can be easily configured to do so. This simple setup means you can track working hours quickly and easily. When timesspans cross the 24-hour mark, things get a little more complicated, but the sheet can still turn it off by changing the date format from time to time.

Previous
Next Post »