Count days from Start Date enetered and stop count when Finish Date is entered

Tonna Heyneke

Good day,

If someone could please assist.

I'm looking for a formula that counts my "wayleave" days starting from since the "wayleaves have been submitted, and stops the count as soon as the Actual Finish date is entered for the "Wayleave".

Ps. Should also be "NETWORKDAYS".

Example Columns:

Column "ROW 1 Actual Start":  Date entered when the wayleave is submitted.

Column "ROW 1 Actual Finish":  Date entered when the wayleave is received.

Column "Time Lapsed": Calculation count days from "ROW 1 Actual Start" entered up to (Today), and stops the count when "ROW 1 Actual Finish" date is entered.

 

Thanks

Tonna

 

Andrée Starå

Hi Tonna,

Try something like this.

=IFERROR(NETWORKDAYS([Actual Start]@row; [Actual Finish]@row); "")

The same version but with the below changes for your and others convenience.

=IFERROR(NETWORKDAYS([Actual Start]@row, [Actual Finish]@row), "")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

You could also try...

 

=NETWORKDAYS([Actual Start]@row, IF(ISDATE([Actual Finish]@row), [Actual Finish]@row, TODAY()))

Andrée Starå

Paul,

Why would you use this instead of the other? Or no specific reason, just another option.

Tonna Heyneke

Hi Andree

Thanks for the feedback, but unfortunately that's not the formula i was looking for.

Let me explain the situation.

This task in the project is called wayleave submission. Its a request to the municipality to work in a public area.

I would like to calculate the days from when I submitted the wayleave (Start date) to the council until the council approves the wayleave "Finish Date". The calculation will happen in the "Time Lapsed" Column this calculation should only count from Date submitted to the current day(Today) and stop the count when a "Finish Date" is entered.

Thanks for the assistance.

 

 

Paul Newcome

I went with this particular solution because the poster is looking to calculate days from start to today until the finish date is populated. Then they want to calculate from start to finish.

Thus the reason for nesting the IF into the second portion of the NETWORKDAYS function to generate the finish date or today's date based on whether or not the finish date is blank.

Andrée Starå

Happy to help!

I saw that Paul answered already!

I read it wrong. Sorry about that!

Let me know if I can help with anything else!

Best,

Andrée

Andrée Starå

Make sense!

I read it wrong!

Sorry about that!

Paul Newcome

You've covered for me plenty of times after I've read something wrong, so no worries. Haha 

Tonna Heyneke

Hi,

OK, i got the basic formula correct but now i would like to add count only to network days.

The basic formula that's working is:

=IF([ROW Submission 1 (Utilities & Operators) AF]1 = ""; TODAY() - [ROW Submission 1 (Utilities & Operators) AS]1; [ROW Submission 1 (Utilities & Operators) AF]1 - [ROW Submission 1 (Utilities & Operators) AS]1)

Thanks

 

Paul Newcome

Give this a go...

 

=NETWORKDAYS([ROW Submission 1 (Utilities & Operators) AS]@row; IF(ISDATE([ROW Submission 1 (Utilities & Operators) AF]@row); [ROW Submission 1 (Utilities & Operators) AF]@row; TODAY()))

Andrée Starå

It's inevitable! It happens to us all, eventually!