Date & Time Functions

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

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

    ADDDAYS("2020-01-01",10)
    Try this example
    date (string)
    String representing the date.
    days (number)
    Number of days to add or substract
  • ADDHOURS

    Returns a new date adding a number for hours

    ADDHOURS("2020-01-01 20:30:40",2)
    Try this example
    datetime (string)
    String representing the date-time
    hours (number)
    Number of hours to add or substract
  • ADDMINUTES

    Returns a new date time adding a number of minutes

    ADDMINUTES("2020-01-01 20:30:40",2)
    Try this example
    datetime (string)
    String representing the date-time
    minutes (number)
    Number of minutes to add or substract
  • ADDMONTHS

    Returns a new date time adding a number of months

    ADDMONTHS("2020-01-01 20:30:40",2)
    Try this example
    datetime (string)
    String representing the date or date-time
    months (number)
    Number of months to add or substract
  • ADDSECONDS

    Returns a new date time adding a number of months

    ADDSECONDS("2020-01-01 20:30:40",2)
    Try this example
    datetime (string)
    String representing the date-time
    seconds (number)
    Number of seconds to add or substract
  • ADDYEARS

    Returns a new date time adding a number of years

    ADDYEARS("2020-01-01 20:30:40",2)
    Try this example
    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)
    Try this example
    year (number)
    The year component of the date.
    month (number)
    The month component of the date.
  • DATEDIF

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

    DATEDIF(DATE(1969, 7, 16), DATE(1969, 7, 24), "D")
    Try this example
    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")
    Try this example
    date_string (string)
    The string representing the date.
  • DATE_FORMAT

    Format a local time/date

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

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

    DAY(DATE(1969,7,20))
    Try this example
    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")
    Try this example
    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)
    Try this example
    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)
    Try this example
    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)
    Try this example
    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.
  • FORMATDATE

    Returns a date formatted according to the specified format

    FORMATDATE("2020-01-01","d.m.Y","UTC")
    Try this example
    date (string)
    Date string
    format (string)
    Output format
  • FORMAT_DATE

    Format a local time/date

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

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

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

    Return the year

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

    Return the hour

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

    Return the minutes

    GETMINUTES("2020-01-01 20:30:40")
    Try this example
    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")
    Try this example
    date (string)
    Date string
  • GETSECONDS

    Return the seconds, according to local time

    GETSECONDS("2020-01-01 20:30:40")
    Try this example
    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")
    Try this example
    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))
    Try this example
    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")
    Try this example
    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")
    Try this example
    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))
    Try this example
    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))
    Try this example
    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))
    Try this example
    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))
    Try this example
    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.

  • PARSEDATE

    Returns a date in ISO 8601 format from the specified format

    PARSEDATE("6.1.20 13","d.m.y H")
    Try this example
    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))
    Try this example
    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")
    Try this example
    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)
    Try this example
    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")
    Try this example
    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")
    Try this example
    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")
    Try this example
    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")
    Try this example
    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")
    Try this example
    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")
    Try this example
    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)
    Try this example
    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)
    Try this example
    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")
    Try this example
    time_string (string)
    The string that holds the time representation.
  • TODAY

    Returns the current date as a date value.

  • WEEKDAY

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

    WEEKDAY(DATE(1969,7,20))
    Try this example
    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)
    Try this example
    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)
    Try this example
    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))
    Try this example
    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)
    Try this example
    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.