Time Lapse Calculation

Nancy Skoulphong

I received an #incorrect argument set

Mike Wilday

@Paul, the Networkdays function is embedded but is missing the second date to compare with. 

Paul Newcome

@Mike

 

The second date is actually generated by the IF statement.

 

IF(ISDATE([Complete Date]@row), [Complete Date]@row, TODAY())

 

The above is nested into the NETWORKDAYS function

 

NETWORKDAYS([Due Date]@row, IF(.....))

 

Then that NETWORKDAYS is nested into the 3rd pfortion of the first IF statement

 

=IF(AND(................), do this, otherwise NETWORKDAYS function)

Paul Newcome

That's my fault. I didn't close out the ISDATE function. Add a closing parenthesis there and then remove one from the end of the formula.

 

My apologies.

 

=IF(AND(ISDATE([Complete Date]@row), [Complete Date]@row <= [Due Date]@row), 0, NETWORKDAYS([Due Date]@row, IF(ISDATE([Complete Date]@row), [Complete Date]@row, TODAY())))

 

The above has the ISDATE closed along with the correct amount of closing parenthesis at the end of the formula. That was my fault for not double checking my work. Sorry about that.

Mike Wilday

Doh. I see it now. Dang closing parenthesis. 

Paul Newcome

Haha. I probably would have done the same had I not been the one to write it.

Nancy Skoulphong

Thank you, Paul. No apologies necessary. You've been so helpful, I can't thank you enough.  

If you don't mind me asking one more question. If I want the formula to return zero when the due date is in the future and the complete date is blank, what do I need to add?

Here's the breakdown:

1. Complete date blank and due date is current date or in the future---> time lapse is zero

2. Complete date blank and due date in the past ---> time lapse equal net workday between due date and today's (current) date.

3. Complete date value is equal or less than due date --> time lapse is zero

4. Complete date value is more than due date --> time lapse is equal net workday between due date and complete date.