25/01/2021
Date

# First day – Last day in Excel

On this page, you will find some formulas to calculate specific days automatically. Building specific dates, such as the first or last day, is always a very complex task, in Excel or any other software.

The function TODAY has been used in all the examples but you can replace it with your own date value.

## First day - Last day of the month

These calculations is really easy to do with the DATE function.

### First day of the month

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

### Last day of the month

In Excel, 1 is one day and not one hour. So using this rule, we just have to subtract 1 from the formula that calculates the first day of the next month.

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

Or you can use the function EOMONTH

=EOMONTH(TODAY(),0)

### First Monday of the month

=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+6)

### Last Monday of the month

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

## Next Quarter

If you want to return the first day of the current quarter, we need to use the MOD function to return a gap of 0, 1 or 2 months

=DATE(YEAR(B2);MONTH(B2)-MOD(MONTH(B2)-1;3);1)

And to return the first day of the next quarter, the formula is

=DATE(YEAR(B2);MONTH(B2)-MOD(MONTH(B2)-1;3)+3;1)

## First day - Last day of the year

### First day of the year

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

### Last day of the year

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

or

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

### First Monday of the year

=DATE(YEAR(TODAY()),1,8)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

### Last Monday of the year

=DATE(YEAR(TODAY())+1,1,0)-WEEKDAY(DATE(YEAR(TODAY()),1,6))

## Number of days in the month

A very important and often ignored piece of information is the number of days in a month. For monthly reports, this information is very important.

The following formulas allow you to return the number of days in a month.

### Number of days in the current month

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

### Number of days in the next month

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

### Number of days in the previous month

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

#### Function DATE – YEAR – MONTH – DAY 20/12/2020 at 18:46

Thank you, Your post has helped me on a project i am working on. 20/12/2020 at 20:05

🙂 09/12/2020 at 23:49

Thank You. First and Last Day of the Year Formulas are greatly appreciated.
So great to find help when needed. 30/08/2019 at 17:16

Thank you for publishing these. It helped with a spreadsheet I'm working on. I did discover an error in a couple of the formulas though. The YEAR function is missing a closing parenthesis in the first day of the year and last day of the year examples. 31/08/2019 at 03:34

Thanks 30/08/2019 at 14:37

Nice blog post. I found a typo that you may want to fix. First day of year - You are missing an end parenthesis. It should read: =DATE(YEAR(TODAY()),1,1) 31/08/2019 at 03:33

Thanks 🙂