«

»

Function DATE - YEAR - MONTH - DAY

Presentation of DAY, MONTH, YEAR

The Functions YEAR, MONTH and DAY are very simple to understand. They extract each part of a date.

  • DAY extracts the day of a date
  • MONTH extracts the day of a date
  • YEAR extracts the day of a date

Individually, these functions do not interesting but used with the DATE function, they are powerful to build dynamic dates.

The DATE function, the link

The DATE function need the 3 parameters of a date to build a date

  • Year
  • Month
  • Day

For example, if you want to display the date 31/12/2017, you write the following function:

=DATE(2017, 12, 31)

Hmmmm, OK! 🤔🤨 But what is interest ? I can write directly 31/12/2017 in a cell without this function.

True! But the interest of this function is to replace the value 2017, 12 or 31 by variables. Let's see some examples.

Dynamic Date

Let's say you want to create a date that will display always the first day of the current month. For that we will use the function TODAY.

If we replace in the previous pics the value in B1 by TODAY, we have now this result

It may looks easy, stupid, no interest. But whatever you will use these formulas, it will always return the day, the month or the year of the current day 😍😍😍

First day of the current month

It is very easy to create a dynamic date for the first day of any month.

The first 2 parameters of the DATE function are the current year and current month

=DATE(YEAR(TODAY()),MONTH(TODAY()),

And now, what is the parameter for the day? Well, very easy ... it's 1 🤣😋😉

So the formula to return the first day of the current month is

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

First day of the previous month

With the same logic, if you want to display always the first month of the previous month the formula is the same. The only thing that is different it's the fact to reduce the value of the month by 1

=DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)

And it works even if you overlap a year. Look at this different result

Last day of the previous month

A lot of users think that create a dynamic date for the last day of any month it's a nightmare. Not at all.

Let's rewrite the formula for the first day of the current month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

In Excel, 1 is one day (and never one hour). So we just have to subtract 1 to the formula to return the last day of the previous month

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1

If you want to show that you are an Excel pro to your friends, you can replace the 1-1 of the formula by 0. The result is the same

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)

Last day of the current month

On the same way, if you want to return the last day of the current month, we have to build a formula to display the first month of the next month

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

And subtract 1 one more time.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

Related articles


Have a look at these other articles that could help you in your work

Permanent link to this article: https://www.excel-exercise.com/function-date-year-month-day/


1 comment

  1. Devashish

    I want to make a salary calculator( in excel ) in which I want to add 3% increment in "basic". My columns are :---- 1. "Month"=march 2017, April 2017 etc 2. "Basic"=50000 , 3. "D.A." = basic*.07, 4. "H.R.A."= basic*.08, 5. "Gross"= basic+da+hra .
    Please help me .

Leave a Reply

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