If statement return value error

[umarraza]

Hi

I have drawn up a draft table of the spreadsheet I am currently working on. The formula in D3 works perfectly fine, it gives me a value of the number of days taken to resolve the incident. However, until the incident is open and there is no date in Cell B3 I need to check how long has the incident been open for in C3. The formula below gives me just that but I want it to be blank if the incident has been resolved. In Cell C4 I have shown the result of the formula if I drag the formula down.

I would be grateful if someone could help me with this please.

Thank you in advance.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=Today()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Date Incident Sent[/TD]
[TD]Date Resolved[/TD]
[TD]No. Days Since Incident Sent[/TD]
[TD]No. Days to Resolve Incident[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10/07/15[/TD]
[TD]18/07/15[/TD]
[TD]=IF(ISBLANK(D3)="","",DATEDIF(A3,A$1,"d"))[/TD]
[TD]=IF(ISBLANK(B3), " ", DATEDIF(A3,B3,"d"))[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]42206[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

The 42206 is a result of the calculation between A4 en A1 in which A4 is blank. When calculating dates Excel assumes a blank cell to be equal to 01-01-1900.

The error value is coming from the logical check you've entered. The syntaxis should be either =IF(ISBLANK(D3),"",DATEDIF(A3,A$1,"d")) or =IF(D3="","",DATEDIF(A3,A$1,"d"))

try this:

	A	B	C	D
Date Incident Sent	Date Resolved	No. Days Since Incident Sent	No. Days to Resolve Incident

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]21-7-2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10-7-2015[/TD]
[TD="align: right"]18-7-2015[/TD]

[TD="align: right"]8[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=IF(ISBLANK(B3),DATEDIF(A3,A$1,"d"),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(ISBLANK(B3),"",DATEDIF(A3,B3,"d"))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

Try this in C3

=IF(AND(A3<>"",B3=""),DATEDIF(A3,A$1,"d"),"")

 

[umarraza]

Hi,

The 42206 is a result of the calculation between A4 en A1 in which A4 is blank. When calculating dates Excel assumes a blank cell to be equal to 01-01-1900.

The error value is coming from the logical check you've entered. The syntaxis should be either =IF(ISBLANK(D3),"",DATEDIF(A3,A$1,"d")) or =IF(D3="","",DATEDIF(A3,A$1,"d"))

try this:

	A	B	C	D
Date Incident Sent	Date Resolved	No. Days Since Incident Sent	No. Days to Resolve Incident

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]21-7-2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10-7-2015[/TD]
[TD="align: right"]18-7-2015[/TD]

[TD="align: right"]8[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C3[/TH]
[TD="align: left"]=IF(ISBLANK(B3),DATEDIF(A3,A$1,"d"),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=IF(ISBLANK(B3),"",DATEDIF(A3,B3,"d"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hey,

Thank you for your reply. This seems to work but how can I get rid of the 42206 where the dates are not available?

Thanks,

 

[umarraza]

Perfectt!!!!!!!

Thank you so so so so much. This has worked perfectly!

 

[umarraza]

Try this in C3

=IF(AND(A3<>"",B3=""),DATEDIF(A3,A$1,"d"),"")

Perfectt!!!!!!!

Thank you so so so so much. This has worked perfectly