Nested IF function

New Contributor

Hello everyone,

I am trying to create a nested IF function. Screenshot included for reference.

The Number of Days column needs to calculate the number of days in the date span in columns C and D. I used formula D3 - D3 +1, which gives me what I need. But, you can see from the screenshot that if there is no data in column C and D it returns 1, when it needs to be zero
(or preferably a blank) cell. I've tried the formula =IF(SUM(D3-C3+1)<1,H3" "), but it is returning zero because it doesn't like the circular reference.

Any advice very welcome, thanks!

Anna_C10_0-1643762162982.png

 

3 Replies
I think this will work for you:

=IF(AND(C3>0, D3>0), D3-C3+1, "")

@JMB17 thank you for replying.

The formula you have suggested is bringing up a #VALUE! error

Are the empty cells not truly empty? If they contain the empty string ( "" ), then you would get #Value. You could try this instead:

=IF(AND(C3<>"", D3<>""), D3-C3+1, "")