---
title: "How to calculate time in Google Sheets: TIME functions guide"
canonical: "https://www.owox.com/blog/articles/time-functions-google-sheets"
updated: "2026-10-05"
---

# How to calculate time in Google Sheets: add, subtract and the TIME functions

To get the time between two moments in Google Sheets, subtract the start from the end and format the result as a duration. This guide covers that, adding and subtracting hours, minutes and seconds, durations over 24 hours and across midnight, and the TIME, TIMEVALUE, HOUR, MINUTE and SECOND functions.

[Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 16 min read

![How to calculate time in Google Sheets: add, subtract and the TIME functions](https://cdn.owox.ai/www/webflow/69f0d71fa7599eba118aa0c7_GOOGLE-SHEETS-TIME-FUNCTIONS.png/public)

To calculate the time between two moments in Google Sheets, subtract the start from the end: **\=D3-C3**. Then format the cell with **Format → Number → Duration**, so that the result reads as hours and minutes. To add time, add a TIME value: `=C3+TIME(2,30,0)` adds two and a half hours.

| You want to | Formula |
| --- | --- |
| Time between a start and an end | `=D3-C3` |
| The same, when the end is after midnight | `=MOD(D3-C3, 1)` |
| The duration as a number of hours | `=(D3-C3)*24` |
| The duration as a number of minutes | `=(D3-C3)*1440` |
| Add 2 hours 30 minutes to a time | `=C3+TIME(2,30,0)` |
| Build a time from three numbers | `=TIME(hour, minute, second)` |
| Turn a text time into a real time | `=TIMEVALUE("9:30 AM")` |
| Take the hour, minute or second out of a time | `=HOUR(C3)`, `=MINUTE(C3)`, `=SECOND(C3)` |

All of this works because of one fact: **Google Sheets stores a time as a fraction of a day**. Noon is 0.5, 6:00 AM is 0.25, one hour is 1/24. Times can be added, subtracted, multiplied and compared like any other number; the format decides whether you see 0.5 or 12:00 PM.

## TIME, TIMEVALUE, HOUR, MINUTE and SECOND: syntax and examples

The examples use a table of Disneyland attractions with their opening and closing times.

### TIME function

TIME **builds a time value from an hour, a minute and a second**. It is the way to write a fixed time or a fixed amount of time inside a formula.

#### Syntax of TIME function

> `=TIME(hour, minute, second)`

Let’s break it down:

*   **hour:** the hour, 0 to 23.
*   **minute:** the minute, 0 to 59.
*   **second:** the second, 0 to 59.

Values above these ranges roll over into the next unit (see the examples below). Negative values return #NUM!.

#### Example of TIME function

With hours, minutes and seconds in columns C, D and E:

> `=TIME(C3, D3, E3)`

![Google Sheets with TIME building a time of day from hours, minutes and seconds in three columns.](https://cdn.owox.ai/www/webflow/67872d12801d81c2427ea579_59360.png/public)

TIME **rolls over values that are too large**: TIME(25,0,0) returns 1:00 AM, and TIME(12,0,60) returns 12:01:00. This also means TIME cannot hold more than 24 hours; for longer durations, see the section on values greater than 24 hours.

### TIMEVALUE function

TIMEVALUE converts a time written as text into **the fraction of a day it represents**. Use it when times arrive as text, for example from an import, and you need to calculate with them.

#### Syntax of TIMEVALUE function

The syntax for the TIMEVALUE function in Google Sheets is:

> `=TIMEVALUE(time_string)`

Let’s explain:

*   **time\_string**: The string that holds the time representation.

#### Example of TIMEVALUE function

For the opening times in column C:

> `=TIMEVALUE(C3)`

Here’s the breakdown:

*   **C3:** Time when the attraction opens.

![Google Sheets with TIMEVALUE converting an attraction’s opening time from text into a time value.](https://cdn.owox.ai/www/webflow/67872d12798096e2f3fab244_59362.png/public)

The result is a decimal such as 0.43. Format the cell as Time to read it as 10:15 AM.

### HOUR function

HOUR **returns the hour of a time** as a whole number from 0 to 23.

#### Syntax of HOUR function

The syntax for the HOUR function in Google Sheets is:

> `=HOUR(time)`

Here’s the explanation:

*   **time:** The time value from which the hour will be extracted. This can be a time string or a reference to a cell containing a time.

#### Example of HOUR function

For the closing times in column D:

> `=HOUR(D3)`

Here’s the breakdown:

*   **D3:** Time when the attraction closes.

![Google Sheets with HOUR extracting the hour from an attraction’s closing time.](https://cdn.owox.ai/www/webflow/67872d11f2a4fa83dcc89626_59364.png/public)

A closing time of 7:00 PM returns 19.

### MINUTE function

MINUTE **returns the minute of a time** as a whole number from 0 to 59.

#### Syntax of MINUTE function

The syntax for the MINUTE function in Google Sheets is:

> `=MINUTE(time)`

Here’s the breakdown:

*   **time**: The cell reference containing the time value from which we want to extract the minute.

#### Example of MINUTE function

For the opening times in column C:

> `=MINUTE(C3)`

Here’s the breakdown:

*   **C3:** Time when the attraction opens.

![Google Sheets with MINUTE extracting the minutes from an attraction’s opening time.](https://cdn.owox.ai/www/webflow/67872d120b14038281f700c9_59366.png/public)

An opening time of 10:15 AM returns 15.

### SECOND function

SECOND **returns the seconds of a time** as a whole number from 0 to 59.

#### Syntax of SECOND function

The syntax for the SECOND function in Google Sheets is:

> `=SECOND(time)`

Here’s the breakdown:

*   **time**: The cell reference containing the time value from which we want to extract the second.

#### Example of SECOND function

For the best ride durations in column D:

> `=SECOND(D3)`

Break it down:

*   **D3:** Refers to the “Best Duration” for each attraction.

![Google Sheets with SECOND extracting the seconds from the best-duration column for each attraction.](https://cdn.owox.ai/www/webflow/67872d1103f1318f90c43c12_59368.png/public)

## Adding and subtracting time

### The time between a start and an end

Subtract the opening time from the closing time to get **how long each attraction is open**:

> `=TEXT(D3-C3, "h:mm")`

Here’s the breakdown:

*   **C3:** Opening time of the attraction.
*   **D3:** Closing time of the attraction.

![Google Sheets with opening time subtracted from closing time to give how long an attraction is open.](https://cdn.owox.ai/www/webflow/67872d11640ca5b1e55d1ce6_59372.png/public)

TEXT turns the result into text in the format you give it, here hours and minutes. Leave TEXT out, `=D3-C3`, and format the cell as Duration if you want to keep calculating with the result.

**When the end is after midnight**, the subtraction is negative and the result is wrong. `=MOD(D3-C3, 1)` handles both cases: for a shift from 10:00 PM to 2:00 AM it returns 4:00.

### Adding time to a time

To add 2 hours to the closing time, for cleaning:

> `=TEXT(D3 + TIME(2,0,0), "h:mm AM/PM")`

Here’s the breakdown:

*   **D3:** Original closing time.
*   **TIME(2,0,0):** Adds 2 hours, 0 minutes, and 0 seconds.

![Google Sheets with TIME adding two hours to an original closing time.](https://cdn.owox.ai/www/webflow/67872d1175f6f0003db4a418_59374.png/public)

### How to add and subtract hours, minutes, or seconds

TIME takes hours, minutes and seconds separately, so any of the three can be added or subtracted. To add 3 hours to an opening time:

> `=TEXT(C3 + TIME(3, 0, 0), "h:mm AM/PM")`

Here’s the breakdown:

*   **C3:** Original opening time.
*   **TIME(3,0,0):** Adds 3 hours, 0 minutes, and 0 seconds.

![Google Sheets with TIME adding three hours to an original opening time.](https://cdn.owox.ai/www/webflow/67872d115e53b2579f2488c0_59376.png/public)

To subtract 45 minutes:

> `=TEXT(C4 - TIME(0, 45, 0), "h:mm AM/PM")`

Let’s break it down:

*   **C4:** Original opening time.
*   **TIME(0, 45, 0):** 0 hours, 45 minutes and 0 seconds, subtracted from the opening time.

![Google Sheets with TIME subtracting forty-five minutes from an opening time.](https://cdn.owox.ai/www/webflow/67872d12de65a2a6b53e5ca1_59378.png/public)

And to add 30 seconds:

> `=TEXT(C5 + TIME(0, 0, 30), "h:mm:ss AM/PM")`

Let’s explain:

*   **C5:** Original opening time.
*   **TIME(0, 0, 30):** 0 hours, 0 minutes and 30 seconds, added to the opening time.

![Google Sheets with TIME adding thirty seconds to an opening time.](https://cdn.owox.ai/www/webflow/67872d1182742bf188124a7e_59380.png/public)

### Calculating the specific time duration by multiplying and dividing the time

A duration is a number, so it can be multiplied and divided. To get the total opening time over a week of 6 working days, multiply the daily duration in column E:

> `=E3 * 6`

Let’s explain:

*   **E3:** The cell containing the daily time duration (hours and minutes).
*   **6:** Multiplies the daily duration for the number of working days in a week.

![Google Sheets with a daily duration multiplied by six to give the time across several days.](https://cdn.owox.ai/www/webflow/67872d11f1746ba8562019ff_59382.png/public)

Format the result as Duration: the Time format would start again from zero after 24 hours.

Dividing one duration by another gives a plain number. To see how many 30-minute maintenance shifts fit into the daily opening time:

> `=TEXT(E3 / TIME(0, 30, 0), "0.0")`

Here’s the breakdown:

*   **E3:** The cell containing the daily time duration.
*   **TIME(0, 30, 0):** Represents 30 minutes in the TIME function.
*   **“0.0”:** The pattern used to format the number in quotation marks.

![Google Sheets with a daily duration divided by TIME(0,30,0) to give the number of thirty-minute shifts.](https://cdn.owox.ai/www/webflow/67872d1155ffccf770e9037f_59384.png/public)

### Applying the TIME function with decimals

Durations often arrive as decimal hours: 7.5 for seven and a half hours. To turn the decimal in column E into a time value, take the whole hours with INT and convert the rest into minutes with MOD:

> `=TIME(INT(E3), MOD(E3*60, 60), 0)`

*   **INT(E3):** the whole number of hours.
*   **MOD(E3 \* 60, 60):** the decimal part, converted to minutes.
*   **0:** no seconds.

![Google Sheets with INT taking the whole-number part of a decimal to convert it into hours and minutes.](https://cdn.owox.ai/www/webflow/67872d12e9c42ff79357883f_59386.png/public)

Dividing by 24 does the same in one step, `=E3/24`, and also works above 24 hours. Format the result as Duration.

### Using TIMEVALUE for different time formats

Times in an import are rarely written the same way: “13:00”, “9:30 AM”, “10/29/2024 9:30 AM”. TIMEVALUE reads all three and **returns the same kind of number for each**:

> `=TIMEVALUE(C3)`

Let’s explain:

*   **TIMEVALUE(C3):** This formula converts the time found in cell C3 into its corresponding decimal value, representing the time as a fraction of a day.

![Google Sheets with TIMEVALUE converting times written in different formats into one comparable value.](https://cdn.owox.ai/www/webflow/67872d1256111ba28b87c979_59388.png/public)

From a text that contains a date and a time, TIMEVALUE keeps only the time. Once every row holds a real time value, the column can be sorted, compared and subtracted.

### Using TIMEVALUE to get the time elapsed between two text times

When the opening and closing times are text, convert both and subtract. See our guide to [dates and times](/blog/articles/dates-and-times-google-sheets) for values that include a date.

> `=TIMEVALUE(D3) - TIMEVALUE(C3)`

In this formula:

*   **C3:** represents the start time (opening time).
*   **D3:** represents the end time (closing time).

![Google Sheets with TIMEVALUE working out the time elapsed between opening and closing.](https://cdn.owox.ai/www/webflow/67872d11ae30367b4319deb9_59392.png/public)

The result is a fraction of a day: 0.04 is one hour. Format the cell as Duration to read it as 1:00:00, or multiply by 24 for a number of hours.

### Using the HOUR function for duration calculations

Subtracting the hour of the opening time from the hour of the closing time gives a quick count of hours:

> `=HOUR(D3) - HOUR(C3)`

In this formula:

*   **C3:** represents the start time (opening time).
*   **D3:** represents the end time (closing time).

![Google Sheets with HOUR used to calculate a duration from a start time and an end time.](https://cdn.owox.ai/www/webflow/67872d127ebeaa52bde79fcf_59394.png/public)

**This ignores the minutes.** An attraction open from 9:30 AM to 7:00 PM returns 10, although it is open for nine and a half hours. For the exact number of hours, use `=(D3-C3)*24`.

### Calculating break duration using the MINUTE function

For breaks that start and end within the same hour, the difference of the minutes is the length of the break:

> `=MINUTE(D3) - MINUTE(C3)`

In this formula:

*   **MINUTE(C3):** Extracts the minute portion from the “Break in” time.
*   **MINUTE(D3):** Extracts the minute portion from the “Resume at” time.

![Google Sheets with MINUTE calculating the length of a break from the difference between two times.](https://cdn.owox.ai/www/webflow/67872d12048a000cbbb2ba69_59396.png/public)

A break from 2:50 PM to 3:10 PM would return -40. `=(D3-C3)*1440` returns the length in minutes whatever the hours are; 1440 is the number of minutes in a day.

### Conditionally formatting based on seconds using the SECOND function

A custom formula in conditional formatting can **highlight times by their seconds**. To mark every malfunction that happened in the first 20 seconds of a minute:

*   Select the range of cells containing the malfunction times (in this case, column D).
*   Go to **Format → Conditional formatting**.
*   Under **Format cells if**, choose **Custom formula is** and enter:

> `=SECOND(D3) < 20`

*   Choose a fill color and click **Done**.

![The Conditional formatting panel highlighting times where the seconds fall in the first twenty of a minute.](https://cdn.owox.ai/www/webflow/67872d1299a1fe79ab83c4ff_59398.png/public)

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## More time calculations

### Handling values greater than 24 hours using TIME function

TIME works within one day. Given more than 24 hours, it **drops the full days and returns the time of day that is left**: TIME(26,0,0) is 2:00 AM. To keep the days, count them in a second column.

Time:

> `=TIME(C3, D3, E3)`

![Google Sheets with TIME taking hours, minutes and seconds from three cells to build a time value.](https://cdn.owox.ai/www/webflow/67872d11a72f4ee777808b95_59402.png/public)

Day Count:

> `=INT(C3/24)`

INT returns the whole number of days in the hours.

![Google Sheets with INT removing the decimal part so a value over twenty-four hours can be handled.](https://cdn.owox.ai/www/webflow/67872d12048a000cbbb2ba66_59404.png/public)

For 38 hours and 30 minutes, the result is 1 day and 2:30 PM.

If you simply need a duration longer than a day, skip TIME: `=C3/24` turns 38 hours into a value that the **Duration** format shows as 38:00:00.

### Using the TIME function when minutes or seconds exceed 60

Minutes and seconds above 59 roll over as well: **75 minutes become 1 hour and 15 minutes**, and 90 seconds become 1 minute and 30 seconds.

> `=TIME(C3, D3, E3)`

![Google Sheets with TIME accepting minutes or seconds above sixty and carrying the excess into the next unit.](https://cdn.owox.ai/www/webflow/67872d139f41b79d9e6234a0_59406.png/public)

### Calculating an end time from a start time and a duration

With the opening time in column C and the duration in column D, this builds the closing time piece by piece:

> `=TIME(HOUR(C3)+HOUR(D3), MINUTE(C3)+MINUTE(D3), SECOND(C3)+SECOND(D3))`

Here’s the breakdown:

*   **C3:** represents the opening time of the attraction (e.g., 10:15 AM).
*   **D3:** represents the duration of the attraction (e.g., 0:45 hours).

![Google Sheets with TIME calculating the interval between an opening time such as 10:15 AM and a closing time.](https://cdn.owox.ai/www/webflow/67872d13c71d9d149775d19e_59408.png/public)

Because both cells already hold time values, `=C3+D3` returns the same closing time. The long form is useful when the parts come from different places.

### Calculating total time worked using TIMEVALUE function

To get the working hours of each attraction from text times:

> `=(TIMEVALUE(D3)-TIMEVALUE(C3))`

Here’s the breakdown:

*   **C3** represents the opening time (e.g., 9:00 AM).
*   **D3** represents the closing time (e.g., 5:00 PM).

![Google Sheets with TIMEVALUE turning text times into numbers so total time worked can be summed.](https://cdn.owox.ai/www/webflow/67872d115f2e284a79f2ce03_59410.png/public)

To display the **calculated duration in a readable format**, follow these steps:

*   Select the cells containing the formula results.
*   Go to **Format → Number**.
*   Choose **Duration**, so that the result reads as hours and minutes (for example 9:30:00).

![The Format menu in the toolbar being used to display summed times as a duration.](https://cdn.owox.ai/www/webflow/67872d11100905e8a17beff7_59412.png/public)

To add the durations up, use SUM on the column and keep the Duration format on the total. With the Time format, a total of 26 hours would show as 2:00.

### Convert a time with milliseconds to decimal hours

Google Sheets reads a value such as 1:12:23.432 as a time, milliseconds included. Because a time is a fraction of a day, multiplying by 24 gives **decimal hours**:

> `=D3 * 24`

The cell usually takes over the time format and shows something like 4:57:22. Set it to **Format → Number → Number** to see 1.2065. Multiply by 1440 instead for minutes, or by 86400 for seconds.

### Tracking response times using the MINUTE function

With the time of an inquiry in column D and the time of the response in column E, the difference of the minutes shows how long the reply took, as long as both fall within the same hour:

> `=MINUTE(E3) - MINUTE(D3)`

Here’s the explanation:

*   **D3:** Timestamp of the inquiry.
*   **E3:** Timestamp of the response.

![Google Sheets with MINUTE tracking response times between an inquiry timestamp and its response.](https://cdn.owox.ai/www/webflow/67872d12c8814b80c3b014b2_59416.png/public)

For response times that can cross the hour, use `=(E3-D3)*1440` and format the result as a number.

### Extracting seconds from a column of time values to analyze frequency

To see whether events cluster at a particular second, count how often the second of each timestamp occurs in the column:

> `=ARRAYFORMULA(COUNTIF(TEXT(D$3:D$12, "ss"), TEXT(SECOND(D3), "00")))`

Here’s the explanation:

*   **TEXT(D$3:D$12, “ss”):** Converts the timestamps in column D to their second values in a “ss” format.
*   **SECOND(D3):** Extracts the second value of the current row’s timestamp.
*   **TEXT(SECOND(D3), “00”):** Ensures the extracted second is in the “00” format, matching the range format for comparison.
*   **COUNTIF:** Counts how many times the extracted second occurs in the entire column.

![Google Sheets with SECOND extracting the seconds from a column of time values to spot patterns.](https://cdn.owox.ai/www/webflow/67872d1103f1318f90c43c0e_59418.png/public)

## Errors and wrong results in time calculations

### #VALUE!: the cell is not a time

⚠️ **Error:** A time function returns #VALUE! when the cell holds text that Google Sheets cannot read as a time.

✅ **Solution:** Check for stray characters and for a format the locale does not use. TIMEVALUE converts well-formed text times; anything else has to be cleaned first.

### #NUM!: a negative time

⚠️ **Error:** TIME returns #NUM! for a negative argument, and HOUR, MINUTE and SECOND return it for a negative value, which is what you get when you subtract a later time from an earlier one.

✅ **Solution:** Subtract the earlier time from the later one, or wrap the difference in MOD(…, 1) when the end can fall after midnight.

### The result shows as a time instead of a number

⚠️ **Error:** A formula such as `=(D3-C3)*24` should return 9.5 and shows 12:00:00 PM. The result cell took over the time format of the cells it refers to.

✅ **Solution:** Select the cell and choose **Format → Number → Number**.

### A total over 24 hours starts again from zero

⚠️ **Error:** A sum of durations that should be 26 hours shows as 2:00.

✅ **Solution:** Use the **Duration** format instead of Time. Time shows the time of day and wraps at midnight; Duration keeps counting.

![The Format → Number menu in Google Sheets, listing the Time, Date time and Duration formats.](https://cdn.owox.ai/www/webflow/67872d11c3ccab650c21d613_59440.png/public)

### Times in different time zones

⚠️ **Error:** Timestamps from two systems are an hour or more apart for the same event.

✅ **Solution:** A time in a cell carries no time zone. Bring all timestamps to one zone before comparing them, by adding or subtracting the offset: `=C3+TIME(3,0,0)` moves a time three hours forward. Functions that return the current moment use the time zone set under File → Settings.

## Related Google Sheets functions

Functions that are often used next to the time functions, each with its own guide:

*   [DATE Functions](/blog/articles/date-functions-google-sheets): Creates dates, extracts components, or converts dates to numbers, essential for date-based analysis.
*   [DAY/DAYS/DAYS360:](/blog/articles/day-days-days360-functions-google-sheets) Extracts the day, calculates days between dates, or uses a 360-day calendar, great for tracking intervals.
*   [NOW/TODAY:](/blog/articles/now-today-functions-google-sheets) Dynamically provides the current date or date and time for real-time tracking.
*   [WORKDAY/NETWORKDAYS:](/blog/articles/workday-networkdays-functions-google-sheets) Calculates workdays for project timelines.
*   [ARRAYFORMULA:](/blog/articles/array-formulas-google-sheets) Applies a formula across a range, automating repetitive calculations across rows or columns.
*   [IMPORTRANGE:](/blog/articles/importrange-google-sheets) Imports data from other spreadsheets.
*   [PIVOT:](/blog/articles/pivot-tables-google-sheets) Summarizes and visualizes data with pivot tables.
*   [AVERAGE:](/blog/articles/average-functions-google-sheets) Returns the mean of a range, including a range of durations.
*   [WEEKDAY/WEEKNUM:](/blog/articles/weekday-weeknum-functions-google-sheets) Returns the day of the week or the week number of the year for a date, useful for weekly reporting.
*   [MONTH/EOMONTH:](/blog/articles/month-eomonth-functions-google-sheets) Extracts the month from a date or returns the last day of a month, handy for monthly reports.

## Time formulas assume the timestamps are right

Every calculation above trusts the two cells it subtracts. In practice, timestamps are the least reliable column in an export: one system logs in UTC and another in local time, a CSV turns times into text, and a tool rounds everything to the minute. The subtraction still returns a clean duration, off by an hour or by a time zone.

The [OWOX Data Marts extension for Google Sheets](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/time-functions-google-sheets/screens/1.png/public)

What changes for the person who maintains the sheet:

*   **Refresh replaces re-export.** _Refresh current report_ in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
*   **The source is written on the sheet.** The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
*   **No SQL and no warehouse access needed.** The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. Converting time zones and deciding which timestamp counts as the start is done once, in the data mart, and the duration formulas in the sheet work on values that mean the same thing in every row. The extension comes with the OWOX Cloud plans of [OWOX Data Marts](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

How do I handle mismatched time formatting errors?

Mismatched time formatting errors often occur when time values are stored as text instead of time. To resolve this, use the TIMEVALUE function to convert text-based times into time values, or reformat the cells by selecting the correct time format from the "Format" menu. Double-check that time strings follow a consistent format.

What is the TIME function in Google Sheets used for?

The TIME function in Google Sheets is used to create a time value based on specific hour, minute, and second inputs. It allows you to easily build time values for scheduling or duration calculations by combining these elements.

Can the TIME function handle values over 24 hours?

No, the TIME function resets after 24 hours, as it is designed to work within the 24-hour clock. For handling times greater than 24 hours, you’ll need to manually adjust your calculations by dividing the total hours by 24 and keeping track of the resulting day count separately using other functions like INT.

How can I format time in Google Sheets?

To format time in Google Sheets, select the cell or range, go to the "Format" menu, and choose "Number," then "Time." You can also apply custom time formats through "More formats." Custom formats let you control how hours, minutes, seconds, or even AM/PM appear based on your needs.

What’s the difference between TIME and TIMEVALUE in Google Sheets?

The TIME function creates a time value from separate hour, minute, and second inputs. TIMEVALUE, on the other hand, converts a text string representing time (like "10:30 AM") into a time value. TIME is used to construct time, while TIMEVALUE converts a string into a usable time format.

How do I subtract time in Google Sheets?

Subtract the start from the end, for example =D3-C3, and format the result with Format > Number > Duration. If the end can fall after midnight, use =MOD(D3-C3, 1). For a plain number of hours, multiply the difference by 24 and format the cell as a number.

## Who wrote this

![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public)

[Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager

Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks.

[LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko)

[Google Sheets Tips](/blog/topics/google-sheets-tips)

## Related articles

[Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets)

[Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](/blog/articles/query-with-concatenate-google-sheets)

[Google Sheets Tips · QUERY with IMPORTRANGE in Google Sheets: formula, examples · October 2, 2026](/blog/articles/query-with-importrange-google-sheets)

[See all articles →](/blog/articles)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md
- [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md
- [dates and times](https://www.owox.com/blog/articles/dates-and-times-google-sheets) — /blog/articles/dates-and-times-google-sheets.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [DATE Functions](https://www.owox.com/blog/articles/date-functions-google-sheets) — /blog/articles/date-functions-google-sheets.md
- [DAY/DAYS/DAYS360:](https://www.owox.com/blog/articles/day-days-days360-functions-google-sheets) — /blog/articles/day-days-days360-functions-google-sheets.md
- [NOW/TODAY:](https://www.owox.com/blog/articles/now-today-functions-google-sheets) — /blog/articles/now-today-functions-google-sheets.md
- [WORKDAY/NETWORKDAYS:](https://www.owox.com/blog/articles/workday-networkdays-functions-google-sheets) — /blog/articles/workday-networkdays-functions-google-sheets.md
- [ARRAYFORMULA:](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md
- [IMPORTRANGE:](https://www.owox.com/blog/articles/importrange-google-sheets) — /blog/articles/importrange-google-sheets.md
- [PIVOT:](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md
- [AVERAGE:](https://www.owox.com/blog/articles/average-functions-google-sheets) — /blog/articles/average-functions-google-sheets.md
- [WEEKDAY/WEEKNUM:](https://www.owox.com/blog/articles/weekday-weeknum-functions-google-sheets) — /blog/articles/weekday-weeknum-functions-google-sheets.md
- [MONTH/EOMONTH:](https://www.owox.com/blog/articles/month-eomonth-functions-google-sheets) — /blog/articles/month-eomonth-functions-google-sheets.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md
- [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](https://www.owox.com/blog/articles/query-with-concatenate-google-sheets) — /blog/articles/query-with-concatenate-google-sheets.md
- [Google Sheets Tips · QUERY with IMPORTRANGE in Google Sheets: formula, examples · October 2, 2026](https://www.owox.com/blog/articles/query-with-importrange-google-sheets) — /blog/articles/query-with-importrange-google-sheets.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md