Difficult formula

[Connie Martin]

I have this formula that is working great, but I need to expand on it and I
have no idea how:
=IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(J123="LOW
SLIP",3,1),NWD)))
The "NWD" at the end of the formula is a named range of dates (non-workdays).
What I would like to add to this formula is to do with the last part
(H123,IF(J123 ="LOW SLIP", etc.). This is what I need to incorporate in
that: if the 3 days added to the date in H123 includes a weekend, then it
would only add 1 day. But this would only be if J123 has "LOW SLIP" in it,
otherwise it would add the 3 days.
I hope someone can come to my aid because I am tired of updating this
manually.
Thank you. Connie

 

[Luke M]

I believe this is what you wanted:

=IF(H123="","",IF(H123="Not
Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H23,3)-H23>3,IF(J123="LOW
SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD)))

 

[Connie Martin]

I really appreciate you tackling this one! However, this formula is giving
be #VALUE! I'm looking at it and trying figure it out, but can't! What do
you think is giving this return? Connie

 

[Luke M]

Oops, I used H23, and you wanted H123

=IF(H123="","",IF(H123="Not
Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H123,3)-H123>3,IF(J123="LOW
SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD)))

That should fix it.

 

[Connie Martin]

Thank you. I get an answer now but the wrong date. Maybe this will help:
"LOW SLIP" is in J123.
Apr-08-09 (formated as a date) is in H123
The answer in G123 where my formula is should be: Apr-13-09, but I am now
getting Apr-09-09.
The reason it should be Apr-13-09 is because of "LOW SLIP" in J123 it would
normally have to add 3 days from H123, which would be Apr-11-09, which falls
on a weekend, therefore it should go to Monday, Apr-13-09.
The named range "NWD" is all non-workdays (statutory holidays and weekends).
The way my formula is working now is it's adding 3 business days, and I am
getting Apr-14-09.
I know this is confusing, so if you wish to leave it alone, I won't blame
you. I will work more with it Monday, if I get a chance. Thank you. Connie