TIME()

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

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()

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false
false