Functions for date conversion

These functions perform date or time-specific operations on dates, and return the result. You can use these to process and calculate dates in imports, reports, or models.

DATE

Converts a text string that represents a date into a real date.

In most cases, XLReporting automatically recognizes the used date format and converts text strings to dates. In some cases, if the date format is not correctly interpreted, you can set the "type" parameter to specify the date format.

DATE(data, type)

Type:
"DMY" - day-month-year
"MDY" - month-day-year
"YMD" - year-month-day
"MMY" - month as a word (e.g. Feb 2019)
"J" - Julian date (Excel)
"N" - Number date (Epoch/Unix)

Example:
data = "3-1-2017"
DATE(data, "DMY") = 2019-01-03

DATEADD

Adds a number of years, months, or days to a given date. If the number is negative, it will be substracted from the given date.

DATEADD(data, number, type)

Type:
"D" - day (default)
"M" - month
"Y" - year

Example:
data = "2019-01-03"
DATEADD(data, 2, "D") = 2019-01-05

DATEDIF

Calculates the number of days, months, or years between 2 given dates. If you omit the 2nd date, today's date will be used. For number of days, you can also use the DAYS function.

DATEDIF(data, date, type)

Type:
"D" - day (default)
"M" - month
"Y" - year

Example:
data = "2019-01-03"
DATEDIF(data, "2019-01-05", "D") = 2

DATERANGE

Returns an array of dates starting at the given date for the given number of days. You can optionally pass an interval (default is 1). The returned data has the same structure as a cell range or data array, which enables you to pass this data directly into a dropdown editor or other functions that expect a cell range or data array.

If you provide the optional date parameter, the function will check if that date falls within the range of dates, and return either that date (true) or nothing (false). You can use the returned value by itself or as a condition inside an IF formula.

DATERANGE(data, number, interval, day)

Example:
DATERANGE("2019-01-05", 3) = "2019-01-05", "2019-01-06", "2019-01-07"
DATERANGE("2019-01-05", 3, 5) = "2019-01-05", "2019-01-10", "2019-01-15"br/>DATERANGE("2019-01-05", 3, 5, "2019-01-10") = "2019-01-10"

DATEVALUE

Converts the given date (which can be text) into a serial number that is compatible with Excel.

DATEVALUE(data)

Example:
data = "2019-01-03"
DATEVALUE(data) = 42738

DAY

Returns the day of the month (1-31) from a given date.

DAY(data)

Example:
data = "2019-01-03"
DAY(data) = 3

DAYNUM

Returns the day number within the calendar year (1-366) from a given date.

DAYNUM(data)

Example:
data = "2019-02-03"
DAYNUM(data) = 34

DAYS

Returns the number of days between the 2 given dates. You can either pass dates or periods (YYYY-MM) as parameters. If you omit the 2nd date, today's date will be used.

You can also pass "Y" or "M" as the 2nd parameter, which will return the number of days within the given calendar year or month.

DAYS(data, date)

Example:
data = "2019-01-03"
DAYS(data, "2019-02-03") = 31
DAYS(data, "M") = 31

DAYS360

Returns the number of days between the 2 given dates, based on a 360-day year where all months are considered to have 30 days.

DAYS360(data, date)

Example:
data = "2019-01-03"
DAYS360(data, "2019-02-03") = 30

DAYSWITHIN

Calculates the number of days that the "from" and "to" dates fall within the given date range. You can either pass dates or periods (YYYY-MM) as parameters. If you omit either the "from" or "to" parameter, it will be assumed to fall within the given date range.

DAYSWITHIN(data, date, from, to)

Example:
data = "2019-01-03"
DAYSWITHIN(data, "2019-01-30", "2019-01-01", "2019-01-10") = 7

YEARS

Returns the number of years between the 2 given years. You can either pass dates, periods (YYYY-MM), or years (YYYY) as parameters. If you omit the 2nd year, today's year will be used.

YEARS(data, date)

Example:
data = "2019-01-03"
YEARS(data, "2019-02-03") = 2

EDATE

Adds a number of months to a given date. If the number is negative, it will be substracted from the given date.

EDATE(data, number)

Example:
data = "2019-01-03"
EDATE(data, 2) = 2019-03-01

EOMONTH

Returns the last day of the month (End Of Month) that is the given number of months before or after the given date. If you omit the number, the last day of the given month will be returned. If the number is negative, it will be substracted from the given date.

EOMONTH(data, number)

Example:
data = "2019-01-03"
EOMONTH(data, 2) = "2019-03-31"

EOYEAR

Returns the last day of the year (End Of Year) that is the given number of years before or after the given date. If you omit the number, the last day of the given year will be returned. If the number is negative, it will be substracted from the given date.

EOYEAR(data, number)

Example:
data = "2019-01-03"
EOYEAR(data, 2) = "2022-12-31"

HOUR

Returns the hour (1-24) from a given date time.

HOUR(data)

Example:
data = "2019-01-03 08:10:20"
HOUR(data) = 8

MINUTE

Returns the minutes (1-59) from a given date time.

MINUTE(data)

Example:
data = "2019-01-03 08:10:20"
MINUTE(data) = 10

MONTH

Returns an integer representing the calendar month from a given date. By default, this returns a number between 1 and 12 unless you pass a defined type, cell range, data array, or comma-delimited list of values as "labels" parameter.

MONTH(data, labels)

Labels:
1 - short month names
2 - long month names
cell range, data array, or comma-delimited list

