Calculate any Day of the Week Using These Excel Formulas

Calculate any Day of the Week Using These Excel Formulas
Last Updated on 15/11/2023
Reading time: 2 minutes

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 for first and last Monday of the Month

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

Formula for first and last Friday of the Month

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 for Monday

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

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

Days in 2 weeks

Related articles

1 Comment

  1. 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)

    Reply

Leave a Reply

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

Calculate any Day of the Week Using These Excel Formulas

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

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 for first and last Monday of the Month

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

Formula for first and last Friday of the Month

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 for Monday

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

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

Days in 2 weeks

Related articles

1 Comment

  1. 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)

    Reply

Leave a Reply

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