# of days outside of range

virtuosok

New Member
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--------------

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Eric W

MrExcel MVP
Try:

Book1
ABCD
1HeadersThe earliest allowed dateExpected dateThe latest allowed date
2Dates scheduled1-Sep-204-Sep-207-Sep-20
3Date actual10-Sep-20
4Difference+3
Sheet10
Cell Formulas
RangeFormula
C4C4=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

New Member
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

MrExcel MVP
Try:

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

THANKS

Replies
6
Views
63
Replies
3
Views
53
Replies
2
Views
72
Replies
1
Views
66
Replies
8
Views
40