Need help calculating "negative" day count using vlookup of exclusion dates

[gmaboing]

I have created a VLOOKUP list of my company's office holidays. This list has only non "standard" holidays and weekends. Example: 7/4 and 7/5/2016 are in the VLOOKUP list because my company is closed and pays us for those days.



Here is what I am trying to do:

There is hard delivery date of 6/30/2016 (Thurs) but the delivery date was 7/7/2016 (Thurs).
My office is closed on 7/4/2016 and 7/5/2016.
I want to return the number of days the data was late, including the exclusion dates listed in the VLOOKUP.

I can calculate the dates for positive numbers, but cannot get negative numbers to work.

 

[Joe4]

Sounds like you want to use the NETWORKDAYS functionality.
See: https://www.techonthenet.com/excel/formulas/networkdays.php

There is also a companion WORKDAY function:
https://www.techonthenet.com/excel/formulas/workday.php

 

[gmaboing]

Yes, I have that part. The part that I cannot get to work is how to subtract two dates using the exclusion list and return the correct negative number.

My company is closed on random non-holiday days. The dates are not the same each year.... (hope that helps clarify)

 

[Joe4]

I am not sure I understand. NETWORKDAYS will return negative numbers if the End Date is before the Start Date.

What is your formula?
Can you walk us through an example, along with your expected results?

 

[gmaboing]

I am not sure I understand. NETWORKDAYS will return negative numbers if the End Date is before the Start Date.

What is your formula?
Can you walk us through an example, along with your expected results?

I think it's a little messy (but I understand it). Will do my best to put something together.

 

[gmaboing]

I think it's a little messy (but I understand it). Will do my best to put something together.

I hope this helps

DATES
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6/16/2016[/TD]
[TD]6/20/2016[/TD]
[TD]6/27/2016[/TD]
[TD]6/29/2016[/TD]
[TD]6/30/2016[/TD]
[TD]7/7/2016[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




STAGES
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]TAT[/TD]
[TD]Phase2[/TD]
[TD]Phase3[/TD]
[TD]Phase4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]-3[/TD]
[TD]-1[/TD]
[/TR]
</tbody>[/TABLE]





CHOLIDAYS
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5/27/2016[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7/4/2016[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]7/5/2016[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]9/2/2016[/TD]
[TD]Friday[/TD]
[/TR]
</tbody>[/TABLE]









Total turnaround time is 10 business days

Dates!A1 is the date to begin processing (date entered manually)

AUTOMATIC calculation for Dates!B1
=IF(Dates!A1="","",WORKDAY(Dates!A1,Stages!B2,CHolidays!$A$1:$A$4))

AUTOMATIC calculation for Dates!C1
=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!C2, CHolidays!$A$1:$A$4))

AUTOMATIC calculation for Dates!D1
=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!D2, CHolidays!$A$1:$A$4))

AUTOMATIC calculation for Dates!E1
=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!A2, CHolidays!$A$1:$A$4))


Here is where I need help…
I need to subtract Dates!E1 - Dates!F1, taking into consideration CHolidays!$A$1:$A$4.
That number should display in G1. The results should be -3.

 

[Joe4]

AUTOMATIC calculation for Dates!E1
=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!A2, CHolidays!$A$1:$A$4))

Are you sure that is right?
It creates a circular reference, since you are entering a formula in cell E1 that references itself (Dates!E1).

 

[gmaboing]

The part that says "AUTOMATIC calculation for Datesxxxx" is not a calculation, that's my way of the logic below will be in that cell.


"=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!A2, CHolidays!$A$1:$A$4))" is the logic used in the Dates!E1 cell for automatic calculation

Does that make sense? If not, i'm not going to worry about it any more.

 

[Joe4]

But you have the cell calling itself in the logic.

Let's pick apart your last statement:

"=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!A2, CHolidays!$A$1:$A$4))" is the logic used in the Dates!E1 cell for automatic calculation

First, let's look at the logic (notice the part in red):

"=IF(Dates!A1="","",WORKDAY(Dates!E1, Stages!A2, CHolidays!$A$1:$A$4))"

Now, look at where you are putting the calculation (notice the part in red):

is the logic used in the Dates!E1 cell for automatic calculation

So, it looks like you are saying that the logic for calculating the value in Dates!E1 is using the value of Date!E1 itself! That is known as a circular reference.
Are you familiar with this term? If not, see this: Circular Reference in Excel - EASY Excel Tutorial

Is this really what you intended to do, or is just a typo?

 

[gmaboing]

UGH! Darn it. It's a typo--my mistake.
At this point, I'm frustrated and will have to sit on this for a few days. I've got meeting and all day training for the next few days.

Thanks for taking a look at this with me so far.