Calculation help (If And)

[sfitzpat11]

Hello,
I am looking for some assistance with a formula to calculate product shipment performance.
Cell A1 = Agreement days for all orders. Lets use 10 days
Cell B1 = Customer request. They might want it sooner or maybe a delayed shipment.
Cell C1 = Actual ship date.
You can see that there could be several situations but the B1 cell complicates this. Without B1 I could just look at the difference between A1 and C1. Examples of my challenge:
1) Customer request date is 12 days and the ship date was 12 days. This is on time but cannot use the 10 days per the agreement. Value of 0.
2) Customer request date is 8 days and the ship date was 10 days. While not meeting the request date the agreement date is still met. Value of 0.
3) Customer request date is 8 days and the ship date was 9 days. While not meeting the request date it was better than the agreement date. Value of -1.

Any help would be greatly appreciated.

 

[Scott Huish]

How do you get the number of days it took to ship if you have no column for the initial request date?

 

[sfitzpat11]

Hi Scott,
The request date / number of days is in cell B1.

 

[Scott Huish]

So is it a date in B1 or a number in B1?

 

[sfitzpat11]

Hi Scott,
Just a number. I have calculated the differences so just looking at how to use them. Could also start with
Cell A1 = Agreement days for all orders. Lets use 10 days.
Cell B1 = Customer Order date (start date)
Cell C1 = Agreement ship date (A1 + B1)
Cell D1 = Customer request date
Cell E1 = Actual ship date

 

[Scott Huish]

It seems that the only way you get a -1 is if the ship date is less than the agreement date. If that's the case, then in F2:
=-(E2<C2)

Copy down.

Excel 2010
	A	B	C	D	E	F
1	Agreement Days	Customer Order Date	Agreement Ship Date	Customer Request Date	Actual Ship Date
2	10	9/15/2017	9/25/2017	9/27/2017	9/27/2017	0
3	10	9/15/2017	9/25/2017	9/23/2017	9/25/2017	0
4	10	9/15/2017	9/25/2017	9/23/2017	9/24/2017	-1
Sheet1

Cell Formulas
Range	Formula
C2	=A2+B2
C3	=A3+B3
C4	=A4+B4
F2	=-(E2)
F3	=-(E3)
F4	=-(E4)

 

[sfitzpat11]

Thanks again. But I am looking for a formula that can manage any variable in columns D and E. In your formula if I change the ship date to 9/30 it does not tell me that I am 3 days late.

 

[Scott Huish]

OK, does this work for you?

=MIN(E2-D2,E2-C2)

Copy down.

Excel 2010
	A	B	C	D	E	F
1	Agreement Days	Customer Order Date	Agreement Ship Date	Customer Request Date	Actual Ship Date
2	10	9/15/2017	9/25/2017	9/27/2017	9/27/2017	0
3	10	9/15/2017	9/25/2017	9/23/2017	9/25/2017	0
4	10	9/15/2017	9/25/2017	9/23/2017	9/24/2017	-1
Sheet1

Cell Formulas
Range	Formula
F2	=MIN(E2-D2,E2-C2)
F3	=MIN(E3-D3,E3-C3)
F4	=MIN(E4-D4,E4-C4)