Date
DATE
The DATE function is a versatile date utility in the formula editor, used to either format a date value or construct a date from year, month, and day components.
Syntax 1 (Format a date): DATE(DateValue, Format, [InputFormat])
DateValue: The date or string to be formatted. Format: The output date format (e.g., "DD-MM-YYYY"). InputFormat (optional): The format of the input string, if DateValue is a string.
Example: DATE("2025-08-26", "DD/MM/YYYY") The function will return "26/08/2025".
Syntax 2 (Construct a date): DATE(Year, Month, Day)
Year: The year as a number. Month (optional): The month as a number (0-based, January is 0). Day (optional): The day as a number.
Example: DATE(2025, 7, 26) The function will return a Date object for August 26, 2025.
DATEADD
The DATEADD function adds a specified number of units to a date.
Syntax: DATEADD(Date, Number, Unit)
Date: The base date. Number: The number of units to add. Unit: The unit of time (e.g., "days", "months").
Example: DATEADD("2025-08-26", 5, "days") The function will return "2025-08-31".
DATEDIFF
The DATEDIFF function calculates the difference between two dates in the specified unit.
Syntax: DATEDIFF(Date1, Date2, Format)
Date1: The start date. Date2: The end date. Format (optional): The date format (e.g., "DD-MM-YYYY").
Example: DATEDIFF("2025-08-01", "2025-08-26") The function will return 25.
DATEDIFFEXCLUDEWEEKEND
The DATEDIFFEXCLUDEWEEKEND function calculates the difference between two dates, excluding weekends.
Syntax: DATEDIFFEXCLUDEWEEKEND(Date1, Date2, Format)
Date1: The start date.
Date2: The end date.
Format (optional): The date format (e.g., "DD-MM-YYYY").
Example: DATEDIFFEXCLUDEWEEKEND("2025-08-01", "2025-08-26") The function will return the number of weekdays between the dates.
DATEONLY
The DATEONLY function extracts the date part from a date-time value.
Syntax: DATEONLY(DateTime)
DateTime: The date-time value.
Example: DATEONLY("2025-08-26T14:30:00") The function will return "2025-08-26".
DATETOSTRING
The DATETOSTRING function converts a date to a string in a specified format.
Syntax: DATETOSTRING(Date, InputFormat, OutputFormat)
Date: The date value.
InputFormat: Date format that 'date' is in e.g.: DD-MM-YYYY.
OutputFormat: Required date format e.g.: DD-MM-YYYY.
Example: DATETOSTRING("2025-08-26", "YYYY-MM-DD", "DD/MM/YYYY") The function will return "26/08/2025".
DAY
The DAY function returns the day part of a date.
Syntax: DAY(Date)
Date: The date value.
Example: DAY(DATE(1997, 4, 11)) The function will return 11.
MONTH
The MONTH function returns the month part of a date.
Syntax: MONTH(Date)
Date: The date value.
Example: MONTH(DATE(1997, 4, 11)) The function will return 11.
MONTHNAME
The MONTHNAME function returns the name of the month for a given date.
Syntax: MONTHNAME(Date)
Date: The date value.
Example: MONTHNAME("2025-08-26") The function will return "August".
PARSEDATE
The PARSEDATE function converts a date value from one format to another.
Syntax: PARSEDATE(DateValue, TargetFormat, OriginalFormat)
DateValue: The date value to convert. TargetFormat: The format to convert to (e.g., "DD-MM-YYYY"). OriginalFormat: The original format of the date value.
Example: PARSEDATE("26-08-2025", "YYYY/MM/DD", "DD-MM-YYYY") The function will return a date object for "2025/08/26".
TODAY
The TODAY function returns the current date.
Syntax: TODAY(Format)
Format: Date format e.g.: DD-MM-YYYY
Example: TODAY("YYYY-MM-DD") The function will return today's date, e.g., "2025-08-26".
YEAR
The YEAR function returns the year from a date.
Syntax: YEAR(Date)
Date: The date value.
Example: YEAR("2025-08-26") The function will return 2025.
SPLITDATERANGEBYMONTHS
The SPLITDATERANGEBYMONTHS function splits a date range into monthly segments.
Syntax: SPLITDATERANGEBYMONTHS(StartDate, EndDate)
StartDate: The start date of the range. EndDate: The end date of the range.
Example: SPLITDATERANGEBYMONTHS("01-01-2025", "31-03-2025") The function will return a string describing each month's range and number of days.
Last updated