How to build your dynamic dates in Excel

How to build your dynamic dates in Excel
Last Updated on 28/12/2022
Reading time: 2 minutes

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
Date Components YEAR MONTH DAY

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

The 3 components in the same 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.

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

Calculation of the ending date

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)

Substract 1 day of the subscription day

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?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

How to build your dynamic dates in Excel

Reading time: 2 minutes
Last Updated on 28/12/2022

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
Date Components YEAR MONTH DAY

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

The 3 components in the same 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.

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

Calculation of the ending date

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)

Substract 1 day of the subscription day

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?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *