Синтаксис
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.
- формат—(необязательно)[optional] Specifies whether the time is displayed using a 12-hour (0) or 24-hour (1) clock.
- 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).
Пример использования
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
Примечания об использовании
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. Например:
=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.
Столбец типа "Текст или число"
| TIME formula inputs | Returns | Time reference | Комментарии |
|---|---|---|---|
| =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) |
Столбец типа "Дата"
| TIME formula inputs | Returns | Комментарии |
|---|---|---|
| =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 |
Столбец типа "Список контактов"
| TIME formula inputs | Returns | Time reference | Исполнитель | Комментарии |
|---|---|---|---|---|
| =TIME(1, 0) + " - " + [Assigned To]6 | 1:00 AM - John Doe | 100 % | John Doe | Append a string of information to a time object |
Остались вопросы?
Используйте шаблон Руководство по работе с формулами, чтобы просмотреть дополнительные ресурсы и изучить более 100 формул. Руководство содержит глоссарий, описывающий каждую функцию, обращение с которой вы сможете отработать на практике, и примеры как часто используемых, так и более сложных функций.
Изучить примеры того, как эту функцию применяют другие пользователи Smartsheet, или задать интересующий вопрос можно в Сообществе Smartsheet.