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
Extract each part 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.

Convert Date format

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

Formula to return the first day of the previous month

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 in 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 posts


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

9 comments

Skip to comment form

    • Kardheepan S on 26/07/2020 at 15:31
    • Reply

    i have declared the data type of the cell to be date. but whem ever i just enter the day and press enter it shows like this

    say 26 then enter

    output 26-01-1900

    I want excel to display it as 26-07-2020, that is when ever i type the date, i want excel to fill the current month and year for me.

    How to achieve it ??

    1. Excel returns 26-01-1900 because the first date in Excel is 01-01-1900. So if you fill 26 with a date format, it's logic to display 26-01-1900.
      But what you want to do, it isn't possible

    • Paula on 20/06/2020 at 19:07
    • Reply

    Hi.. i want to create an attendance register.. but my days should start from the 15th of the previous month, to the 15th of the current month.. what function must i use.

    i have used: 2 functions
    start date: =DATEVALUE("15"&L1) and end date: =EDATE(D2,1)

    But the above functions will only give me dates from the 15th of the current month, to the 15th of the next month, and that's not what i want..

    Please hel

    1. Hi Paula,
      In this article, I have explained how to build the formula to return the first day of any month. Please, read carefully the article and you will see the formula is not difficult.
      Then, if you are able to return the first day, you will be able to create the formula for the 15th

    • joshua on 14/10/2019 at 10:13
    • Reply

    This is Joshua
    I want year and moth and day

    • 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.

report this ad