-
Checking if cell value is within +-15 from next one.
Hi everyone,
I have list of dates (column A) and I need to check if they are within X(lets say 15) days of the previous or next date.
I would like Excel to put a 1 column B if this is the case, and 0 otherwise.
You can check the attached .xls for an example - Days +-15.xlsx
Thanks a lot!
V
-
Hi
For ex., in B2:
=IF(ISNUMBER(A2),OR(IF(ISNUMBER(A1),ABS(A2-A1)<=15),IF(ISNUMBER(A3),ABS(A2-A3)<=15))+0,"")
Copy down
-
in B2 (different because it only has one date to compare against):
=IF(ABS(A2-A3)<=15,1,0)
in B3 then copy down (this formula MUST be entered with Ctrl + Shift + Enter, not just Enter):
=IF(MIN(ABS(A2:A3-A3:A4))<=15,1,0)
Strictly you should make the formula in the last row (B7 in your example) a bit different too, but if the cell below the last date in column A is blank you'll get away without doing so.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Thanks lecxe and p45cal!
Didn't think of using ABS(.) at all. Closing this as resolved!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules