If Formula Based on Dates

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
951
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:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Please check the syntax for the COUNTA (and the AND operator) function first. and adapt our formula accordingly
 
Upvote 0
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.
 
Upvote 0
=if(and(e3="",d3=""),"",if(e3="",networkdays(d3,today()),networkdays(d3,e3)))
=IF(AND(D3="",E3=""),"",IF(E3="","Pending","Completed"))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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
Back
Top