Function DATE – YEAR – MONTH – DAY

Function DATE – YEAR – MONTH – DAY
Last Updated on 28/12/2022
Reading time: 2 minutes

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 month of a date
  • YEAR extracts the year of a date
Date Components YEAR MONTH DAY

Individually, these functions are not interesting. But used with the DATE function, they are powerful for building dynamic dates 😎👍

How to use the DATE function

The DATE function will return a date with the combination of the 3 arguments

  • Year
  • Month
  • Day

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

=DATE(2021, 12, 31) => 31/12/2021

The DATE function returns a date

Of course, there is no reason to write a date with the example I have used.

WAIT! It's really important.

But now, instead of the value 31, let's write the DATE function with the value 32 for the days.

=DATE(2021, 12, 32) => 01/01/2022

Date 2021 12 32

Of course, no month has 32 days. So, why the formula doesn't return an error?

So how the DATE function works

Behind the scene

We have seen in this article that a date is just a formatted number.

If we change the number format to General, we have the following result

DATE function in General format

Here, the value 44561 means the 44561 days of Excel because the first date in Excel is the 01/01/1900.

So, even if the arguments don't reflect a real date, the DATE function will always display a correct date according to each value.

Another example with a "ridiculous" month value. Here, we build a date with 17 months

Calculate a date with 17 months

Again, the function has calculated the correct date according to the arguments. We have used this technique to calculate the ending date of the contract duration in this example.

Replace the argument by references

Instead of a value, we can use the contain of a cell's reference to build a date.

Use cells reference to calculate a date

Like this, it's easier to change one of the argument of the date like we did for the calendar.

But also, it's the best way to calculate custom date like the first or the last day of the month

12 Comments

  1. Scot Parker
    12/01/2021 @ 00:22

    I just want to automate the next day. B3 has the date. B4 has = B3+1. Doesn't work. The cells have been preformated to date.

    Reply

    • Frédéric LE GUEN
      12/01/2021 @ 08:32

      It should. The only reason it doesn't work it's because you don't have a date in B3 or the format of B4 is Text

      Reply

  2. EAB
    30/09/2020 @ 05:13

    How do I get the column to read the day of the week, then the date of that day, then the month and then the year... as in Wednesday 30 September 2020 and then the cell below to read Thursday 1 October 2020 and so on, in a column down the page ....

    Thanks,

    Reply

  3. Kardheepan S
    26/07/2020 @ 15:31

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

    Reply

    • Frédéric LE GUEN
      26/07/2020 @ 16:32

      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

      Reply

  4. Paula
    20/06/2020 @ 19:07

    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

    Reply

    • Frédéric LE GUEN
      20/06/2020 @ 19:26

      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

      Reply

  5. joshua
    14/10/2019 @ 10:13

    This is Joshua
    I want year and moth and day

    Reply

  6. Abdul Mannan
    18/07/2019 @ 08:34

    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.

    Reply

    • Frédéric LE GUEN
      23/07/2019 @ 07:12

      It's possible with the function DATEDIF

      Reply

  7. Parshant Pathania
    06/08/2018 @ 16:39

    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.

    Reply

  8. Devashish
    22/04/2018 @ 06:29

    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 .

    Reply

Leave a Reply

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

Function DATE – YEAR – MONTH – DAY

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

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 month of a date
  • YEAR extracts the year of a date
Date Components YEAR MONTH DAY

Individually, these functions are not interesting. But used with the DATE function, they are powerful for building dynamic dates 😎👍

How to use the DATE function

The DATE function will return a date with the combination of the 3 arguments

  • Year
  • Month
  • Day

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

=DATE(2021, 12, 31) => 31/12/2021

The DATE function returns a date

Of course, there is no reason to write a date with the example I have used.

WAIT! It's really important.

But now, instead of the value 31, let's write the DATE function with the value 32 for the days.

=DATE(2021, 12, 32) => 01/01/2022

Date 2021 12 32

Of course, no month has 32 days. So, why the formula doesn't return an error?

So how the DATE function works

Behind the scene

We have seen in this article that a date is just a formatted number.

If we change the number format to General, we have the following result

DATE function in General format

Here, the value 44561 means the 44561 days of Excel because the first date in Excel is the 01/01/1900.

So, even if the arguments don't reflect a real date, the DATE function will always display a correct date according to each value.

Another example with a "ridiculous" month value. Here, we build a date with 17 months

Calculate a date with 17 months

Again, the function has calculated the correct date according to the arguments. We have used this technique to calculate the ending date of the contract duration in this example.

Replace the argument by references

Instead of a value, we can use the contain of a cell's reference to build a date.

Use cells reference to calculate a date

Like this, it's easier to change one of the argument of the date like we did for the calendar.

But also, it's the best way to calculate custom date like the first or the last day of the month

12 Comments

  1. Scot Parker
    12/01/2021 @ 00:22

    I just want to automate the next day. B3 has the date. B4 has = B3+1. Doesn't work. The cells have been preformated to date.

    Reply

    • Frédéric LE GUEN
      12/01/2021 @ 08:32

      It should. The only reason it doesn't work it's because you don't have a date in B3 or the format of B4 is Text

      Reply

  2. EAB
    30/09/2020 @ 05:13

    How do I get the column to read the day of the week, then the date of that day, then the month and then the year... as in Wednesday 30 September 2020 and then the cell below to read Thursday 1 October 2020 and so on, in a column down the page ....

    Thanks,

    Reply

  3. Kardheepan S
    26/07/2020 @ 15:31

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

    Reply

    • Frédéric LE GUEN
      26/07/2020 @ 16:32

      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

      Reply

  4. Paula
    20/06/2020 @ 19:07

    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

    Reply

    • Frédéric LE GUEN
      20/06/2020 @ 19:26

      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

      Reply

  5. joshua
    14/10/2019 @ 10:13

    This is Joshua
    I want year and moth and day

    Reply

  6. Abdul Mannan
    18/07/2019 @ 08:34

    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.

    Reply

    • Frédéric LE GUEN
      23/07/2019 @ 07:12

      It's possible with the function DATEDIF

      Reply

  7. Parshant Pathania
    06/08/2018 @ 16:39

    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.

    Reply

  8. Devashish
    22/04/2018 @ 06:29

    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 .

    Reply

Leave a Reply

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