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))}
I don't quiet understand the "+" sign, but your solution seems to be working. – gavsiu – 2016-10-10T08:34:02.153
please see my update, I've tried to explain it. – Máté Juhász – 2016-10-10T08:40:35.290