# of days outside of range

virtuosok

New Member
Joined
Sep 2, 2020
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
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"...
ABCD
1 HeadersThe earliest allowed dateExpected dateThe latest allowed date
2 Dates scheduled01-Sep-2004-Sep-2007-Sep-20
3 Date actual-----------blank------------10-Sep-20--------------blank--------------
4 Difference-----------blank------------+3--------------blank--------------
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,330
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
Joined
Sep 2, 2020
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
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....
 

Watch MrExcel Video

Forum statistics

Threads
1,112,865
Messages
5,542,956
Members
410,579
Latest member
bdubz
Top