If Formula Based on Dates

[kumara_faith]

Hi,

I have the following input table:

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellpadding="0">
<tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>28-01-19</b></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
<tr><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>No</b></font></td><td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Policy Number</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Received Date</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Completed Date</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Pending Days</b></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Status</b></font></td></tr>
<tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">20-01-19</font></td><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">22-01-19</font></td><td bgcolor="#FFFF00" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">6</font></td><td bgcolor="#FFFF00" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"></font></td></tr>
</table>

I ma trying to input the formula in F3 as follows:

a) If E3 is empty, then use Networkdays formula to show the difference between cell D3 and the current date.
b) If cell E3 is filled with a date, then use Networkdays formula to show the difference between cell D3 and cell E3.
c) If both cell E3 and D3 are empty, then return as blank.

I am using the following formula but it does not seem to work

=IF(COUNTA(E3=0),NETWORKDAYS(D3,B1),NETWORKDAYS(D3,E3))

In cell G3, I am trying to use a formula for the following:

a) If both cell D3 an E3 is empty, then return as blank,
b) if cell D3 is filled and cell E3 is empty, then return as "Pending".
c) If cell D3 and cell E3 is filled with date, then return as "Completed".

I am using the formula below but does not seem to work:

=IF(AND(COUNTA(D3=0,E3=0)),"",IF(AND(COUNTA(D3>=0,E3=0)),"PENDING","COMPLETED"))


Note: Cell D3 is Received Date, E3 is Completed Date, F3 is Pending Days and cell G3 is Status.

Appreciate any assistance for both formula. Thank you.

 

[arthurbr]

Please check the syntax for the COUNTA (and the AND operator) function first. and adapt our formula accordingly

 

[kumara_faith]

Hi,

My apologies. I am not sure I understand. I have checked the formula in Excel and Excel does not prompt that the formula is wrong.

 

[Kerryx]

=if(and(e3="",d3=""),"",if(e3="",networkdays(d3,today()),networkdays(d3,e3)))
=IF(AND(D3="",E3=""),"",IF(E3="","Pending","Completed"))

 

[kumara_faith]

Hi Kerryx,

That worked. Thank you and appreciate your time.