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

• Year
• Month
• Day

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.

=DATE(YEAR(C1),MONTH(C1),DAY(C1))

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.

=DATE(YEAR($C$1),MONTH($C$1)+B4,DAY($C$1))

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.

=DATE(YEAR($C$1),MONTH($C$1)+B4,DAY($C$1)-1)

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