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

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

## Related posts

• • Mac McCorison on 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.

• • Frédéric LE GUEN on 31/08/2019 at 03:34
Author

Thanks

• • David Stein on 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)

• • Frédéric LE GUEN on 31/08/2019 at 03:33
Author

Thanks 🙂