Function DATE – YEAR – MONTH – DAY

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


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

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


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

Difference between 2 dates – DATEDIF

Frédéric LE GUEN

First day – Last day in Excel

Frédéric LE GUEN

How to display hours over 24

Frédéric LE GUEN


Scot Parker 12/01/2021 at 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 at 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

EAB 30/09/2020 at 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 ....


Kardheepan S 26/07/2020 at 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 at 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

Paula 20/06/2020 at 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

Frédéric LE GUEN 20/06/2020 at 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

joshua 14/10/2019 at 10:13

This is Joshua
I want year and moth and day

Abdul Mannan 18/07/2019 at 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 at 07:12

It's possible with the function DATEDIF

Parshant Pathania 06/08/2018 at 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.

Devashish 22/04/2018 at 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 .


Leave a Comment