ConvertSite
Date functions

TIMEVALUE

Converts a time string to a fractional number between 0 and 1, where 0 represents midnight and 1 represents the next midnight. Use TIMEVALUE to convert user-entered time strings into values you can combine with dates or use in calculations.

Syntax

TIMEVALUE(timeString)

  • timeString (String): A time in any of these formats:

    • "HH:MM" (24-hour)

    • "HH:MM:SS" (24-hour with seconds)

    • "HH:MM AM/PM" (12-hour)

Returns

Number between 0 and 1 representing the time as a fraction of a day.

  • 0 = midnight (00:00)

  • 0.5 = noon (12:00)

  • 1 = next midnight (24:00)

Examples

Convert user time input

TIMEVALUE("14:30")

Returns 0.604166 (14.5 hours ÷ 24 hours).

Combine with date to create datetime

DATETIME(DATE(2024, 12, 25), TIMEVALUE("09:30"))

Creates December 25, 2024 at 9:30 AM.

Parse 12-hour format

TIMEVALUE("6:00 PM")

Returns 0.75 (18:00 = 18 ÷ 24).

Set appointment from user input

APPOINTMENT_DATETIME = DATETIME(APPOINTMENT_DATE, TIMEVALUE(TIME_INPUT))

Merges a date field with a time string entered by the user.

Useful for combining user-entered times with dates, or for time calculations where fractions are easier to work with than hour/minute components.

The fractional result can be used directly in DATETIME or stored for later calculations.

Common Patterns

Business hours start:

BUSINESS_START = DATETIME(CURRDATE(), TIMEVALUE("09:00"))

Event start from form inputs:

EVENT_START = DATETIME(EVENT_DATE, TIMEVALUE(START_TIME))

Deadline at end of day:

DEADLINE = DATETIME(DUE_DATE, TIMEVALUE("23:59"))

Calculate time difference:

START = TIMEVALUE("09:00")
END = TIMEVALUE("17:30")
HOURS_DIFF = (END - START) * 24

Format Examples

Input

Result

Explanation

"00:00"

0

Midnight

"12:00"

0.5

Noon (halfway through day)

"6:00 AM"

0.25

6 AM (quarter of day)

"6:00 PM"

0.75

6 PM (three-quarters of day)

"14:30"

0.604166

2:30 PM (14.5 ÷ 24)

"09:15:30"

0.385416

9:15:30 AM with seconds

Common Mistakes

  • Forgetting quotes: Use TIMEVALUE("14:30") not TIMEVALUE(14:30). Input must be a string.

  • Invalid format: Use "HH:MM" or "HH:MM AM/PM". Random formats like "2pm" or "14h30" will error.

  • Expecting time object: TIMEVALUE returns a number, not a time object. Use it with DATETIME to create a full datetime.

  • Using standalone for display: The decimal output (0.604166) isn't useful for display. Combine with DATE/DATETIME first.

See Also

DATETIME · CURRTIME · HOUR · MINUTE · SECOND

Was this helpful?
Was this helpful?