How to build your custom date in Excel? In fact, it's very simple.
Functions DAY, MONTH, YEAR
These functions 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
By themselves, these functions aren't useful but used with the DATE function, they are powerful to build dynamic dates 😲😎
How the DATE function works?
The DATE function is very easy to use. It needs only 3 arguments IN THIS ORDER
- Year
- Month
- Day
For example, if you want to display the date the 4th July 2021, you write the following function
=DATE(2021, 07, 04) => 04/07/2021
But why using a formula to write a Date instead of writing the date directly in a cell? With the Date function, you can build custom dates 😲
How to build your custom date
Step 1: Extract each date component
Here, with the date in C1, we can extract each component of the date
Step 2: Combine all the components in a single formula
Here, we will write each component in a single formula.
=DATE(YEAR(C1),MONTH(C1),DAY(C1))
The result is exactly the same date as the subscription date 🤔 Now, it's time to see the power of the DATE function.
Step 3: Add Months to the subscription date
Here, we will calculate the ending date of the contract But weu don't know yet if we subscribe for 12, 18, 24 or 36 months and what is the ending date in each situation. But with the DATE function, it's not a problem.
What we going to do it's just to add the numbers of month to the argument of the months in the Date function.
=DATE(YEAR($C$1),MONTH($C$1)+B4,DAY($C$1))
For instance, in B4 I have 12 and when I add this value to subscription month, the formula returns the date of 05/12/2022. Automatically, the DATE function has adjust the result 😲
Step 4 : You can apply calculation for any component
To finish our example, the ending date is always the day before the day of the subscription date. This time, we will subsctrat 1 to the day component.
=DATE(YEAR($C$1),MONTH($C$1)+B4,DAY($C$1)-1)
Other examples where you need to build a custom date
Building a custom date is a very common task with Excel. Here is a list of articles where this technique is used.
- Build a dynamic calendar
- Calculate the seniority
- Calculate the first day, last day of a month / year
- Formulas for calculating any day in a week
- Weekly calendar
