If Formula Based on Dates

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
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.
 
Last edited:

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,201
Office Version
  1. 2010
Please check the syntax for the COUNTA (and the AND operator) function first. and adapt our formula accordingly
 

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
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

Board Regular
Joined
May 6, 2016
Messages
101
=if(and(e3="",d3=""),"",if(e3="",networkdays(d3,today()),networkdays(d3,e3)))
=IF(AND(D3="",E3=""),"",IF(E3="","Pending","Completed"))
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,779
Messages
5,626,831
Members
416,203
Latest member
Rebmit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top