DateTime from Date, DateTime, or Time

Returns DateTime from Date, DateTime, or Time. If a Time value is specified, which has no date component, returns a default date of 12/30/1899. If a Date value, which has no time component,  returns 12:00:00 AM. If the specified value is not a Date, DateTime, or Time value, returns a blank.

Sample usage

DATETIME("4/1/2010 3:14") returns 4/1/2010 3:14:00 AM (24-hour time if no AM/PM)

DATETIME("4/1/2010 15:14") returns 4/1/2010 3:14:00 PM (24-hour time)

DATETIME("4/1/2010 3:14 PM") returns 4/1/2010 3:14:00 PM (12-hour time with AM/PM)

DATETIME(NOW()) returns the current date & time from a DateTime value. See also: NOW()

DATETIME("4/1/2010") returns 4/1/2010 12:00:00 AM (a Date value has no time component, so a default is used).

DATETIME(TODAY()) returns today's date at 12:00:00 AM from a Date value (TODAY() returns a Date value, which has no time component, so a default is used).

DATETIME("3:14") : returns 12/30/1899 3:14:00 AM (a Time value has no date component, so a default is used).

DATETIME(TIMENOW()) : returns 12/30/1899 at the current time (TIMENOW() returns a Time value, which has no date component, so a default is used).

Common problems

DATETIME("Good morning, Martin!") returns blank because the textual input isn't a recognized temporal type and so cannot be converted.



  • when - A Date, DateTime, or Time value.

Note: Because temporal types are Text values with specific formats, any textual value may be supplied to DATETIME(), but non-temporal values will produce a blank result.

See also

Date and time expressions






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