Excluding Weekends/Holidays in "IF" formula.

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

Excluding Weekends/Holidays in "IF" formula.

Post by 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.
You do not have the required permissions to view the files attached to this post.

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

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

Post by HansV »

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.

Post by snb »

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.

Post by cking619 »

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.

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

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

Post by HansV »

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.

Post by cking619 »

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.

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

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

Post by 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
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.

Post by snb »

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