**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

Don Rogers

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?