• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stop counting down NETWORKDAYS function based on date in another cell

C

cmkarnes

Member
Hello, I am stumped - have seen some info on this forum related to the above, but cannot piece it together to have it work for me. I just can't get the formulas to work. Here is the scenario - I have a worksheet in Excel 2010 which tracks each step of the hiring process along with a proposed Enter On Duty (EOD) date, and an actual EOD date.

J4 - Populated w/Projected EOD Date
O4 - User types in date paperwork submitted to HR to start hiring procedures
X4 - User types in the actual EOD date
Y4 - Countdown to Projected EOD Date

1) When user inserts date in O4, I've got a formula plugged in that automatically populates a future date based on a 100-day timeline in J4. That works fine. Formula is: =IF(ISBLANK(O4),"",WORKDAY(O4,100))
2) In Y4, I have a formula that starts a countdown and indicates how many days left until the Projected EOD Date is reached - that seems to be working fine - =IF(J4="","",NETWORKDAYS(TODAY(),J4)). I included in these functions for cells to remain blank until ref data is entered.

3) Here's the issue: When the user types in the actual EOD date in X4, I want Y4 to stop counting the days, and calculate the actual length of time it took to have the person come on duty based on the date the user enters in X4. Because it is possible that a user may forget to enter the EOD date until a later point in time, it's got to be able to base the resulting # on the actual date of the EOD vs. the day a date gets entered in the cell.

I'm just lost on how to get XY to do what I need. I'm desperate for an answer. Also, I realize i have formulas for both WORKDAY and NETWORKDAYS above, but it seemed from my research they were each capable of only certain things - is that correct? Thank you so much - Chris
 
Hi:

I am not sure whether I understood your problem correctly , do a simple If condition do the trick or the issue more complicated than I reckon?
 
@cmkarnes,

If I understand correctly, you simply need a nested IF such as:
=IF(X4="",IF(J4="","",NETWORKDAYS(TODAY(),J4)),NETWORKDAYS(O4,X4))

I hope that helps.

Regards,
Ken
 
I apologize for the lateness of this - management kept changing the sheet around as to what they wanted. So here's the scenario right now (some cell refs may be different due to adding/deleting columns per their requests) and the current formula I've got in AA4:

=NETWORKDAYS(TODAY(),K4)

AA4 shows networkdays countdown to the due date in K4 - if a date gets input by the user in only X4, the count should stop but I'd like the networkday number to remain in AA4 for reference purposes. If only cell Y4 gets populated (this is text-it is a data validation dropdown list), I want AA4 to again stop counting, and have the networkday number again stay in AA4 for reference purposes. If they both get populated, the same scenario. I hope this makes sense. I am unable to download the sheet due to the sensitive nature of the work. thank you so much. Chris
 
You must log in or register to reply here.
Back
Top