Stop Counting NETWORKDAY after Done Box is checked

edited 12/09/19 in Formulas and Functions

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!



  • L_123
    L_123 ✭✭✭✭✭✭

    Why are you using today? couldn't it simply be:


    if(done14 = 1,networkdays([Commencement Date]14,[Last Updated]14))



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/01/18

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!