Stop Counting NETWORKDAYS based on date entered on another cell

[cmkarnes]

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

 

[steve the fish]

What does this mean?

"the day a date gets entered in the cell"

 

[cmkarnes]

What does this mean?

"the day a date gets entered in the cell"

Thank you for the question. What I'm saying is that once a user types a date in X4 (Actual EOD Date), I wish to stop the countdown to the Projected EOD date that shows up in Y4, and have a #of days show up that indicates the difference difference between the date in X4 and J4 instead. thx-Chris

 

[steve the fish]

Hi. I may be misunderstanding but this perhaps:

=IF(ISNUMBER(X4),NETWORKDAYS(O4,X4),IF(J4="","",NETWORKDAYS(TODAY(),J4)))

 

[cmkarnes]

Hi again - I've been testing this out - it works perfectly and is doing exactly what I wanted!!!!! I cannot thank you enough - I tried every combo I could think of and just couldn't come up with anything that would work, though it seemed I was somewhat close. You have no idea how much grief you just saved me as my deadline is tomorrow morning!! Take care - Chris