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

Related articles

• The WEEKDAY function – Secrets and Errors
• Calculate Monday Previous Week with Excel
• Calculate Monday in 2 weeks with Excel
• Calculate the Number of Days between 2 dates in Excel
• First day – Last day in Excel
• How to Calculate the Week Number in Excel?
• Calculate a date from the week number in Excel
• Date Picker in Excel
• Write Months in Excel in a Foreign Language
• Displaying Days in Letters in Excel
• Calculate Monday Previous Week with Excel
• Convert a Text Date to a Number Date