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.

Convert date format without formula

If your need is to convert  date format , like YYYYMMDD to DD/MM/YYYY, it is not necessary to use this formula.

You can do it with the tool Text to Columns. You will find the explanations in this article.

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


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


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


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


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


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


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


And subtract 1 one more time.


Related posts

Permanent link to this article:


Skip to comment form

    • Abdul Mannan on 18/07/2019 at 08:34
    • Reply

    Sir I want to deduct total days of EOL availed by an employee for example 663 days (1 year 9 months and 24 days) from his total service (41 years 8 months and 17 days), I am unable to do it, please provide a solution to it, if possible.

    1. It's possible with the function DATEDIF

    • Parshant Pathania on 06/08/2018 at 16:39
    • Reply

    6/1/2018 Fri
    6/17/2018 Sun

    how to extract only day from above.....I tried left, right & mid but forward slash is coming as well.

    • Devashish on 22/04/2018 at 06:29
    • Reply

    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.