## Excluding Weekends/Holidays in "IF" formula.

cking619
NewLounger
Posts: 5
Joined: 16 Jan 2021, 14:10

### Excluding Weekends/Holidays in "IF" formula.

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".

You do not have the required permissions to view the files attached to this post.

HansV
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Excluding Weekends/Holidays in "IF" formula.

In I2:

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

snb
4StarLounger
Posts: 542
Joined: 14 Nov 2012, 16:06

### Re: Excluding Weekends/Holidays in "IF" formula.

Weekend Sa, So

=NETWORKDAYS.INTL(max(E2:F2), G2;1)
Last edited by snb on 01 Nov 2023, 19:59, edited 1 time in total.

cking619
NewLounger
Posts: 5
Joined: 16 Jan 2021, 14:10

### Re: Excluding Weekends/Holidays in "IF" formula.

HansV wrote:
31 Oct 2023, 08:09
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
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Excluding Weekends/Holidays in "IF" formula.

This looks OK to me:

S2472.png

What would you want to be different?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

cking619
NewLounger
Posts: 5
Joined: 16 Jan 2021, 14:10

### Re: Excluding Weekends/Holidays in "IF" formula.

That's odd.

This is what it does when I plug it in.
You do not have the required permissions to view the files attached to this post.

HansV
Posts: 78071
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Excluding Weekends/Holidays in "IF" formula.

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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

snb
4StarLounger
Posts: 542
Joined: 14 Nov 2012, 16:06

### Re: Excluding Weekends/Holidays in "IF" formula.

My version
You do not have the required permissions to view the files attached to this post.