Contents

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

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.

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

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

### First day of previous month

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

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

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.

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

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.

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

## arieta

02/25/2016 at 10:51 pm (UTC 0) Link to this comment

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

## Anonymous

02/08/2016 at 10:49 am (UTC 0) Link to this comment

change = Year – Month – Date wise short ..

## Anurag

10/19/2015 at 9:41 am (UTC 0) Link to this comment

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

## Priyankar Samanta

10/06/2015 at 6:07 pm (UTC 0) Link to this comment

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

## Anonymous

07/13/2015 at 9:39 am (UTC 0) Link to this comment

HEY

HOW DO I GET YEAR MONTH DAY IN EXCEL

## liz barnes

04/12/2015 at 3:55 pm (UTC 0) Link to this comment

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

## Anonymous

02/13/2015 at 1:43 pm (UTC 0) Link to this comment

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

## lisa

02/05/2015 at 9:38 pm (UTC 0) Link to this comment

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

## Tammy

12/09/2014 at 6:41 pm (UTC 0) Link to this comment

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.

## Rahul

12/09/2014 at 9:50 am (UTC 0) Link to this comment

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

## FrÃ©dÃ©ric LE GUEN

12/13/2014 at 6:30 pm (UTC 0) Link to this comment

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

## TuxSax

12/01/2014 at 7:36 am (UTC 0) Link to this comment

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,

## Paudel

10/19/2014 at 3:25 am (UTC 0) Link to this comment

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

## Craig G

06/04/2014 at 2:56 pm (UTC 0) Link to this comment

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.

## FrÃ©dÃ©ric LE GUEN

08/06/2014 at 10:40 am (UTC 0) Link to this comment

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/

## craig g

06/04/2014 at 2:00 pm (UTC 0) Link to this comment

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.

## FrÃ©dÃ©ric LE GUEN

06/10/2014 at 7:17 am (UTC 0) Link to this comment

With the IF function + DATE function

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

## Rachel

05/15/2014 at 6:31 pm (UTC 0) Link to this comment

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?

## FrÃ©dÃ©ric LE GUEN

05/15/2014 at 9:10 pm (UTC 0) Link to this comment

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