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)
Calculate First Day of the Month with EOMONTH
There’s no Excel function to calculate the first day of the month, but the EOMONTH function will calculate the last day of a month.
To find the first day of a month we will calculate the last day of the previous month and add one day.
=EOMONTH(B3,-1)
Here let’s find the last day of the previous month:
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)
Ref: Return the first day or the last day (Month, Quarter, Year) – Excel Exercise (excel-exercise.com)