2

I am trying to figure out how to best put this requirement into a formula to be used in CELL B1 in Google Sheets:

If the date in cell A1 is a Sunday or Monday, cell B1 should show that week's date for Thursday, otherwise, it should show the following week's date for Thursday.

This formula works, but seems inefficient due to its length - is there any way I could condense this? 

=IF(WEEKDAY(A1)=1,A1+4,
 IF(WEEKDAY(A1)=2,A1+3,
 IF(WEEKDAY(A1)=3,A1+9,
 IF(WEEKDAY(A1)=4,A1+8,
 IF(WEEKDAY(A1)=5,A1+7,
 IF(WEEKDAY(A1)=6,A1+6,
 IF(WEEKDAY(A1)=7,A1+5,"")))))))
player0
  • 124,011
  • 12
  • 67
  • 124
J. Kubassek
  • 205
  • 2
  • 14

1 Answers1

1

you can just use:

=A1+MATCH(WEEKDAY(A1), {"","",2,1,7,6,5,4,3}, 0)

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    This is much nicer, thank you, although I admit I'm still trying to understand how that works so I can apply it to other scenarios, for example; if the date in cell A1 is a Saturday, cell B1 should show the date for the week after the following Thursday, otherwise the date displayed should be for the following Thursday: `=IF(WEEKDAY(A39)=1,A39+11, IF(WEEKDAY(A39)=2,A39+10, IF(WEEKDAY(A39)=3,A39+9, IF(WEEKDAY(A39)=4,A39+8, IF(WEEKDAY(A39)=5,A39+7, IF(WEEKDAY(A39)=6,A39+6, IF(WEEKDAY(A39)=7,A39+12, "")))))))` – J. Kubassek Oct 31 '19 at 11:47
  • 1
    think of it like this: if weekday of A1 is **2**, then `MATCH` will lookup the value 2 in `{"","",2,1,7,6,5,4,3}` and output `3` because value 2 is on 3rd position in this string `{"","",2,1,7,6,5,4,3}` – player0 Oct 31 '19 at 13:46
  • 1
    I get it now! So in my new scenario, I would do this: `=A1+MATCH(WEEKDAY(A1), {"","","","","",6,5,4,3,2,1,7}, 0)` Thanks so much! – J. Kubassek Oct 31 '19 at 20:51