Changing any Weekday to a Specific Weekday

[miyengar]

Here's my problem: I'm looking for a formula to change a weekday in the previous month - the day that is 30 days from a day in this month - to a Monday of the following week, if it's a weekday oother than a Monday. For example, if 30 days from the current month (June) is May 24, 2012 (Thursday), I want the formula result to show May 28, 2012 (next Monday). Greatly appreciate any help!

 

[T. Valko]

Here's my problem: I'm looking for a formula to change a weekday in the previous month - the day that is 30 days from a day in this month - to a Monday of the following week, if it's a weekday oother than a Monday. For example, if 30 days from the current month (June) is May 24, 2012 (Thursday), I want the formula result to show May 28, 2012 (next Monday). Greatly appreciate any help!

Maybe this...

A2 = some date

=A2-30+CHOOSE(WEEKDAY(A2-30),1,0,6,5,4,3,2)

 

[miyengar]

Many thanks! This works perfectly!

 

[T. Valko]

Many thanks! This works perfectly!

You're welcome. Thanks for the feedback!

 

[miyengar]

Again, thanks! Small request: would you explain the formula, especially the "Choose" part and the numbers that follow? Thanks!

 

[T. Valko]

Again, thanks! Small request: would you explain the formula, especially the "Choose" part and the numbers that follow? Thanks!

Here ya go...

A2 = some date

=A2-30+CHOOSE(WEEKDAY(A2-30),1,0,6,5,4,3,2)

You want the date for the next Monday if the date minus 30 days is already not a Monday.

We get the date:

A2-30

Then we test what weekday that is:

WEEKDAY(A2-30)

That will return a number from 1 to 7 where:

1 = Sunday
2 = Monday
3= Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

We then use that number to CHOOSE how many days we need to add to the date, A2-30, to give us the next Monday date.

Weekday 1 (Sunday) add 1 day
Weekday 2 (Monday) add 0 days
Weekday 3 (Tuesday) add 6 days
Weekday 4 (Wednesday) add 5 days
Weekday 5 (Thursday) add 4 days
Weekday 6 (Friday) add 3 days
Weekday 7 (Saturday) add 2 days

 

[miyengar]

Many thanks for your clear and succint explanation! Greatly appreciate your help!!

 

[T. Valko]

Many thanks for your clear and succint explanation! Greatly appreciate your help!!

You're welcome. Thanks for the feedback!