In this article:

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

All | Text | Date | Number | Aggregate | Filters | Lookups | Period | Queries | Math | System | Financial | Conditional | Common | Special

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.

✭ Tips:

- Whist dates are processed in the YYYY-MM-DD notation, each user can individually decide
how dates are displayed in reports and models, by selecting the desired date format
in
**Manage - Profile**.

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-2019"

DATE(data, "DMY") = 2019-01-03

Adds a given *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

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

Returns an array of dates starting at the given date for the given *number* of
days. You can optionally pass an *interval* parameter (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 *day* parameter, the function will check if that
day 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"

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

DATEVALUE(data)

Example:

data = "2019-01-03"

DATEVALUE(data) = 42738

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

DAY(data)

Example:

data = "2019-01-03"

DAY(data) = 3

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

DAYNUM(data)

Example:

data = "2019-02-03"

DAYNUM(data) = 34

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* parameter, today's date will be used. You can also pass "Y" or
"M" as the 2nd *date* 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

Returns the number of days between the 2 given date parameters, 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

Calculates the number of days in the given period that match the given list of calendar dates. You can optionally count specific days of the week as well.

The *data* parameter is mandatory and needs to be a cell range, data array, or
comma-delimited list of values containing the calendar dates that you want to count. Dates
must be entered as DD/MM/YYYY, DD-MM-YYYY or YYYY-MM-DD.

The *period* parameter is also mandatory and is the calendar month for which you want to
count the number of days.

The *days* parameter is optional and can be a cell range, data array, or comma-delimited
list of values containing up to 7 values of either 0 or 1, each representing a day in
the week (0=Mon, 1=Tue, 2=Wed, 3=Thu, 4=Fri, 5=Sat, 6=Sun). For example "0,0,1,0,1" indicates
Wednesdays and Fridays.

The *type* parameter is optional and can be used to either count the number of days
that match the list of dates, or count the remaining days that do not match the list of dates.

DAYSIN(data, period, days, type)

type:

0 = count the days that match (default)

1 = count the days that do not match

Example:

data = "1/1/2019, 10/1/2019, 3/2/2019"

DAYSIN(data, "2019-01") =
2

DAYSIN(data, "2019-01", "0,1") = 1 (the number of Tuesdays that match the dates)

DAYSIN(data,
"2019-01", "0,1", 1) = 4 (the number of Tuesdays that do no match the dates)

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

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

EDATE(data, number)

Example:

data = "2019-01-03"

EDATE(data, 2) = 2019-03-03

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"

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"

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

HOUR(data)

Example:

data = "2019-01-03 08:10:20"

HOUR(data) = 8

Returns an integer representing the calendar week number for a given date, according to ISO 8601. Week numbering starts in the week with the 1st Thursday in the year, and weeks start on Mondays. This is the default in Europe. If you want to calculate week numbers for North America and Canada, you should use the WEEKNUM function.

ISOWEEKNUM(data)

Example:

data = "2019-01-03"

ISOWEEKNUM(data) = 1

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

MINUTE(data)

Example:

data = "2019-01-03 08:10:20"

MINUTE(data) = 10

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 label type, or a cell
range, data array, or comma-delimited list of values as *names* parameter.

You can also use this function for quick conversion between the name of a month vice versa the number of the month, by passing either Jan-Dec as data, or 1-12.

MONTH(data, names)

names:

1 = short month names

2 = long month names

3 = period
codes (a 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, 3) = "P01"

MONTH(data,
"JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC") = "JAN"

MONTH("Apr")
= 4

MONTH(4) = "Apr"

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

This function is an alias for the PERIODS function and thus identical.

MONTHS(data, month)

Example:

data = "2019-01"

MONTHS(data, "2019-03") = 2

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

NETWORKDAYS(data, date, holidays)

Example:

data = "2019-01-03"

NETWORKDAYS(data, '2019-01-07') = 2

Returns the current date and time.

NOW()

Example:

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

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"

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-2019"

PERIOD(data, "DMY") = 2019-01

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

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 *type* parameter.

You can also use this function for quick conversion between the name of a quarter vice versa the number of the quarter, by passing either Qtr1-Qtr4 as data, or 1-4.

QUARTER(data, names)

names:

1 = short quarter names

2 = long quarter names

3 =
quarter codes (a 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, 3) = "Q2"

QUARTER(data,
"I,II,III,IV") = "II"

QUARTER(2) = "Qtr 2"

Returns the seconds from a given date and time.

SECOND(data)

Example:

data = "2019-01-03 08:10:20"

SECOND(data) = 20

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"

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"

Returns the period based on today's date, plus or minus the given *number* of periods
(optional).

THISPERIOD(number)

Examples:

today = 10-dec-2019

THISPERIOD() = 2019-12

THISPERIOD(-6) =
2019-06

Returns the year based on today's date, plus or minus the given *number* of years
(optional).

THISYEAR(number)

Examples:

today = 10-dec-2019

THISYEAR() = 2019

THISYEAR(-1) =
2018

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

Returns today's date.

TODAY()

Example:

TODAY() = "2019-01-03"

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
*names* parameter.

WEEKDAY(data, names)

names:

1 = short day names

2 = long day names (a 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, "Sun,Mon,Tue,Wed,Thu,Fri,Sat") = "Tue"

Returns an integer representing the calendar week number for a given date. Week numbering starts on Jan 1, and weeks start on Sundays. This is the default in North America and Canada. If you want to calculate week numbers for Europe, you should use the ISOWEEKNUM function.

WEEKNUM(data)

Example:

data = "2019-01-03"

WEEKNUM(data) = 1

Returns the year from a given date or text.

YEAR(data)

Example:

data = "2019-01-03"

YEAR(data) = 2019

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"

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, "2021-02-03") = 2

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
of *holidays* (a cell range, data array, or comma-delimited list of dates).

WORKDAY(data, number, holidays)

Example:

data = "2019-01-03"

WORKDAY(data, 2) = 2019-01-07