How do I display TIME as HH:MM:SS instead of a decimal?

TIME returns a decimal, but most spreadsheets automatically format it as time when you enter the formula. If it displays as a decimal, right-click the cell, select Format Cells, and choose Time. Excel and Sheets both support this.

Can TIME handle times past midnight, like 25:30:00?

Yes. TIME(25,30,0) wraps around: 25 hours = 1 day + 1 hour, so it returns 1:30 AM. This is by design and lets you add durations without manually breaking them into days and time.

What's the difference between TIME and TIMEVALUE?

TIME takes numeric hour/minute/second arguments and returns a decimal. TIMEVALUE takes a text string (like "9:30 AM") and converts it to a decimal. Use TIME for math, TIMEVALUE for parsing text.

How do I create a time with just hours, or hours and minutes (no seconds)?

Pass 0 for unused arguments: TIME(9,0,0) for 9 AM, or TIME(14,30,0) for 2:30 PM. The third argument defaults to 0 if omitted in most spreadsheet implementations.

TIME function

TIME converts hours, minutes, and seconds into a decimal time value between 0 and 1, representing a time on a 24-hour clock.

=TIME(hour, minute, second)

Generate a TIME formula

Describe what you need. The generator will reach for TIME where TIME is the right tool, and tell you when it is not.

Arguments

How TIME reads its arguments

Argument	Required	Description
hour	Required	An integer from 0 to 23 representing the hour of the day; values outside this range wrap around (24 becomes 0, 25 becomes 1).
minute	Required	An integer from 0 to 59 representing the minute within the hour; values ≥60 carry over to the next hour.
second	Required	An integer from 0 to 59 representing the second within the minute; values ≥60 carry over to the next minute.

Returns

A decimal number representing a time of day, typically displayed as a time format (e.g., 14:30:00).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Create a timestamp for end-of-business order cutoff

Order ID	Region	Rep	Order Date	Cutoff Timestamp
1001	North	Alice	2024-01-15	=DATE(A2)+TIME(17,0,0)
1002	South	Bob	2024-01-15	=DATE(A2)+TIME(17,0,0)
1003	East	Carol	2024-01-15	=DATE(A2)+TIME(17,0,0)

=DATE(A2)+TIME(17,0,0)

Result: 2024-01-15 17:00:00 (5:00 PM on the order date)

TIME(17,0,0) creates the decimal value for 5:00 PM. When added to the order date using DATE(), it combines into a full datetime timestamp. This is useful for setting a daily order cutoff time across all regions.

2. Represent a morning follow-up call time

Order ID	Region	Rep	Units	Unit Price	Follow-up Time
1001	North	Alice	5	25	=TIME(9,30,0)
1002	South	Bob	3	40	=TIME(9,30,0)
1003	East	Carol	8	20	=TIME(9,30,0)

=TIME(9,30,0)

Result: 0.395833 (displays as 09:30:00 or 9:30 AM)

TIME(9,30,0) returns the decimal representation of 9:30 AM. Spreadsheets internally store times as decimals where 0 = midnight and 1 = 11:59:59 PM. When formatted as time, this decimal displays as 9:30 AM, making it easy to schedule uniform follow-up calls across the sales team.

3. Combine order date with next-day morning call

Order ID	Region	Rep	Order Date	Next-Day Call
1001	North	Alice	2024-01-15	=DATE(A2+1)+TIME(8,15,0)
1002	South	Bob	2024-01-15	=DATE(A2+1)+TIME(8,15,0)
1003	East	Carol	2024-01-15	=DATE(A2+1)+TIME(8,15,0)

=DATE(A2+1)+TIME(8,15,0)

Result: 2024-01-16 08:15:00 (8:15 AM the next day)

A2+1 advances the date by one day, and TIME(8,15,0) adds 8 hours and 15 minutes. Together they create a follow-up call timestamp for 8:15 AM the morning after each order, automating a standard sales workflow across all reps.

Common errors

Which TIME error are you seeing?

Error	Why it happens	How to fix it
#NUM!	Hour, minute, or second argument is negative (e.g., TIME(-1,30,0) or TIME(10, 5, -10)).	Ensure all three arguments are non-negative. Use ABS() to convert negative values, or use MAX(argument, 0) to clamp them to zero.
#VALUE!	One or more arguments are text, arrays, or non-numeric types instead of numbers (e.g., TIME("09",30,0) or TIME(A1:A3, 0, 0)).	Use single numeric cells only. Convert text to a number with VALUE(): TIME(VALUE(A1), B1, C1). Do not pass ranges or arrays.
#REF!	One or more cells that TIME references have been deleted or moved (e.g., =TIME(A1, B1, C1) where A1 was deleted).	Restore the deleted column or cell, or update the formula to reference valid cells. Use Ctrl+Z to undo a recent deletion if needed.

Tips and when to use something else

TIME always treats arguments modulo their valid range: TIME(25,70,80) wraps to TIME(1,10,20) (1:10:20 AM), so be intentional if you're passing overflowed values.
Combine TIME with DATE to create full datetime stamps; DATE alone gives you only the day, TIME alone gives you only the time within a 24-hour period.
If you need to extract hours, minutes, or seconds from an existing time value, use HOUR(), MINUTE(), and SECOND() instead—they are the inverse of TIME.
For durations or elapsed time, use DATEDIF() or simple subtraction (end_time - start_time) rather than TIME; TIME is for representing clock times, not intervals.

Related functions

DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.NOWReturns the current date and time as a timestamp value that updates automatically each time the spreadsheet recalculates.DATEVALUEDATEVALUE converts text strings representing dates into serial numbers Excel and Sheets recognize, enabling date calculations and sorting.TIMEVALUEConverts time text into a decimal number enabling calculations with warehouse receipt times, shipping schedules, and processing timestamps.HOURHOUR extracts the hour component from a time value, returning a number between 0 and 23 representing the hour of the day.

Frequently asked questions

How do I display TIME as HH:MM:SS instead of a decimal?: TIME returns a decimal, but most spreadsheets automatically format it as time when you enter the formula. If it displays as a decimal, right-click the cell, select Format Cells, and choose Time. Excel and Sheets both support this.
Can TIME handle times past midnight, like 25:30:00?: Yes. TIME(25,30,0) wraps around: 25 hours = 1 day + 1 hour, so it returns 1:30 AM. This is by design and lets you add durations without manually breaking them into days and time.
What's the difference between TIME and TIMEVALUE?: TIME takes numeric hour/minute/second arguments and returns a decimal. TIMEVALUE takes a text string (like "9:30 AM") and converts it to a decimal. Use TIME for math, TIMEVALUE for parsing text.
How do I create a time with just hours, or hours and minutes (no seconds)?: Pass 0 for unused arguments: TIME(9,0,0) for 9 AM, or TIME(14,30,0) for 2:30 PM. The third argument defaults to 0 if omitted in most spreadsheet implementations.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17