# Calculate any day of the week with formula

Last Updated on 15/11/2022 by Frédéric LE GUEN

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 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 (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 N previous weeks

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

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

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

# Calculate any day of the week with formula

Last Updated on 15/11/2022 by Frédéric LE GUEN

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 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 (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 N previous weeks

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

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

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