Date

How to build your custom date in Excel

Reading Time: 2 minutes

How to build your custom date in Excel? In fact, it's very simple.

Functions DAY, MONTH, YEAR

These functions are very simple to understand; they 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 useful but used with the DATE function, they are powerful to build dynamic dates 😲😎

How the DATE function works?

The DATE function is very easy to use. It needs only 3 arguments IN THIS ORDER

  • Year
  • Month
  • Day

For example, if you want to display the date the 4th July 2021, you write the following function

=DATE(2021, 07, 04) => 04/07/2021

But why using a formula to write a Date instead of writing the date directly in a cell? With the Date function, you can build custom dates 😲

How to build your custom date

Step 1: Extract each date component

Here, with the date in C1, we can extract each component of the date

Date Components YEAR MONTH DAY

Step 2: Combine all the components in a single formula

Here, we will write each component in a single formula.

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

The 3 components in the same function

The result is exactly the same date as the subscription date 🤔 Now, it's time to see the power of the DATE function.

Step 3: Add Months to the subscription date

Here, we will calculate the ending date of the contract But weu don't know yet if we subscribe for 12, 18, 24 or 36 months and what is the ending date in each situation. But with the DATE function, it's not a problem.

What we going to do it's just to add the numbers of month to the argument of the months in the Date function.

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

For instance, in B4 I have 12 and when I add this value to subscription month, the formula returns the date of 05/12/2022. Automatically, the DATE function has adjust the result 😲

Calculation of the ending date

Step 4 : 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 subsctrat 1 to the day component.

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

Substract 1 day of the subscription day

Other examples where you need to build a custom date

Building a custom date is a very common task with Excel. Here is a list of articles where this technique is used.

Related posts

Convert YYYYMMDD to DD/MM/YYYY

Frédéric LE GUEN

Add Days Excluding the Weekend

Frédéric LE GUEN

First day – Last day in Excel

Frédéric LE GUEN

Leave a Comment