Excluding Weekends/Holidays in "IF" formula.

[cking619]

Scenario:

Column E = Required Submittal Date
Column F = Deferred Date
Column G = Date Delivered
Column I = Delivery Lag

Issue: I am trying to get "E1-G1 = I1" when Col F is blank. As well as, "F1-G1 = I1" when Col F has a date. The problem for me is I can't make that work with accounting for Weekends and Holidays. I need those formulas to only add "work days".

I hope that made sense.

[HansV]

In I2:

=IF(G2="", "", LET(d, IF(F2="", E2, F2), IF(d<=G2, NETWORKDAYS(d, G2)-1, 1-NETWORKDAYS(G2, d))))

[snb]

Weekend Sa, So

=NETWORKDAYS.INTL(max(E2:F2), G2;1)

[cking619]

In I2:

=IF(G2="", "", LET(d, IF(F2="", E2, F2), IF(d<=G2, NETWORKDAYS(d, G2)-1, 1-NETWORKDAYS(G2, d))))

I appreciate the reply. My only hold up on this one is how it handles negative days. For example, if G2 is before E2, it adds negative days so rather than -1 it would be -3.

[HansV]

This looks OK to me:

S2472.png

What would you want to be different?

[cking619]

That's odd.

This is what it does when I plug it in.

[HansV]

Many of the values in the date column are actually text values. If you set the horizontal alignment to General, you'll see that some cells are left-aligned (text), others are right-aligned (dates).
In the attached version I have used Text to Columns to convert them to 'real' dates.

Test Case.xlsx

[snb]

My version