# Function DATE – YEAR – MONTH – DAY

Last Updated on 28/12/2022 by Frédéric LE GUEN

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

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

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

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

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

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.

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

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.

• 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

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,

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

• 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

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

• 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

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

This is Joshua
I want year and moth and day

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.

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

It's possible with the function DATEDIF

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.

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 .

# Function DATE – YEAR – MONTH – DAY

Last Updated on 28/12/2022 by Frédéric LE GUEN

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

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

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

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

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

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.

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

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.

• 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

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,

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

• 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

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

• 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

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

This is Joshua
I want year and moth and day

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.

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

It's possible with the function DATEDIF

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.

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 .