Creating your custom dates is very easy with Excel. Let's see some examples.
Functions DAY, MONTH, YEAR
These functions 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 helpful. But used with the DATE function, they are powerful for building dynamic dates 😲😎
How does the DATE function work?
The DATE function builds a date with 3 arguments IN THIS ORDER
For example, if you want to display the date of the 4th of July 2021, you write the following formula.
=DATE(2021, 07, 04) => 04/07/2021
Ok but what's the point? 🤔
Let's see how to use these components to build your custom date.
Step 1: Extract each component with a formula
Here, we will extract each part of the date with the functions YEAR, MONTH and DAY. And then, we will integrate them to the DATE function.
OK, the result is the same. So why is it so important to write the DATE function like this 🤔
Well, there are 2 reasons.
- Each component of the date depends on the value in C1. When the date in C1 changes, the custom date in C3 will reflect the update.
- Also, you can add days, months, or years like any other calculations. This is how you create your custom dates 😉👍
Step 2: Add Months to the subscription date
Here, we want to calculate the contract's ending date.
We have 4 different contract duration: 12, 18, 24, or 36 months.
To calculate the ending date, we will add the month duration to the MONTH argument in the DATE function.
As you can see, for each month's value in column B, calculating each ending date is easy to obtain.
Step 3: 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 subtract 1 to the day component.
For more information, you can read the article about the way to calculate the first day of the month or the last day of the month.
Other examples where you need to build a dynamic date
Building a dynamic 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
- Formulas for calculating any day in a week
- Weekly calendar
11/02/2023 @ 20:32
When counting military service the first and last day must be counted. For example, January 1 thru 31 is 31 days, not 30 days as the DATEDIF formula displays. I am trying to to display six different assignments with the total years, months & days for each assignment. Also, a total time from entry to discharge counting the first and last day of service. I can usually add +1 to the days augment of formula and it works. However, when +1 is added for dates near end of month the DATEDIF formula gives some strange answers.
Do you have any suggestions for a formula that will add the first and last day to a total year, month & day time interval and be consistent with results?