Nested IF statement help

[wadergirl]

I know this is going to be super easy, I'm just having one of those mental/emotional days where I just cant even. I had a working formula in a template previously, but then of course some genius (not me) saved over the template and the working formula was lost.

Basically, I need an IF statement that returns this Thursday's date, if today is Monday, but NEXT Thursday's date if today is Tuesday-Friday. i.e. Today, the formula would return 5/7. Tomorrow and the rest of the week, it would return 5/14.

The formula that I did have was something along the lines of:
=IF(A5="","",IF(WORKDAY(Today())=2,(Today()+3))

Unfortunately, in trying to re-create it with the 5 other possibilities of what WORKDAY equals and how many days would need to be added to Today(), I kept getting #VALUE error, and like I said, I'm at the point of "can't even" today. Please help!

 

[Eric W]

Maybe:

=TODAY()+CHOOSE(WEEKDAY(TODAY()),4,3,9,8,7,6)

 

[wadergirl]

Thanks Eric! I am not familiar with the CHOOSE function, but I plugged that in, and at least today it works. Looks like definitely a much simpler solution! I'll be back tomorrow to let you know if it doesn't update to 5/14 as I expect.

(And this is mostly just for self-reference in case I need to come back to this thread for something else, but I just thought I would post my full NON-working formula, found in a duplicate backup of my template.. LOL... too bad I apparently didn't save the backup with my working version of this formula... this is the full formula that gave the #VALUE error.)
=IF(B5="","",CONCATENATE(A5,"-",TEXT(IF(WEEKDAY(TODAY()) = 6, TODAY()+3,(IF(WEEKDAY(TODAY()) = 5,TODAY()+4),(IF(WEEKDAY(TODAY()) = 4,TODAY()+6),(IF(WEEKDAY(TODAY()) = 3,TODAY()+7),(IF(WEEKDAY(TODAY()) = 2,TODAY(),"")))))),"mmddyy"),"-",BG5))

 

[Eric W]

In looking at your original formula, a couple of things stand out. First, the logic it uses and what I use is very similar. The first parameter of CHOOSE is a number from 1 to whatever. The rest of the parameters is a list of results you want. If the first parameter is a 1, then take the first item from the list. If it's a 2, then choose the second item from the list, and so on. So if I give it WEEKDAY(TODAY()) as the first parameter, that gives me the day of the week, and rest of the list is how many days I need to add to get to the day I want. So I add 4 for Sunday, 3 for Monday, 9 for Tuesday, etc.

The second thing I noticed is that you take the resultant date, format it, and append - "BG5". Let me know if you need help figuring out how to do that.

 

[wadergirl]

The second thing I noticed is that you take the resultant date, format it, and append - "BG5". Let me know if you need help figuring out how to do that.

So the ultimate end result of the formula is a CONCATENATE of "A5-mmddyy-BG5" (where MMDDYY is the date formula that was the issue) - example would be "01-050720-AP"

 

[wadergirl]

P.S. When I replaced all of the nested IF statements with the TODAY()+CHOOSE(WEEKDAY(TODAY()),4,3,9,8,7,6), the CONCATENATE portion did work correctly.

 

[wadergirl]

Not that I really doubted you, but thank you again, Eric! Your formula is much better than mine (when it was working).

 

[Eric W]

Not that I really doubted you, but thank you again, Eric! Your formula is much better than mine (when it was working).

Glad it helped!