In this article, you will find all the formulas that allow you to calculate the days of the week; like the next Monday, Tuesday, Wednesday, ….
Formula to calculate the First Monday of the month
The following formula returns the first Monday of the current month based on today's date.
=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+7-1)
Formula to calculate the last Monday of the month
The following formula returns the last Monday of the current month.
=DATE(YEAR(TODAY());MONTH(TODAY())+1;1)-WEEKDAY(DATE(YEAR(TODAY());MONTH(TODAY())+1;7-1))
Formula to calculate the first or last Friday of the month
Now, if you want to return on the last Friday of the month (or any other day), you have to change the last digit.
- For Monday you have to put 1
- For Tuesday, you have to put 2
- …
- For Friday, you have to put 5
- For Saturday, you have to put 6
- …
So the formula to return the first Friday of the Month is
=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+7-5)
And the last one is
=DATE(YEAR(TODAY());MONTH(TODAY())+1;1)-WEEKDAY(DATE(YEAR(TODAY());MONTH(TODAY())+1;7-5))
Next Monday in one week or 2 weeks
- In the next formula, replace MyDate with a real date to find the net Monday.
=MyDate-WEEKDAY(MyDate-2)+7*1
- Monday in 2 weeks
=MyDate-WEEKDAY(MyDate-2)+7*2
Previous Monday (same week as current date)
The following formula calculates the Monday preceding a date (MyDate) remaining in the same week.
=MyDate-WEEKDAY( MyDate-1)+1
Monday of the previous week
The following formula calculates the Monday before a date (MyDate) for the previous week.
=TODAY()-WEEKDAY(TODAY()-1)+1-7
Formulas to calculate ALL the days of the week (following or after a date).
For the following formulas, we consider 3 variables
- D : the reference date
- X : the value of the day (1 for Monday, 2 for Tuesday, …)
- N : the value of the week
Look at the results of formulas in the following Excel workbooks.
Previous days (today included)
The following formula calculates the days preceding a given date.
=D-WEEKDAY(D-X)+1
Previous days (Today excluded)
Here, the formula returns almost the same results except for the date of today which is excluded
=D-WEEKDAY(D-X)
Previous days Previous N weeks
Formula to calculate any day of the week N weeks ago
=D-WEEKDAY(D-X)+1-7*(N-1)
Upcoming days in the next few weeks
Formula to calculate any day of the week in N weeks (example here in 2 weeks).
=D-WEEKDAY(D-X)+1+7*N
Rick Rothstein
03/06/2022 @ 02:55
In your formula to calculate the last Monday of the month, your formula uses 11 function calls. I don't know if it would be more efficient given the function calls involved, but the following formula to do this uses only 8 function calls...
=EOMONTH(TODAY(),0)-WEEKDAY(EOMONTH(TODAY(),0))+2-7*(WEEKDAY(EOMONTH(TODAY(),0))<2)
Similarly, this would give the last Friday of the month (changing out the two 2's, the weekday number for Monday, with two 6's, the weekday number for Friday)...
=EOMONTH(TODAY(),0)-WEEKDAY(EOMONTH(TODAY(),0))+6-7*(WEEKDAY(EOMONTH(TODAY(),0))<6)