Nested IF function

%3CLINGO-SUB%20id%3D%22lingo-sub-3102231%22%20slang%3D%22en-US%22%3ENested%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3102231%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20nested%20IF%20function.%20Screenshot%20included%20for%20reference.%3C%2FP%3E%3CP%3EThe%20Number%20of%20Days%20column%20needs%20to%20calculate%20the%20number%20of%20days%20in%20the%20date%20span%20in%20columns%20C%20and%20D.%20I%20used%20formula%20D3%20-%20D3%20%2B1%2C%20which%20gives%20me%20what%20I%20need.%20But%2C%20you%20can%20see%20from%20the%20screenshot%20that%20if%20there%20is%20no%20data%20in%20column%20C%20and%20D%20it%20returns%201%2C%20when%20it%20needs%20to%20be%20zero%3CBR%20%2F%3E(or%20preferably%20a%20blank)%20cell.%20I've%20tried%20the%20formula%20%3DIF(SUM(D3-C3%2B1)%26lt%3B1%2CH3%22%20%22)%2C%20but%20it%20is%20returning%20zero%20because%20it%20doesn't%20like%20the%20circular%20reference.%3C%2FP%3E%3CP%3EAny%20advice%20very%20welcome%2C%20thanks!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Anna_C10_0-1643762162982.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F344390i1DAE59578CA1DB6D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Anna_C10_0-1643762162982.png%22%20alt%3D%22Anna_C10_0-1643762162982.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3102231%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3102544%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20IF%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3102544%22%20slang%3D%22en-US%22%3EI%20think%20this%20will%20work%20for%20you%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(AND(C3%26gt%3B0%2C%20D3%26gt%3B0)%2C%20D3-C3%2B1%2C%20%22%22)%3C%2FLINGO-BODY%3E
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, "")