Calculate any day of the week with formula

Calculate any day of the week with formula
Last Updated on 15/11/2022
Reading time: 2 minutes

Discover all the formulas that return the days of the week, like Monday, Tuesday, Wednesday, .... , before or after a date.

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())+6)

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

Next Monday from any date

In the following formula, replace the MyDate variable with a date in a cell to find the following Monday.

=MyDate-WEEKDAY( MyDate-2)+7

Monday in 2 weeks

The following formula calculates you the next Monday in 2 weeks from the MyDatedate.

=MyDate-WEEKDAY(MyDate-2)+7*2

Previous Monday (include the same day)

The following formula calculates the previous Monday a date (MyDate) by staying in the same week.

=MyDate-WEEKDAY(MyDate-1)+1

Monday of the previous week (exclude the same day)

The following formula calculates the previous Monday's date (MyDate) not in the same week.

=TODAY()-WEEKDAY(TODAY()-1)+1-7

Formulas for Monday

Formulas to calculate ALL days of the week (following or after a date).

For the following formulas, we consider 3 variables

  • D: the date
  • X: the value of the day (1 for Monday, 2 for Tuesday, ...)
  • N: the number of weeks

Look at the results of the 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

Previous days (Excluded today)

Here, the formula returns almost the same results except for today's date which is excluded

=D-WEEKDAY(D-X-1)-7*(N-1)

Previous days exclude today

Previous days N previous weeks

Formula to calculate any day of the week there are N weeks

=D-WEEKDAY(D-X)+1-7*(N-1)

Days of the next week

Coming days in the coming weeks

Formula to calculate any day of the week in N weeks (example here in 2 weeks).

=D-WEEKDAY(D-X)+1+7*N

Days in 2 weeks

Leave a Reply

Your email address will not be published. Required fields are marked *

Calculate any day of the week with formula

Reading time: 2 minutes
Last Updated on 15/11/2022

Discover all the formulas that return the days of the week, like Monday, Tuesday, Wednesday, .... , before or after a date.

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())+6)

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

Next Monday from any date

In the following formula, replace the MyDate variable with a date in a cell to find the following Monday.

=MyDate-WEEKDAY( MyDate-2)+7

Monday in 2 weeks

The following formula calculates you the next Monday in 2 weeks from the MyDatedate.

=MyDate-WEEKDAY(MyDate-2)+7*2

Previous Monday (include the same day)

The following formula calculates the previous Monday a date (MyDate) by staying in the same week.

=MyDate-WEEKDAY(MyDate-1)+1

Monday of the previous week (exclude the same day)

The following formula calculates the previous Monday's date (MyDate) not in the same week.

=TODAY()-WEEKDAY(TODAY()-1)+1-7

Formulas for Monday

Formulas to calculate ALL days of the week (following or after a date).

For the following formulas, we consider 3 variables

  • D: the date
  • X: the value of the day (1 for Monday, 2 for Tuesday, ...)
  • N: the number of weeks

Look at the results of the 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

Previous days (Excluded today)

Here, the formula returns almost the same results except for today's date which is excluded

=D-WEEKDAY(D-X-1)-7*(N-1)

Previous days exclude today

Previous days N previous weeks

Formula to calculate any day of the week there are N weeks

=D-WEEKDAY(D-X)+1-7*(N-1)

Days of the next week

Coming days in the coming weeks

Formula to calculate any day of the week in N weeks (example here in 2 weeks).

=D-WEEKDAY(D-X)+1+7*N

Days in 2 weeks

Leave a Reply

Your email address will not be published. Required fields are marked *