Excel formula to subtract dates

问题:

I have a formula where I can subtract dates from 3 columns based on a few conditions:

=IF(OR(COUNT(D15:F15)=3; AND(ISBLANK(F15); COUNT(D15:E15)=2)); E15-D15; IF(AND(ISBLANK(E15); COUNT(D15:F15)=2); F15-D15; 0))

The problem is, when the date from the column D is blank, I get a huge number on my result, because excel interprets blank as a random date.

I need to adapt this formula to make it return 0 whenever the D column has a 0 or blank value.

Can anyone help me with that please?

回答1:

Firstly, comparing a blank cell to 0 will return true, so the tests are easiler than you think.

Secondly, here's a simpler version of the formula

Thirdly, the answer this this Q: If(D15=0,0, ...)

Here's the improved formula, for row 15

=IF(D15=0,0,IF(E15=0,IF(F15=0,0,F15-D15),E15-D15))