DATE
A date from a year, a month and a day; months and days past their range carry over, and years 0 to 49 read as 2000 to 2049, 50 to 99 as 1950 to 1999.
Group: Date and time. Compatible with DAX.
Syntax
DATE ( <year>, <month>, <day> )| Parameter | Type | Description |
|---|---|---|
year | Whole number | The year, best written with four digits. |
month | Whole number | The month, 1 to 12. Past 12 it carries on into the next years; 0 or less goes back into the years before. |
day | Whole number | The day of the month. Past the month's last day it runs on into the next month. |
Returns
Date. That date, at midnight.
Remarks
- A date is a number of days counted from 30 December 1899, day 0: 1 January 2024 is day 45292, and noon adds 0.5.
INTof a date gives its number, and a date plus 1 is the next day. - Years 0 to 49 are taken as 2000 to 2049, and 50 to 99 as 1950 to 1999. From 100 on, the year is the one written.
- Months and days out of their range move the date:
DATE ( 2024, 14, 1 )is 1 February 2025 andDATE ( 2024, 0, 1 )is 1 December 2023. - Day 0 is an error, not the month's eve as in Excel: for the last day of a month, use
EOMONTH. A negative day goes back from the first of the month, soDATE ( 2024, 1, -5 )is 26 December 2023. - A decimal is rounded (
DATE ( 2024, 1.5, 1 )falls in February) and a blank counts as 0. - The result must fall between 1 January 100 and 31 December 9999; otherwise the call fails, as it does for 29 February 1900, a day that never was.
- A fixed date can also be written as a literal:
dt"2024-03-15".
Examples
A date, and a date with a time
EVALUATE
ROW (
"Date", DATE ( 2024, 3, 15 ),
"With a time", DATE ( 2024, 3, 15 ) + TIME ( 14, 30, 0 )
)| Date | With a time |
|---|---|
| 2024-03-15 | 2024-03-15 14:30:00 |
Months and days past their range
EVALUATE
ROW (
"Month 14", DATE ( 2024, 14, 1 ),
"Month 0", DATE ( 2024, 0, 1 ),
"Day 32", DATE ( 2024, 1, 32 ),
"Day -5", DATE ( 2024, 1, -5 )
)| Month 14 | Month 0 | Day 32 | Day -5 |
|---|---|---|---|
| 2025-02-01 | 2023-12-01 | 2024-02-01 | 2023-12-26 |
The last day of February
The first of March less one day.
EVALUATE
ROW (
"2024", DATE ( 2024, 3, 1 ) - 1,
"2025", DATE ( 2025, 3, 1 ) - 1
)| 2024 | 2025 |
|---|---|
| 2024-02-29 | 2025-02-28 |