Excel Date Tolerance?

ssmacc · 2 Feb 2018 at 13:21

Hello, I’m trying to write a formula to solve the following.

I have two dates (A1, B1) that should be 14 days apart +/- 3 days…in C1 what formula would solve this?

I’m sure it’s simple for someone in the know, I’m googling it and although I can solve it with numbers I’m struggling with days…

Thanks in advance,

Grrrrr · 2 Feb 2018 at 13:37

To find how many days apart two dates are the formula is simple:

=B1-A1

So you want to know if the answer is between -3 and 3:

=ABS(ABS(B1-A1)-14)<=3

The ABS() function just makes any number positive e.g. -2 becomes 2. The second ABS in the formula is so that it doesn't matter whether the first date is before or after the second date. The first ABS is because you're checking for +/- 14 days. Finally you're checking the answer is less than or equal to 3. The formula will return TRUE or FALSE as the answer.

ssmacc · 2 Feb 2018 at 13:51

Thanks, looks spot on!

I was looking at a date in "A5" and "C5" and had come up with the following, which gave me the opposite of what I wanted

=7+A5=CA5=AND(7+A5<=C5+3,7+A5>=C5-3)

ssmacc · 2 Feb 2018 at 13:58

Grrrrr said:
To find how many days apart two dates are the formula is simple:

=B1-A1

So you want to know if the answer is between -3 and 3:

=ABS(ABS(B1-A1)-14)<=3

The ABS() function just makes any number positive e.g. -2 becomes 2. The second ABS in the formula is so that it doesn't matter whether the first date is before or after the second date. The first ABS is because you're checking for +/- 14 days. Finally you're checking the answer is less than or equal to 3. The formula will return TRUE or FALSE as the answer.

Hello,

Thanks for this (again) the second date should always be after the first date, as in -14 days should trigger "False", can this be amended to account for that?

Cheers,

Grrrrr · 2 Feb 2018 at 14:00

=ABS(B1-A1-14)<=3

EDIT - Got A1 and B1 the wrong way round!

ssmacc · 2 Feb 2018 at 14:34

Cheers,
Copied that directly from the forum and due to the white font colour got very confused when the result was completely blank no matter what!

(I could see the formula in the fx bar but nothing in the cell)