TIME()

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)

1. (60 * 60) gives number of seconds in 60 minutes (one hour).
2. (60 * 60) * [Hours] gives number of seconds in the time period expressed by the Hours column value.
3. FLOOR([Hours] / 24) gives whole days. See also: FLOOR()
4. MOD(FLOOR([Hours]), 24) gives remaining whole hours. See also: MOD()
5. FLOOR(MOD(((60 * 60) * [Hours]), (60 * 60)) / 60) gives remaining whole minutes.
6. MOD(((60 * 60) * [Hours]), 60) gives remaining seconds.
7. CONCATENATE(..., ":", ..., ":", ...) constructs a Text value formatted as a time value. Note that this only includes the time within one day; Time values cannot exceed 24 hours. See also CONCATENATE()
8. TIME(...) converts the Text value to a Time value.
9. (... - "00:00:00") subtracts a Time value from a Time value, giving a Duration value. A Duration value allows more than 24 hours.
10. ... + (FLOOR([Hours] / 24) * 24) adds the number of hours in the whole days from Hours.

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 - A Date, DateTime, or Time 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.

See also

Date and time expressions

DATE()

DATETIME()