↑ Return to Date & Time

DATE – YEAR – MONTH – DAY

Functions YEAR, MONTH and DAY are very simple. They extract each part of a date. Individually, these functions do not really interest but combined with the DATE function, they automatically generate key dates (such as the first of the month, the last day of a month …).

Feature Overview

The DAY function extracts the day of a date
The MONTH function extracts the day of a date
The YEAR function extracts the day of a date

The DATE function is, for its part, creating a date from three parameters that are to be provided and respectively in this order:

  • year
  • month
  • day

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

= DATE (2010, 12, 25)

The DATE function is gorgeous because if you write 14 months and 38 days in the second and third parameters, the function will adjust itself the result by adding the days equivalent to 14 months and 38 days. And not only the function returns exactly the number of days depending on the month but also the breakdown of months and years.

= DATE (2010, 14, 38) => 10/03/2011

You can also override parameters by functions. If you want to always display the current year as the first parameter, you will extract the current year and the functions YEAR and TODAY

= DATE(YEAR(TODAY()),12,31)

First day

First day of a month

It is very easy to create the first of any month, you can just put 1 in the formula DATE third.

For the month and year of the formula DATE, we will use functions MONTH, YEAR TODAY and to extract the month and the current year.

= DATE (YEAR (TODAY ()), MONTH (TODAY ()); 1)

First day of previous month

In this case, you must use exactly the same formula as above and subtract 1 month

= DATE (YEAR (TODAY ()), MONTH (TODAY ()) -1, 1)

Last day

Last day of the current month

We all know that the number of days in a month varies. Seen this way, the problem seems very complicated to solve to return the value of the last day of a month. But in fact, thanks to the DATE function, the problem is solved without difficulty.

Let the formula allowed us to calculate the first day of any month. By adding additional one month, we get the first day of the month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

And using the rule of Excel that 1 is equal to one day, you just have to subtract 1 to this formula to find the last day of any month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1) -1


Last day of previous month

To display the last day of previous month, simply subtract 1 to the formula that gives you the first day of the month.

=DATE(YEA(TODAY()),MONTH(TODAY());1)-1

To go a little further (and also to impress your friends 🙂 ), you see that your formula ends with 1 and then you subtract 1. So if we replace the 1 – 1 of the formula with a 0, you find that the result is the same.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

Application

Now we will use these formulas and the TEXT function to design a document that will display for each month, the first and last day of the previous month.
This example is very important because every month, a lot of managers change dates of the timesheet manually and they loose a lot of time. Now, it’s Excel which do this task for you.




19 comments

Skip to comment form

  1. arieta

    Hi there, can i have some help please? I am trying to write a formula to add years to a certain year = say 2 July 18 plus 5 years = 2 July 23 (this Ive managed to write) BUT now i need to figure out how to add to that formula if instead of typing in a number, i type in a word like NO or YES or DEFER, then i want the date (ie 2 July 18) to remain unchanged. thanks muchly – A2

  2. Anonymous

    change = Year – Month – Date wise short ..

  3. Anurag

    I want number of days in the selected month to reflect .

  4. Priyankar Samanta

    25Years,4Months,0Days.I want to convert it only into months .so what formula should I use in excel

  5. Anonymous

    HEY
    HOW DO I GET YEAR MONTH DAY IN EXCEL

  6. liz barnes

    I am trying to create a formula that gives a month and date only, without year…..ie……6/1……June,1

  7. Anonymous

    i need how to add a 10/02/2015 & 13/02/2015. pls some one help. i want formula of this one

  8. lisa

    I need a date formula to calculate benefits: if hire date is G2, I want it to display a date that is the first of the month following 30 calendar days. default date format is mm/dd/yyyy

  9. Tammy

    Please help! I am trying to write a formula that will convert the date in B3 (an appointment date from 2014) to the same day of the week in 2015. I’ve tried multiple formulas, but can’t seem to get the right one to recalculate my date for me based on the day of the week. All appointments on Tuesday in 2014 need to also be on Tuesday in 2015. Am I making sense? Thanks in advance for any assistance.

  10. Rahul

    I am looking for a formula which returns 1st if given date falls between 1 to 15 and will returns 15th if given date falls between 16 to 31

    1. Frédéric LE GUEN

      It’s simple. You just have to make a test with the function IF. Let’s say you have a date in cell A1
      IF(DAY(A1)<15,DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(A1),MONTH(A1),15))

  11. TuxSax

    There’s one function I need and wasn’t able to figure out.
    Let’s say I have a row with columns for each month of the year, e.g Jan 2014, Feb 2014, etc.
    Now I want to condition the year part by linking it to another cell that contains the year, so if I change the value of that cell to 2015, it will change Jan 2014, Feb 2014, etc. to Jan 2015, Feb 2015, etc.
    Is this possible?
    Thanks,

  12. Paudel

    my month and year is same, only the day changes so if i type the day month and year should come. eg if typed 24 then result 24/08/2014 similarly 29 then 29/08/2014

  13. Craig G

    I am looking for a formula that might help me provide an output of a month/year based upon the following… If the date within the cell in question is the 1st day of the month then the output will reflect that month. Ie, 2/1/14 = Feb 2014. However if the cell in question is 2/2/14 it would translate to March 2014.

    1. Frédéric LE GUEN

      In fact, you just have to change the format of the number.
      Read this article to know how http://www.excel-exercise.com/function/date-time/date-format/

  14. craig g

    I like to develop a formula which reflects the first of the next month if the date of the cell in question is any date later than the first of the month.

    1. Frédéric LE GUEN

      With the IF function + DATE function
      =IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))

  15. Rachel

    I am trying to create a formula where if hire date = 1st of the month then give me the date of the 1st of the month in three months, but if hire date = any other day of the month then give me the date of the 1st of the month in four months. Any ideas on how to create this?

    1. Frédéric LE GUEN

      With this formula, it works (your hire date is in E16)
      =IF(DAY(E16)=1,DATE(YEAR(E16),MONTH(E16)+3,1),DATE(YEAR(E16),MONTH(E16)+4,1))

Leave a Reply

%d bloggers like this: