Returns the converted value if it is recognized as a Date
, DateTime
, or Time
value, or blank if not. If a Date
value is specified, which has no time component, returns 12:00:00 AM
.
Sample usage
TIME("3:14")
(24-hour time if no AM/PM): 3:14:00 AM
TIME("15:14")
(24-hour time): 3:14:00 PM
TIME("3:14 PM")
(12-hour time with AM/PM): 3:14:00 PM
TIME(TIMENOW())
: The current time from a Time
value. See also: TIMENOW()
TIME("4/1/2010")
: 12:00:00 AM
(a Date
value has no time component, so a default is used).
TIME(TODAY())
: 12:00:00 AM
(TODAY()
returns a Date
value, which has no time component, so a default is used).
TIME("4/1/2010 3:14")
: 3:14:00 AM
TIME(NOW())
: The current time from a DateTime
value. See also: NOW()
Duration from Decimal hours
Convert a number of hours expressed as a Decimal
value to a Duration
value.
(
TIME(
CONCATENATE(
MOD(FLOOR([Hours]), 24),
":",
FLOOR(MOD(((60 * 60) * [Hours]), (60 * 60)) / 60),
":",
MOD(((60 * 60) * [Hours]), 60)
)
)
- "00:00:00"
)
+ (FLOOR([Hours] / 24) * 24)
(60 * 60)
gives number of seconds in 60 minutes (one hour).(60 * 60) * [Hours]
gives number of seconds in the time period expressed by theHours
column value.FLOOR([Hours] / 24)
gives whole days. See also:FLOOR()
MOD(FLOOR([Hours]), 24)
gives remaining whole hours. See also:MOD()
FLOOR(MOD(((60 * 60) * [Hours]), (60 * 60)) / 60)
gives remaining whole minutes.MOD(((60 * 60) * [Hours]), 60)
gives remaining seconds.CONCATENATE(..., ":", ..., ":", ...)
constructs aText
value formatted as a time value. Note that this only includes the time within one day;Time
values cannot exceed 24 hours. See alsoCONCATENATE()
TIME(...)
converts theText
value to aTime
value.(... - "00:00:00")
subtracts aTime
value from aTime
value, giving aDuration
value. ADuration
value allows more than 24 hours.... + (FLOOR([Hours] / 24) * 24)
adds the number of hours in the whole days fromHours
.
See also: CONCATENATE()
, FLOOR()
, MOD()
Common Problems
TIME("Good morning, Martin!")
: Returns blank because the textual input isn't a recognized temporal type and so cannot be converted.
Syntax
TIME(when)
when
- ADate
,DateTime
, orTime
value.
Notes
When dates are used as constant values within an expression, they must be represented in the MM/DD/YYYY
format. This doesn't mean your spreadsheet data must use dates in this format: the date representation in your spreadsheets is determined by the locale/language of the spreadsheet.
Because temporal types are Text
values with specific formats, any textual value may be supplied to TIME()
, but non-temporal values will produce a blank result.