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

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

Find more posts tagged with

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…