Syntax
TIME(time_value, [format], [precision])
- time_value—The value to create time with. Must be provided as a string or numbers representing hours, minutes, and seconds. When using a string, hours, minutes, and seconds must be separated by colons “HH:MM AM” or “HH:MM:SS PM” When using numbers, the values must be separated by commas, all three time intervals (hours, minutes, seconds) must be entered to apply optional format and precision values.
- format—[optional][optional] Specifies whether the time is displayed using a 12-hour (0) or 24-hour (1) clock.
- precision—[optional][optional] Requires format to be set first. Specifies the interval of time to return in either hh (1), hh:mm (2), or hh:mm:ss (3).
Sample usage
Example 1 → TIME("4:30 PM", 1, 3) Syntax → TIME(time_value, [format], [precision])
Example 2 → TIME(4, 30, 10, 0) Syntax → TIME(time_value, [format])
Example 3 → TIME("8:30")+5 Syntax → TIME("hours:min")+min
Usage notes
You can input the time_value argument in one of these three distinct formats:
- String Format: Input numbers and text to represent time.
- For example, =TIME("8:30 PM", 1) returns 20:30.
- Using additional optional arguments, =TIME("8:30 PM", 1,3) returns 20:30:00.
- Cell Reference: Cell references usually include a time value extracted from a date/time value (from a system column or a text/number field) or a distinct time value entered in a cell through a dropdown list.
Dropdown lists are recommended when adding time values to a cell via a form since they ensure proper formatting.
- Numeric Inputs: Use up to three numbers for hours, minutes, and seconds, separated by commas.
- For instance, =TIME(9, 30,15) returns 9:30:15 AM.
- All three components—hour, min, and sec—must be specified when using optional arguments to avoid a #UNPARSEABLE error or unexpected output.
To extract the time component from System columns such as Modified (Date) and Created (Date), use the RIGHT() function set to return the last 8 characters, like so:
- =TIME(RIGHT(Modified@row, 8)).
TIME outputs a time object that you can utilize in larger formulas. For example, to send morning emails before 9:00 AM, the formula could be:
=IF(TIME([Time of Day]1) < TIME(9, 0, 0), "Send morning mail!")
If you omit the optional format and precision arguments, the TIME function's output will depend on how time_value is expressed. For example:
=TIME("15") returns 15, whereas =TIME("3 PM") returns 3 PM.
Examples
When performing calculations, the order in which you use TIME() with other values matters. The following chart shows example results based on the order in which you use TIME and the column type.
If you receive an #UNPARSEABLE error after copy-pasting, try typing the formula manually into the cell instead.
Text/Number column type
| TIME formula inputs | Returns | Time reference | Comments |
|---|---|---|---|
| =TIME(10, 0) | 10:00 AM | 10:00 AM | Create a time object |
| =TIME([Time Reference]@row, 1) | 11:45 | 11:45 AM | Create a time object referencing another cell |
| =TIME([Time Reference]@row, 0) + 5 | 8:35 AM | 08:30AM + 5 minutes | Add 5 minutes to a time object - example 1 |
| =TIME("8:30")+5 | 08:35 | 08:30 + 5 minutes | Add 5 minutes to a time object - example 2 |
| =TIME("8:30 PM",1) + 5 | 8:35 PM | 20:30 + 5 minutes | Add 5 minutes to a time object - example 3 |
| =TIME("2:55 PM", 0) | 2:55 PM | 14:55 | Express a time object in a 12 hour format |
| =TIME("2:55 PM", 1) | 14:55 | 02:55 PM | Express a time object in a 24 hour format |
| =TIME([Time Reference]@row) - 270 | 3:30 AM | 03:30 | Subtract 270 minutes from a time object |
| =TIME(RIGHT(Modified@row, 8)) | 10:54 AM | 10:54 | Convert last modified time into time object |
| =TIME(RIGHT(Modified@row, 8)) + 4 | 10:59 AM | 10:59 | Add 4 minutes to last modified time |
| =(TIME([Time Reference]10) - TIME(Return10)) | 0.1875 | Difference between two times as a fraction of a working day | |
| =(TIME([Time Reference]10) - TIME(Return10)) * 24 | 4.500 | Convert difference between two times expressed in hours (only works when working day = 24 hrs) | |
| =(TIME([Time Reference]10) - TIME(Return10)) * 1440 | 270 | Convert difference between two times expressed in minutes (only works when working day = 24 hrs) |
Date column type
| TIME formula inputs | Returns | Comments |
|---|---|---|
| =ROUNDDOWN((TIME([Time Reference]10) - TIME(Return10)) * 24) + " hour(s) and " + VALUE(RIGHT((TIME([Time Reference]10) - TIME(Return10)) * 24, FIND(".", (TIME([Time Reference]10) - TIME(Return10)) * 24))) * 60 + " minutes" | 4 hour(s) and 30 minutes | Convert a time difference into a string of hours and minutes (only works when working day = 24 hrs) |
| =DATEONLY(Modified9) - DATEONLY(Modified10) + " day(s), " + " " + ROUNDDOWN((TIME(RIGHT(Modified9, 8)) - TIME(RIGHT(Modified10, 8))) * 24) + " hour(s) and " + (((TIME(RIGHT(Modified9, 8)) - TIME(RIGHT(Modified10, 8))) * 24) - ROUNDDOWN((TIME(RIGHT(Modified9, 8)) - TIME(RIGHT(Modified10, 8))) * 24)) * 60 + " minutes" | 0 day(s), 1 hour(s) and 31 minutes
| Convert a time difference into a string of days, hours and minutes (only works when working day = 24 hrs) |
| ="Due Date: " + DATEONLY(Created@row) + " @" + (TIME(RIGHT(Created@row, 8)) + 120) | Due Date: 08/28/23 @11:54 AM | Use a created date to generate a due date + time assuming a SLA for the task in minutes |
Contact list column type
| TIME formula inputs | Returns | Time reference | Assigned to | Comments |
|---|---|---|---|---|
| =TIME(1, 0) + " - " + [Assigned To]6 | 1:00 AM - John Doe | 1:00 | John Doe | Append a string of information to a time object |
Still need help?
Use the Formula Handbook template to find more support and resources, view 100+ formulas, review a glossary of every function that you can practice working with in real time, and find examples of commonly used and advanced formulas.
You can find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.