Examples:
data = "2019-01-03"
MONTH(data) = 1
MONTH(data, 1) = "Jan"
MONTH(data, 2) = "January"
MONTH(data, "JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC") = "JAN"

NETWORKDAYS

Returns the number of working days between the 2 given dates, excluding Saturdays, Sundays, and an optional list (cell range, data array, or comma-delimited list of values) of holiday dates.

NETWORKDAYS(data, date, holidays)

Example:
data = "2019-01-03"
NETWORKDAYS(data, '2019-01-07') = 2

NOW

Returns the current date and time.

NOW()

Example:
NOW() = "2019-01-03 11:00"

PENSIONDATE

Returns the eligible start date for pension, based on the given birth date of a person. This function is using country-specific lookup tables and rules. If you want your country to be supported as well, please contact us.

PENSIONDATE(data, type)

Type:
"NL" - Netherlands (default)

Example:
data = "1970-3-17"
PENSIONDATE(data) = "2037-07-17"

PERIOD

Converts the given date to a standardized period name (YYYY-MM).

In most cases, XLReporting automatically recognizes the used date format and converts text strings to dates. In some cases, if the date format is not correctly interpreted, you can set the "type" parameter to specify the date format.

PERIOD(data, type)

Type:
"DMY" - day-month-year
"MDY" - month-day-year
"YMD" - year-month-day

Example:
data = "3-1-2017"
PERIOD(data, "DMY") = 2019-01

PERIODS

Calculates the number of periods between the 2 given periods. You can either pass dates, periods (YYYY-MM), or years (YYYY) as parameters.

PERIODS(data, period)

Example:
data = "2019-01"
PERIODS(data, "2019-03") = 2

QUARTER

Returns an integer representing the calendar quarter from a given date. By default, this returns a number between 1 and 4 unless you pass a defined type, cell range, data array, or comma-delimited list of values as "labels" parameter.

QUARTER(data, labels)

Labels:
1 - short quarter names
2 - long quarter names
cell range, data array, or comma-delimited list

Examples:
data = "2019-04-03"
QUARTER(data) = 2
QUARTER(data, 1) = "Qtr 2"
QUARTER(data, 2) = "2nd Quarter"
QUARTER(data, "Q1,Q2,Q3,Q4") = "Q2"

SECOND

Returns the seconds from a given date and time.

SECOND(data)

Example:
data = "2019-01-03 08:10:20"
SECOND(data) = 20

SOMONTH

Returns the first day of the month (Start Of Month) that is the given number of months before or after the given date. If you omit the number, the first day of the given month will be returned. If the number is negative, it will be substracted from the given date.

SOMONTH(data, number)

Example:
data = "2019-01-03"
SOMONTH(data, 0) = "2019-01-01"

SOYEAR

Returns the first day of the year (Start Of Year) that is the given number of years before or after the given date. If you omit the number, the first day of the given year will be returned. If the number is negative, it will be substracted from the given date.

SOYEAR(data, number)

Example:
data = "2019-05-03"
SOYEAR(data, 0) = "2019-01-01"

TIMEVALUE

Converts the time part of the given datetime (which can be text) into a decimal number between 0 and 1 (representing the number of seconds since midnight) that is compatible with Excel.

TIMEVALUE(data)

Example:
data = "2019-01-03 13:50"
TIMEVALUE(data) = 0.55333333

TODAY

Returns today's date.

TODAY()

Example:
TODAY() = "2019-01-03"

WEEKDAY

Returns an integer representing the day of the week for a given date (Sunday = 1, Saturday = 7). By default, this returns a number between 1 and 7 unless you pass a defined type, cell range, data array, or comma-delimited list of values as "labels" parameter.

WEEKDAY(data, labels)

Labels:
1 - short day names
2 - long day names
cell range, data array, or comma-delimited list

Examples:
data = "2019-01-03"
WEEKDAY(data) = 3
WEEKDAY(data, 1) = "Tue"
WEEKDAY(data, 2) = "Tuesday"
WEEKDAY(data, "Zo,Ma,Di,Wo,Do,Vr,Za") = "Di"

WEEKNUM

Returns an integer representing the calendar week number (from 1 to 53) from a given date.

WEEKNUM(data)

Example:
data = "2019-01-03"
WEEKNUM(data) = 1

YEAR

Returns the year from a given date.

YEAR(data)

Example:
data = "2019-01-03"
YEAR(data) = 2019

YEARRANGE

Returns an array of years starting at the given year for the given number of years. You can optionally pass an interval (default is 1). The returned data has the same structure as a cell range or data array, which enables you to pass this data directly into a dropdown editor or other functions that expect a cell range or data array.

If you provide the optional year parameter, the function will check if that year falls within the range of years, and return either that year (true) or nothing (false). You can use the returned value by itself or as a condition inside an IF formula.

YEARRANGE(data, number, interval, year)

Example:
YEARRANGE("2019", 3) = "2019", "2020", "2021"
YEARRANGE("2019", 3, 5) = "2019", "2024", "2029"

WORKDAY

Returns the nearest working day in the future or past, based on the given number of days. If the number is negative, the date will be in the past. You can optionally provide a list (cell range, data array, or comma-delimited list of values) of holiday dates.

WORKDAY(data, number)

Example:
data = "2019-01-03"
WORKDAY(data, 2) = 2019-01-07

Recommended reading:
Back to top | Imports | Reports | Models