Excel - Find the Next or Previous Tuesday, where Week begins on Saturday

Question

Would really appreciate some help in writing a formula to calculate the Date of the 'next' or the 'previous' nominated day of the week (e.g. Tuesday as day 4), where the start of the week is a Saturday.

I have tried the following =IF(MOD(A1-1,7)>2,A1+2-MOD(A1-1,7)+7,A1+2-MOD(A1-1,7))

And also =date + 7 – WEEKDAY(date + 7 –dow)

But a) I can't find a way to get these to use Weekday(A1,16) - i.e. a Week running Sat-Sun b) if Today (in A1) is a Monday, it seems to calculate the 'next' Monday as Today's date, however this is the day on which it needs to find the Next Monday.

I really have tried hard to find a solution online, so I would really appreciate help in my struggles here!

Answer 1

If you want to get specific day of week, it really does not matter if your input is saturday or not.

Just as example. to get the previous tuesday of any given date, you need:

=SUMPRODUCT(--(WEEKDAY(A2-{0;1;2;3;4;5;6};2)=2)*(A2-{0;1;2;3;4;5;6}))

In case you need the next tuesday of any given date:

=SUMPRODUCT(--(WEEKDAY(A2+{0;1;2;3;4;5;6};2)=2)*(A2+{0;1;2;3;4;5;6}))

Finally, to find the closes Tuesday for a given date (it could be the next one or the previous one) just combine both:

=IF(A2-SUMPRODUCT(--(WEEKDAY(A2-{0;1;2;3;4;5;6};2)=2)*(A2-{0;1;2;3;4;5;6}))>SUMPRODUCT(--(WEEKDAY(A2+{0;1;2;3;4;5;6};2)=2)*(A2+{0;1;2;3;4;5;6}))-A2;SUMPRODUCT(--(WEEKDAY(A2+{0;1;2;3;4;5;6};2)=2)*(A2+{0;1;2;3;4;5;6}));SUMPRODUCT(--(WEEKDAY(A2-{0;1;2;3;4;5;6};2)=2)*(A2-{0;1;2;3;4;5;6})))

The tricky part here is this part: WEEKDAY(A2-{0;1;2;3;4;5;6};2)=2 I'm using WEEKDAY to get the weekdays of the next 7 days, including the given date and checking if it's a tuesdat or not (second day of week). You can adapt this part according to your regional settings.

Notice my argumen separator is ; so you may need to change those for commas.

Answer 2

After more trial and error, I've figured the following does work

=date + 8 – WEEKDAY(date + 8 –dow)

Phew... thanks if you've read this.