Stop Counting NETWORKDAY after Done Box is checked
Hi everyone,
I'm hoping this is easy to read, it is hard to explain.
I am trying to STOP counting days from commencement date of a project once the DONE box is checked.
I have managed to make it work without using NETWORKDAY, using this formula
=IF(Done1 = 1, [Last Updated]1 - [Commencement Date]1, TODAY() - [Commencement Date]1)
but I would rather count only Work Days.
I tried this:
=IF(Done14 = 1, NETWORKDAY([Last Updated]14 - [Commencement Date]14), IF(Done14 = 0, NETWORKDAY(TODAY() - [Commencement Date]14))))
Also tried this:
=IF(Done14 = 1, NETWORKDAY([Last Updated]14 - [Commencement Date]14), NETWORKDAY(TODAY() - [Commencement Date]14))))
and similar variations but it returns an error. Can anybody assist? Thanks in advance!
Comments
-
Why are you using today? couldn't it simply be:
if(done14 = 1,networkdays([Commencement Date]14,[Last Updated]14))
(Untested)
-
Create a "Done on" column (the date it was completed). This will drive the rest of it based off of your "Commencement" column. These are the columns I have referenced in my formulas (obviously you'll need to update the column names to reflect whatever you have):
Count________Commencement________Done on
In the "Count" column, enter:
=IF(ISBLANK(Commencement@row), "", IF(ISDATE([Done On]@row), NETWORKDAYS(Commencement@row, [Done On]@row), IF(ISBLANK([Done On]@row), NETWORKDAYS(Commencement@row, TODAY()))))
The "Commencement" column is a date column reflecting your commencement date.
The "Done on" column is a date column where you will enter the completion date.
If you use this, you can replace your Done checkbox column with the Done on date column.
Help Article Resources
Categories
Check out the Formula Handbook template!