Forum Discussion
Formula Help
I have the following formula currently in use in a spreadsheet.
=T25+CHOOSE(WEEKDAY(T25),1,0,-1,-2,-3,0,-1)
This is to calculate a Monday or Friday date in my spreadsheet. Our calendars have changed and now I need this formula to be able to pick dates Monday through Thursday, and if it falls on a Friday to back date to Thursday. Can anyone help me with this? 😀😀
8 Replies
Hans...you are a life saver! It's been so long since I've modified this that I forgot what the 0,0,1,1 etc meant. 😄 I truly appreciate you and your knowledge!
Would you be able to assist with 1 more? I overlooked this one in the mix of things.
=IF(WEEKDAY(G21,2)>1,G21-WEEKDAY(G21,2)+1,G21-WEEKDAY(G21,2)-6)
=IF(WEEKDAY(Q18,2)>5,Q18-WEEKDAY(Q18,2)+5,Q18-WEEKDAY(Q18,2)-2)
=W18+CHOOSE(WEEKDAY(W18),1,0,-1,-2,-3,0,-1)
=WORKDAY.INTL(M10+181,-1,"0111011",$J$9:$J$38)
I also have these 2 that need to calculate the same, Monday thru Thursday and back date to Thursday if it falls on Friday. Are you able to assist with those as well? I appreciate the help!
=Q18+CHOOSE(WEEKDAY(Q18),1,0,0,0,0,-1,-2)
=W18+CHOOSE(WEEKDAY(W18),1,0,0,0,0,-1,-2)
=WORKDAY.INTL(M10+181,-1,"0000111",$J$9:$J$38)
Thank you Hans! The Q18+CHOOSE(WEEKDAY(Q18),1,0,0,0,0,-1,-2) formula is still producing Friday dates, does something need to be modified?
Thank you Hans :) Monday through Thursday is what I'm looking for. I will plug this in and see if it does what's needed!
🤩
Do you mean Monday OR Thursday? If so:
=T25+CHOOSE(WEEKDAY(T25),1,0,-1,-2,0,-1,-2)
Or Monday to Thursday? If so:
=T25+CHOOSE(WEEKDAY(T25),1,0,0,0,0,-1,-2)
