Date

First day – Last day in Excel

Reading Time: < 1 minute

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

Examples with the date of TODAY

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)

Related posts

Weekly calendar 2020 in one formula

Frédéric LE GUEN

Calculation of tenths, hundredths and thousandths with Excel

Frédéric LE GUEN

Function DATE – YEAR – MONTH – DAY

Frédéric LE GUEN

7 comments

Pramod 20/12/2020 at 18:46

Thank you, Your post has helped me on a project i am working on.

Reply
Frédéric LE GUEN 20/12/2020 at 20:05

🙂

Reply
Kevin PhD 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.

Reply
Mac McCorison 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.

Reply
Frédéric LE GUEN 31/08/2019 at 03:34

Thanks

Reply
David Stein 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)

Reply
Frédéric LE GUEN 31/08/2019 at 03:33

Thanks 🙂

Reply

Leave a Comment