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.
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.
This function is useful for ensuring that imported or user-entered date values are correctly interpreted for use in reports and models. You can specify formats like "DMY" or "MDY" to handle international data variations or system-specific formats.
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.
This function is useful for adjusting dates to calculate due dates, contract renewals, or planning timelines. For example, it helps automate projections that depend on future or past date offsets.
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.
This function is useful for calculating the duration of projects, financial terms, or asset holding periods. It’s a practical tool for comparing time spans such as customer contract lengths or investment durations.
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.
This function is useful for creating custom date ranges for reporting periods, forecasts, or time series analysis. You can also use it to validate whether a specific date falls within a set range.
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.
This function is useful for performing calculations or sorting dates as numbers in your dataset. It ensures consistency when mixing date formats or importing external data.
DATEVALUE(data)
Example:
data = "2019-01-03"
DATEVALUE(data) = 42738
Returns the day of the month (1-31) from a given date.
This function is useful for identifying patterns in data such as monthly sales peaks or expense timing, or for filtering records by specific days.
DAY(data)
Example:
data = "2019-01-03"
DAY(data) = 3
Returns the day number within the calendar year (1-366) from a given date.
This function is useful for tracking elapsed days since year start, for example to calculate performance milestones, project phases, or fiscal year day counts.
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.
This function is useful for calculating elapsed time, invoice terms, or resource usage. It’s versatile and supports date inputs or shorthand for periods like “M” or “Y”.
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.
This function is useful for standardized interest or depreciation calculations in accounting, especially where uniform monthly durations are assumed.
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.
This function is useful for payroll, holiday tracking, or attendance where you want to match specific working or non-working days within a given period.
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.
This function is useful for prorating values like salaries, lease costs, or budget allocations that apply only within a certain reporting window.
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.
This function is useful for planning monthly cycles like payments, project milestones, or review dates. It helps shift periods cleanly across months.
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.
This function is useful for determining financial period ends, cut-off dates for processing, or recurring reporting deadlines tied to month-end.
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.
This function is useful for calculating fiscal year-ends, long-term investment periods, or multi-year reporting cutoffs. You can shift forward or backward by any number of years.
EOYEAR(data, number)
Example:
data = "2019-01-03"
EOYEAR(data, 2) = "2022-12-31"
Returns the hour (1-24) from a given date time.
This function is useful for analyzing the time of day of transactions or activity, especially when working with detailed time logs or time-based billing.
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.
This function is useful for European financial calendars, logistics, and consistent weekly reporting. It ensures correct alignment with international week numbering standards.
ISOWEEKNUM(data)
Example:
data = "2019-01-03"
ISOWEEKNUM(data) = 1
Returns the minutes (1-59) from a given date time.
This function is useful for time tracking and timestamp analysis, such as logging transaction times or identifying operational bottlenecks.
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.
This function is useful for grouping data by month or converting month numbers to labels in reports. It supports formats like short/long names or custom lists.
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 useful for calculating contract terms, accrual durations, or installment periods in monthly increments.
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).
This function is useful for payroll, staffing, and project timelines. It excludes weekends and optional holiday calendars.
NETWORKDAYS(data, date, holidays)
Example:
data = "2019-01-03"
NETWORKDAYS(data, '2019-01-07') = 2
Returns the current date and time.
This function is useful for creating dynamic timestamps, audit logs, or capturing snapshot timings in reports.
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.
This function is useful for workforce planning, retirement forecasts, and HR reporting using localized rules for the Netherlands.
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.
This function is useful for normalizing dates in budgeting and reporting, especially when dates are entered in varied formats.
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.
This function is useful for measuring elapsed financial periods between reporting dates, such as quarter-to-quarter or year-on-year comparisons.
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.
This function is useful for grouping financial data into quarterly segments for reporting, forecasting, or board review.
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.
This function is useful for precision logging or compliance applications where full timestamp resolution is required.
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.
This function is useful for defining recurring billing cycles, monthly planning start dates, or modeling budget periods.
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.
This function is useful for defining fiscal year start points or calculating eligibility dates that align with yearly planning.
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).
This function is useful for generating rolling-period reports, dynamic forecasts, or comparing current to previous periods.
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).
This function is useful for generating rolling-period reports, dynamic forecasts, or comparing current to previous periods.
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.
This function is useful for time-based calculations, such as work-hour billing or proportional allocations within a day.
TIMEVALUE(data)
Example:
data = "2019-01-03 13:50"
TIMEVALUE(data) =
0.55333333
Returns today's date.
This function is useful for dynamically filtering data by current day, such as aging reports, overdue alerts, or live dashboards.
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.
This function is useful for scheduling, identifying weekly patterns, or filtering by workdays. Supports different naming formats.
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.
This function is useful for North American weekly reports or aligning operations with Sunday-based weeks.
WEEKNUM(data)
Example:
data = "2019-01-03"
WEEKNUM(data) = 1
Returns the year from a given date or text.
This function is useful for grouping or filtering by year in time-based financial analysis, or converting full dates to annual references.
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.
This function is useful for building dropdown lists, timeline filters, or validating whether a value falls within a set of years.
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.
This function is useful for experience tracking, age calculations, depreciation schedules, or comparing fiscal year gaps.
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).
This function is useful for adjusting schedules to business days, such as payment dates or task deadlines, while accounting for weekends and holidays.
WORKDAY(data, number, holidays)
Example:
data = "2019-01-03"
WORKDAY(data, 2) = 2019-01-07