Presentation of DAY, MONTH, YEAR
The Functions YEAR, MONTH, and DAY are very simple to understand. They extract each part of a date.
- DAY extracts the day of a date
- MONTH extracts the month of a date
- YEAR extracts the year of a date
Individually, these functions are not interesting. But used with the DATE function, they are powerful for building dynamic dates 😎👍
How to use the DATE function
The DATE function will return a date with the combination of the 3 arguments
For example, if you want to display the date 31/12/2021, you write the following function:
=DATE(2021, 12, 31) => 31/12/2021
Of course, there is no reason to write a date with the example I have used.
WAIT! It's really important.
But now, instead of the value 31, let's write the DATE function with the value 32 for the days.
=DATE(2021, 12, 32) => 01/01/2022
Of course, no month has 32 days. So, why the formula doesn't return an error?
So how the DATE function works
Behind the scene
We have seen in this article that a date is just a formatted number.
If we change the number format to General, we have the following result
Here, the value 44561 means the 44561 days of Excel because the first date in Excel is the 01/01/1900.
So, even if the arguments don't reflect a real date, the DATE function will always display a correct date according to each value.
Another example with a "ridiculous" month value. Here, we build a date with 17 months
Again, the function has calculated the correct date according to the arguments. We have used this technique to calculate the ending date of the contract duration in this example.
Replace the argument by references
Instead of a value, we can use the contain of a cell's reference to build a date.
Like this, it's easier to change one of the argument of the date like we did for the calendar.
But also, it's the best way to calculate custom date like the first or the last day of the month