Description
The DATE function calculates a date value from a year, month, and day.
Usage
DATE(year, month, day)
| Argument | Type | Required | Description |
|---|---|---|---|
| year | number | Yes | The full four-digit year of the date. |
| month | number | Yes | The month of the date (1-12) |
| day | number | Yes | The day of the month (1-31) |
Remarks
ActivityInfo supports dates between the year 1000 CE and 9999 CE.
The month argument represents the month of the year from 1 to 12 (January to December).
If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,14,2) returns the date value February 2, 2009.
If month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. For example, DATE(2008,-3,2) returns the date value representing September 2, 2007.
The day argument represents the day of the month from 1 to 31.
If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008,1,35) returns the date value representing February 4, 2008.
If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example, DATE(2008,1,-15) returns the date value representing December 16, 2007.
Examples
Comparison with a specific date
When collecting dates, you may want to ensure that they fall into a specific date range, for example between January 2022 and July 2023.
TRAINING_DATE > DATE(2022,1,1) && TRAINING_DATE < DATE(2023,7,31)
Adding 6 months to a date
If you need to calculate a deadline that falls six months after the START_DATE of a project, you could calculate this using the following formula:
DATE(YEAR(START_DATE), MONTH(START_DATE) + 6, DAY(START_DATE))
Finding the last day of the month
You might have a form that tracks appointments with a health worker that needs to be reported to the Ministry of Health by the last day of the next calendar month. Since different months have different number of days, you can use a day argument of zero, which effectively subtracts 1 day from the first day of the month:
DATE(YEAR(APPOINTMENT_DATE), MONTH(APPOINTMENT_DATE) + 3, 0)
In this way, if the appointment took place on January 16th, 2024, the formula above would calculate March 1st, 2024 minus 1 day, or February 29th, 2024.