# of days outside of range

[virtuosok]

Hi,
I have two dates - these are B2 and D2 below. The expectation is that a specific event should happen in-between them, but it may also occur outside of this range. Just as a FYI, C2 serves as a reference, a mid point between these two extremes and can essentially be ignored.
Can you help me with the formula for cell C4 which would calculate how many days (if any) outside of the B2...D2 range the actual date in C3 is? In the example below, this is +3 days, but it can be -10 days, ideally the formula should preserve the "minus" or "plus" sign. Alternatively, the date in C3 can be within the B2-D2 window, in which case it would be nice for the formula to return "OK"...

A	B	C	D
1 Headers	The earliest allowed date	Expected date	The latest allowed date
2 Dates scheduled	01-Sep-20	04-Sep-20	07-Sep-20
3 Date actual	-----------blank------------	10-Sep-20	--------------blank--------------
4 Difference	-----------blank------------	+3	--------------blank--------------

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Headers	The earliest allowed date	Expected date	The latest allowed date
2	Dates scheduled	1-Sep-20	4-Sep-20	7-Sep-20
3	Date actual		10-Sep-20
4	Difference		+3
Sheet10

Cell Formulas
Range	Formula
C4	C4	=IF(C3<B2,C3-B2,IF(C3>D2,C3-D2,"OK"))

I used a custom format of "+"0;"-"0 in C4 to preserve the sign.

 

[virtuosok]

Thanks Eric W,
This works well! I like your solution on custom format
The only remaining (relatively minor) issue is that if there's no entry in C3, I'm getting -44095 in C4. If would be best if it were blank, or N/A....

 

[Eric W]

Try:

=IF(C3="","N/A",IF(C3<B2,C3-B2,IF(C3>D2,C3-D2,"OK")))

 

[virtuosok]

THANKS