Calculating Time in Google Sheets: How-To
To calculate time in Google Sheets, you use standard arithmetic on cells formatted as durations, and the result is displayed in the same time format. You can add, subtract, or find the difference between times, and convert the result to decimal hours or minutes when needed.
Why Time Calculations in Sheets Can Be Tricky
Google Sheets stores time as a fraction of a day. For example, 6:00 AM is stored as 0.25, and 12:00 PM is 0.5. This means that when you subtract two times, you get a fraction of a day, not a number of hours. If you simply subtract 9:00 AM from 5:00 PM, the result is 0.333…, which displays as 8:00:00 AM if the cell is formatted as a time. To get a useful answer, you need to either format the result as a duration or multiply by 24 to convert to hours.
Basic Time Subtraction and Addition
To find the difference between two times, use the minus operator. For example, if cell A1 contains 9:00 AM and B1 contains 5:00 PM, the formula =B1-A1 will return 0.333… if the cell is formatted as a number, or 8:00:00 AM if formatted as a time. To display it as 8:00:00 (a duration), format the result cell as Format > Number > Duration. This shows the total elapsed hours, minutes, and seconds.
Adding times works the same way: =A1+B1 adds the two times. For example, 1:30 PM plus 2:45 PM gives 4:15 PM. If the sum exceeds 24 hours, the duration format will show the total hours (e.g., 26:00:00).
Converting Time to Decimal Hours or Minutes
Often you need the result as a decimal number, such as 8.5 hours for payroll or billing. To convert a time difference to decimal hours, multiply the result by 24. For example, =(B1-A1)*24 gives 8.0 for a 9-to-5 shift. To get decimal minutes, multiply by 1440 (24*60). To get seconds, multiply by 86400.
Be careful: if the time spans midnight (e.g., 10:00 PM to 6:00 AM), a simple subtraction will return a negative number. In that case, use the MOD function: =MOD(B1-A1,1) to get the correct positive duration.
Using the TEXT Function to Format Results
You can also use the TEXT function to display a time value as text in a specific format. For example, =TEXT(B1-A1,"h:mm") returns the difference as text like “8:00”. This is useful when you want to embed the result in a sentence or a label. However, text results are not usable in further calculations, so it’s best to keep the numeric value in a separate cell if you need to do more math.
Common Mistakes and How to Avoid Them
- Wrong cell format: If your result shows as a time of day (like 12:00 AM) instead of a duration, change the format to Duration.
- Negative times: When subtracting a later time from an earlier one, Sheets may show a negative time or a series of hashes. Use MOD to handle overnight shifts.
- Mixed units: If you enter “8” instead of “8:00”, Sheets treats it as 8 days, not 8 hours. Always enter times with colons.
- Forgetting to multiply by 24: When you need decimal hours, you must multiply by 24; otherwise, you get a fraction of a day.
Working with Durations Over 24 Hours
If you are adding up a list of durations that exceed 24 hours (e.g., total weekly work hours), the SUM function will work, but the result may display as a time of day (like 2:00 AM) instead of 26:00. To fix this, format the sum cell as Duration. For example, if you have a list of daily hours in cells A1:A5, use =SUM(A1:A5) and format the result as Duration to see the total hours correctly.
Using Built-in Time Functions
Google Sheets includes several functions for time calculations:
- NOW() returns the current date and time. To get the current time only, use
=NOW()and format as time. - TODAY() returns the current date.
- HOUR(), MINUTE(), SECOND() extract parts from a time value. For example,
=HOUR(A1)returns the hour as a number. - TIME(hour, minute, second) creates a time from numbers. For example,
=TIME(14,30,0)returns 2:30 PM.
Frequently Asked Questions
How do I calculate the difference between two times in Google Sheets?
Use the minus operator: =B1-A1. Then format the result cell as Duration to see the elapsed hours and minutes. If the times span midnight, use =MOD(B1-A1,1).
How do I convert time to decimal hours in Google Sheets?
Multiply the time difference by 24. For example, =(B1-A1)*24 gives the number of hours as a decimal. For minutes, multiply by 1440.
Why is my time subtraction showing as a time of day instead of hours?
Because the cell is formatted as a time, not a duration. Change the format to Format > Number > Duration to display the total elapsed time.
How do I add up a column of times that total more than 24 hours?
Use the SUM function and format the result cell as Duration. Sheets will then display the total hours, even if over 24, like 26:00:00.
Can I calculate time in Google Sheets using military time?
Yes, just enter times in 24-hour format (e.g., 14:30 for 2:30 PM). The calculations work the same way. The display format can be changed to 24-hour or 12-hour via Format > Number > More Formats > More date and time formats.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.