WEEKDAY function returning 42000+ when nested inside IF

[Gingercat]

Hi everyone! Got a fun formula here that checks the contents of specific row cells and based on their content, should output an adjusted date or a text value.

The purpose of this formula is to show the end user, in column F (where it is down-filled from F2 in an auto-expanded table that's now up to row 51, so that's the row I copied it from) roughly when to expect a result with a standard time delay of 2 days for (in column D) a "Y", 3 days for a "N", 4 days for a "Y" if the initial date + 2 days includes a weekend day (Saturday or Sunday), and 5 days for a "N" if the initial date + 2 days includes a weekend day, because the expected work processing doesn't take place on a weekend. If the G column contains a value (that gets input by the user when they verify that the processing has taken place and would usually be a date), the F column should change its contents to "Results Received" instead of an expected date. This makes it easier on the end user, since they only need to keep an eye on F values that (using conditional formatting) turn red when the expected date is "within the last week" (to account for any days of absence by the user).

The formula I currently have is this:

=IF(OR(G51="",G51="N/A"),IF(E51<>"",IF(E51<>"N/A",IF(D51="Y",IF(WEEKDAY(E51+2>5),E51+4,E51+2),IF(D51="N",IF(WEEKDAY(E51+2>5),E51+5),E51+3)),"N/A"),""),"Results Received")

E51 can have blank values because that row references a request for which processing hasn't started yet, or "N/A" because processing for that request isn't needed at all (the work has already been done before the request even came in, for instance, but the request itself still needs to be tracked).

My problem comes from the fact that when "WEEKDAY" is referenced by itself, it returns the expected single-digit value just fine, but if nested inside an IF, it returns a value above 42000, which will always return TRUE for the ">5" test. It seems that this function is supposed to MOD(x,7) the value before returning it, but doesn't do that step if it's inside an IF. Trying to MOD it myself results in Excel telling me the formula has an error even though the added brackets etc seem to line up fine. I upgraded from Excel 2013 to 2016, but it seems that this issue followed over to the newer version. Seems like a bug, and maybe it hasn't been caught yet for patching because people usually don't stick a WEEKDAY() inside an IF()?

There's probably a much more elegant way to achieve what I'm after as well; I've seen some very complex kludge formulas reduced to beautiful, elegant snippets; maybe the same can be done here

Advice and assistance are welcomed!

-Ginger-

[shg]

Just format the result as a date.

[Gingercat]

That doesn't help with trying to determine whether WEEKDAY+2 includes a weekend or not and adjusting accordingly. If it was as easy as just formatting as a date I would have done that already.

[Gingercat]

Fixed it:

=IF(OR(G51="",G51="N/A"),IF(E51<>"",IF(E51<>"N/A",IF(D51="Y",IF(MOD(WEEKDAY(E51),7)>4,E51+4,E51+2),IF(D51="N",IF(MOD(WEEKDAY(E51),7)>4,E51+5,E51+3))),"N/A"),""),"Scan Received")

If anyone has a shorter, more elegant way of achieving the same outcome as this formula, I'd be very happy to see it so I can learn from it