 Home » Function » Date » How to build your dynamic dates in Excel

# How to build your dynamic dates in Excel

Last Updated on 28/12/2022 by Frédéric LE GUEN

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.

#### 1 Comment

1. 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?

# How to build your dynamic dates in Excel

Last Updated on 28/12/2022 by Frédéric LE GUEN

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.

#### 1 Comment

1. 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?