- time_value
- [precision]
- [format]
- time_value—The value to create time with. Must be provided as a string or numbers (separated by commas or colons depending on time_value format used) representing hours, minutes, and seconds.
- precision—[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).
- format—[optional]Specifies whether the time is displayed using a 12-hour (0) or 24-hour (1) clock.
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("20:30:00", 1) returns 20:30:00.
- Using additional optional arguments, =TIME("20:30:00", 0) returns 8:30:00 PM.
- Cell Reference: Reference a cell, typically in a Date/Time Column (e.g., start and end date columns). Another common reference source is cells that already contain TIME() function formulas.
- 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 to avoid an #UNPARSEABLE error when using optional arguments.
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 other 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.
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.
| Column or Data Type | TIME formula inputs | Returns | Assigned to | Comments |
|---|---|---|---|---|
| Text/Number | =TIME(10, 0, 0) | 10:00 AM | Create a time object | |
| Text/Number | =TIME([Time Reference]@row, 1) | 11:45 | Create a time object referencing another cell | |
| Text/Number | =TIME([Time Reference]@row, 0) + 5 | 8:35 AM | Add 5 minutes to a time object - example 1 | |
| Text/Number | =TIME("8:30")+5 | 08:35 | Add 5 minutes to a time object - example 2 | |
| Text/Number | =TIME("8:30 PM",1) + 5 | 8:35 PM | Add 5 minutes to a time object - example 3 | |
| Text/Number | =TIME("2:55 PM", 0) | 2:55 PM | Express a time object in a 12 hour format | |
| Text/Number | =TIME("2:55 PM", 1) | 14:55 | Express a time object in a 24 hour format | |
| Contact List | =TIME(1, 0) + " - " + [Assigned To]6 | 1:00 AM - John Doe | John Doe | Append a string of information to a time object |
| Text/Number | =TIME([Time Reference]@row) - 270 | 3:30 AM | Subtract 270 minutes from a time object | |
| Text/Number | =TIME(RIGHT(Modified@row, 8)) | 10:54 AM | Convert last modified time into time object | |
| Text/Number | =TIME(RIGHT(Modified@row, 8)) + 4 | 10:59 AM | Add 4 minutes to last modified time | |
| Text/Number | =(TIME([Time Reference]10) - TIME(Return10)) | 0.1875 | Difference between two times as a fraction of a working day | |
| Text/Number | =(TIME([Time Reference]10) - TIME(Return10)) * 24 | 4.500 | Convert difference between two times expressed in hours (only works when working day = 24 hrs) | |
| Text/Number | =(TIME([Time Reference]10) - TIME(Return10)) * 1440 | 270 | Convert difference between two times expressed in minutes (only works when working day = 24 hrs) | |
| Date | =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) | |
| Date | =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) | |
| Date | ="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 |