2

I'm trying to find the smallest difference between 2 sets of dates.

The most simple array formula sort of works and finds the smallest difference, but if 1 of the columns is missing a date, it treats the blank cell as 0 and the result of MIN difference in days becomes something like -42634 (which is not what I want).

{=MIN(X23:X95-P23:P95)}

After some reading, I tried to subtract only if both columns is not blank and while it works with a single row...

=IF(OR(ISBLANK(X23),ISBLANK(P23)),"",X23-P23)

it returns a #VALUE! error if I try wrapping it with MIN...

=MIN(IF(OR(ISBLANK(X23),ISBLANK(P23)),"",X23-P23))

I'm no Excel expert, but I'm guessing it's because a MIN of nothing but blank cells is going to throw an error as I read it's supposed to ignore blanks. Using MIN/MAX on 2 cells with one containing a date and the other blank seems to confirm this as it results in the only date.

I tried to incorporate this into the original formula, but I have no idea what I'm doing and I end up getting a #VALUE! error again. Help?

{=MIN(IF(OR(ISBLANK(X23:X95),ISBLANK(P23:P95)),"",X23:X95-P23:P95))}
Improve this question

1 Answer 1

Reset to default
1

OR evaluates ALL of its parameters to ONE RESULT (not just pairwise).

To evaluate it as you need use
IF(ISBLANK(X23:X95)+ISBLANK(P23:P95)...

Tip: to better understand your formula you can evaluate it and see results step by step in Formula - "Evaluate formula"

Edit

Using +,-,/,* operators Excel automatically converts boolean to integers (True -> 1; False -> 0), so addition (+) works similar as OR, and multiplication (*) works as AND.
After, IF converts numbers back to boolean (0 -> False, non-zero -> True).

Improve this answer
2
  • I don't quiet understand the "+" sign, but your solution seems to be working.
    – gavsiu
    Oct 10, 2016 at 8:34
  • please see my update, I've tried to explain it.
    – Máté Juhász
    Oct 10, 2016 at 8:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .