# Date & Time Functions

Creating Dates & Times. Performing Calculations with Dates. Current Date & Time. Extracting Components of Time & Date.

Returns a new date adding a number of days to a date

 date (string) String representing the date. days (number) Number of days to add or substract

Returns a new date adding a number for hours

 datetime (string) String representing the date-time hours (number) Number of hours to add or substract

Returns a new date time adding a number of minutes

 datetime (string) String representing the date-time minutes (number) Number of minutes to add or substract

Returns a new date time adding a number of months

 datetime (string) String representing the date or date-time months (number) Number of months to add or substract

Returns a new date time adding a number of months

 datetime (string) String representing the date-time seconds (number) Number of seconds to add or substract

Returns a new date time adding a number of years

 date (string) String representing the date or date-time years (number) Number of years to add or substract
• DATE

Converts a year, month, and day into a date.

DATE(1969,7,20)
 year (number) The year component of the date. month (number) The month component of the date.
• DATE_FORMAT

Format a local time/date

DATE_FORMAT("d.m.Y","2020-01-01")
 format (string) The format of the outputted date string datetime (string) Datetime string
• DATEDIF

Calculates the number of days, months, or years between two dates.

DATEDIF(DATE(1969, 7, 16), DATE(1969, 7, 24), "D")
 start_date (date) The start date to consider in the calculation. Must be a reference to a variable containing a DATE, a function returning a DATE type, or a number. end_date (date) The end date to consider in the calculation. Must be a reference to a variable containing a DATE, a function returning a DATE type, or a number.
• DATEVALUE

Converts a provided date string in a known format to a date value.

DATEVALUE("1969-7-20")
 date_string (string) The string representing the date.
• DAY

Returns the day of the month that a specific date falls on, in numeric format.

DAY(DATE(1969,7,20))
 date (any) The date from which to extract the day. Must be a reference to a variable containing a date, a function returning a date type, or a number.
• DAYS

The DAYS function returns the number of days between two dates.

DAYS("7/24/1969", "7/16/1969")
 end_date (date) The end of the date range. start_date (date) The start of the date range.
• DAYS360

Returns the difference between two days based on the 360-day year used in some financial interest calculations.

DAYS360(DATE(1969,7,16),DATE(1969,7,24),1)
 start_date (date) The start date to consider in the calculation. Must be a reference to a variable containing a date, a function returning a date type, or a number. end_date (date) The end date to consider in the calculation. Must be a reference to a variable containing a date, a function returning a date type, or a number.
• EDATE

Returns a date a specified number of months before or after another date.

EDATE(DATE(1969,7,20), 1)
 start_date (date) The date from which to calculate the result. months (number) The number of months before (negative) or after (positive) start_date to calculate.
• EOMONTH

Returns a date representing the last day of a month which falls a specified number of months before or after another date.

EOMONTH(DATE(1969, 7, 20), 1)
 start_date (date) The date from which to calculate the the result. months (number) The number of months before (negative) or after (positive) start_date to consider. The last calendar day of the calculated month is returned.
• FORMAT_DATE

Format a local time/date

FORMAT_DATE("d.m.Y","2020-01-01")
 format (string) The format of the outputted date string datetime (string) Datetime string
• FORMATDATE

Returns a date formatted according to the specified format

FORMATDATE("2020-01-01","d.m.Y","UTC")
 date (string) Date string format (string) Output format
• GETDAY

Return the day of the week. Sunday is 0, Monday is 1, and so on.

GETDAY("2020-01-01")
 date (string) Date string
• GETFULLYEAR

Return the year

GETFULLYEAR("2020-01-01")
 date (string) Date string
• GETHOURS

Return the hour

GETHOURS("2020-01-01 20:30:40")
 datetime (string) Datetime string, or Date string or Time string
• GETMINUTES

Return the minutes

GETMINUTES("2020-01-01 20:30:40")
 datetime (string) Datetime string, or Date string or Time string
• GETMONTH

Return the month. Note: January is 0, February is 1, and so on.

GETMONTH("2020-01-01")
 date (string) Date string
• GETSECONDS

Return the seconds, according to local time

GETSECONDS("2020-01-01 20:30:40")
 datetime (string) Datetime string, or Date string or Time string
• GETTIME

Return the number of milliseconds since 1970/01/01. Unix time

GETTIME("2020-01-01 20:30:40")
 datetime (string) Datetime string or Date string or Time string
• HOUR

Returns the hour component of a specific time, in numeric format.

HOUR(TIME(11,40,59))
 time (time) The time from which to calculate the hour component. Must be a reference to a variable containing a date/time, a function returning a date/time type, or a number.
• IF_DATE_AFTER

Determines whether the first date is after the second

IF_DATE_AFTER("2020-01-02","2020-01-03")
 datetime1 (string) Datetime string datetime2 (string) Datetime string
• IF_DATE_EQUAL

Determines whether both dates are equal

IF_DATE_EQUAL("2020-01-01","2020-01-01")
 datetime1 (string) Datetime string datetime2 (string) Datetime string
• ISOWEEKNUM

Returns the number of the ISO (International Organization for Standardization) week of the year where the provided date falls.

ISOWEEKNUM(DATE(1969, 7, 20))
 date (date) The date for which to determine the ISO week number. Must refer to a variable containing a date, number, or function returning a date type.
• MINUTE

Returns the minute component of a specific time, in numeric format.

MINUTE(TIME(11,40,59))
 time (time) The time from which to calculate the minute component. Must be a reference to a variable containing a date/time, a function returning a date/time type, or a number.
• MONTH

Returns the month of the year a specific date falls in, in numeric format.

MONTH(DATE(1969, 7, 20))
 date (date) The date from which to extract the month. Must be a reference to a variable containing a date, a function returning a date type, or a number.
• NETWORKDAYS

Returns the number of net working days between two provided days.

NETWORKDAYS(DATE(1969,7,16),DATE(1969,7,24))
 start_date (date) The start date of the period from which to calculate the number of net working days. end_date (date) The end date of the period from which to calculate the number of net working days.
• NOW

Returns the current date and time as a date value.

NOW()
• PARSEDATE

Returns a date in ISO 8601 format from the specified format

PARSEDATE("6.1.20 13","d.m.y H")
 date (string) String representing the date. format (string) Format according to the tokens representation
• SECOND

Returns the second component of a specific time, in numeric format.

SECOND(TIME(11,40,59))
 time (time) The time from which to calculate the second component. Must be a reference to a variable containing a date/time, a function returning a date/time type, or a number.
• SETDATE

Set the day of the month

SETDATE(15,"2020-01-01 20:30:40")
 new_date (number) An integer representing the day of a month. datetime (string) Optional: Datetime string or Date string. Otherwise current date will be used
• SETDAY

Set the day of the month using number of English name

SETDAY("2020-01-01 20:30:40",15)
 datetime (string) Datetime string or Date string. new_day (any) An integer representing the day of a month 1-7 or English name of the day (monday - sunday)
• SETFULLYEAR

Set the year (four digits for dates between year 1000 and 9999) of the date string

SETFULLYEAR(2021,"2020-01-01 20:30:40")
 new_year (number) A value representing the year, negative values are not allowed datetime (string) Optional: Datetime string or Date string. Otherwise current date will be used
• SETHOURS

Sets the hour of a date string.

SETHOURS(15,"2020-01-01 20:30:40")
 new_hour (number) An integer representing the hour. 0-23 datetime (string) Optional: Datetime string or Date string. Otherwise current date will be used
• SETMINUTES

Sets the minutes of a date string.

SETMINUTES(17,"2020-01-01 20:30:40")
 new_minutes (number) An integer representing the minutes. 0-59 datetime (string) Optional: Datetime string or Date string. Otherwise current date will be used
• SETMONTH

Sets the month of a date string. January is 0, February is 1, and so on.

SETMONTH(4,"2020-01-01 20:30:40")
 new_month (number) An integer representing the month. 0-11 datetime (string) Optional: Datetime string or Date string. Otherwise current date will be used
• SETSECONDS

Sets the seconds of a date string.

SETSECONDS(35,"2020-01-01 20:30:40")
 new_seconds (number) An integer representing the seconds datetime (string) Optional: Datetime string or Date string. Otherwise current date will be used
• SETTIME

Add N seconds to January 1, 1970, and display the new date and time

SETTIME(1332403882,"2020-01-01 20:30:40")
 add_seconds (number) The number of seconds to be added to, or subtracted from, midnight January 1, 1970 datetime (string) Optional: Datetime string or Date string. Otherwise January 1, 1970 will be used
• SETYEAR

Set the year (four digits for dates between year 1000 and 9999) of the date string

SETYEAR("2020-01-01 20:30:40",2021)
 datetime (string) Datetime string or Date string. new_year (number) A value representing the year, negative values are not allowed
• TIME

Converts an hour, minute, and second into a time.

TIME(11,40,59)
 hour (number) The hour component of the time. minute (number) The minute component of the time.
• TIMEVALUE

Returns the fraction of a 24-hour day the time represents.

TIMEVALUE("1969-7-20")
 time_string (string) The string that holds the time representation.
• TODAY

Returns the current date as a date value.

TODAY()
• WEEKDAY

Returns a number representing the day of the week of the date provided.

WEEKDAY(DATE(1969,7,20))
 date (date) The date for which to determine the day of the week. Must be a reference to a variable containing a date, a function returning a date type, or a number. type (number) Optional: A number indicating which numbering system to use to represent weekdays. By default counts starting with Sunday = 1
• WEEKNUM

Returns a number representing the week of the year where the provided date falls.

WEEKNUM(DATE(1969, 7, 20), 1)
 date (date) The date for which to determine the week number. Must be a reference to a variable containing a date, a function returning a date type, or a number. type (number) Optional: A number representing the day that a week starts on as well as the system used for determining the first week of the year (1=Sunday, 2=Monday).
• WORKDAY

Calculates the end date after a specified number of working days.

WORKDAY(DATE(1969,7,20), 4)
 start_date (date) The date from which to begin counting. num_days (number) The number of working days to advance from start_date. If negative, counts backwards.
• YEAR

Returns the year specified by a given date.

YEAR(DATE(1969,7,20))
 date (date) The date from which to calculate the year. Must be a variable reference to a variable containing a date, a function returning a date type, or a number.
• YEARFRAC

Returns the number of years, including fractional years, between two dates using a specified day count convention.

YEARFRAC(DATE(1969,7,16),DATE(1969,7,24),1)
 start_date (date) The start date to consider in the calculation. Must be a reference to a variable containing a date, a function returning a date type, or a number. end_date (date) The end date to consider in the calculation. Must be a reference to a variable containing a date, a function returning a date type, or a number.