All formulas to calculate any day

All formulas to calculate any day
Last Updated on 18/04/2024
Reading time: 2 minutes

Welcome to our guide on mastering Excel formulas to calculate any day effortlessly.

In this article, we'll delve into essential formulas that simplify day calculations, catering to beginners and professionals alike.

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 *

All formulas to calculate any day

Reading time: 2 minutes
Last Updated on 18/04/2024

Welcome to our guide on mastering Excel formulas to calculate any day effortlessly.

In this article, we'll delve into essential formulas that simplify day calculations, catering to beginners and professionals alike.

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 *