Networkdays excluding Holidays with nested ifs?

[Just_Pat]

Hello all,

I don't think my issue is as complicated as some of the ones I've seen on this site but i think my lack of understanding in the formula is causing me problems.

I was instructed to gather some metrics using data from out ticketing system. This is where i am running into problems, I need to find out how many work days away from target was the ticket completed. Our ticketing system uses 2 separate columns for target date one for HR related tickets the other for all the rest. I managed to get it to a point where i can get the amount of days between the dates using the formula bellow but i don't know where i would insert the NETWORKDAYS and Holidays portion.

here is an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Target Date[/TD]
[TD="align: center"]HR Target date[/TD]
[TD="align: center"]Completed Date[/TD]
[TD="align: center"]Days from Target[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]03/21/2018[/TD]
[TD][/TD]
[TD]05/03/2018[/TD]
[TD="align: center"]43[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]04/10/2018[/TD]
[TD]05/03/2018[/TD]
[TD="align: center"]-5[/TD]
[/TR]
</tbody>[/TABLE]

Current formula: =IF(B1="",C1-A1,IF(A1="",C1-B1))

I don't know if there is a better way of doing this so I will gladly accept any assistance you can provide.

Thanks in advance

 

[Joe4]

Your formula would look something like this:

[COLOR=#333333]=IF(B1="",NETWORKDAYS(A1,C1,Z1:Z10),IF(A1="",[/COLOR][COLOR=#333333]NETWORKDAYS(B1,C1,Z1:Z10)[/COLOR][COLOR=#333333]))[/COLOR]

where Z1:Z10 is your range of holidays (adjust as needed).

See: https://support.office.com/en-us/article/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7

Also note that if you have both A1 and B1 populated, you are going to get an error, as you have not specified what you want to happen in that situation (so it would go to the optional FALSE argument of that second IF, which you have not populated).

 

[Just_Pat]

Awesome this works exactly as you said and your powers of prediction are very good. In my mind i could not imagine a scenario where both A1 and B1 would be filed but i was proven wrong, turns out we have special change type requests that use both dates. I guess i would just need to add NETWORKDAYS(A1,C1,Z1:Z10) somewhere at the end?

 

[Joe4]

Try this simplification:

[COLOR=#333333]=NETWORKDAYS(IF(A1="",B1,A1),C1,Z1:Z10)[/COLOR]

In this case, if A1 is populated, it will use A1 (regardless of what B1 is).
It will only use B1 if A1 is blank.

 

[Just_Pat]

Try this simplification:

[COLOR=#333333]=NETWORKDAYS(IF(A1="",B1,A1),C1,Z1:Z10)[/COLOR]

In this case, if A1 is populated, it will use A1 (regardless of what B1 is).
It will only use B1 if A1 is blank.

Wow this works perfectly well thank you for you help i think i was over complicating things a bit.