Calculating Difference Between 2 Dates when Either/Or May be Blank

[veraloopy]

I've been banging my head with this for a few days..

I have 2 dates, one in column J and the other in L, I need to calculate the difference between the 2 dates, however where 1 of them may be blank (this could be either / or), then I need to return "N/A" to the cell.

I've tried this, but it only works when cell L15 does not have a date, If cell J15 doesn't have a date, it returns 41311 (based on the date in L15 and J15 being blank

=IF(L15>0,L15-J15, "N/A")

Then I tried this:

=IF(L2<>"",L2-J2,"N/A") OR =IF(J2<>"",L2-J2,"N/A")

But it returns #Value

any ideas?

 

[namliam]

=IF(or(J2<>"",L2<>""),L2-J2,"N/A")

Not that that formula will do what you want it too, but that is the way you do an OR

 

[NBVC]

I think that should be AND() because you want both to be non-blank.

=IF(AND(J2<>"",L2<>""),L2-J2,"N/A")

or you can use something like..

=IF(COUNTBLANK(J2,L2)>0,"N/A",L2-J2)

 

[veraloopy]

Thanks both

This worked for me:

=IF(AND(J2<>"",L2<>""),L2-J2,"N/A")

Thanks NVBC