Count Formula

Danfenti

Is there a formula I could use to populate a number into a cell that is a counter of workdays since job has started, and it starts counting once a check box is checked off and stops counting when another check box is checked? this way we can keep an eye on our time frame of a job from start to finish

Chris McKay

Hi Danfenti,

Because of the way Smartsheet calculates dates, this is what is happening under the hood in your sheet:

• Each row has a Created and Modified value. This is system calculated (i.e. you have no control over the values).
• The Modified value is the difficult one, as this will change any time the row is modified in any way.
• This includes using functions like TODAY() to work out how many workdays have elapsed since you checked your start box.
• Your sheet will keep updating the Modified date (each time you load/save the sheet) if you calculate workdays in this way.
• This also means that using the Modified date to calculate both Start and Finish will cause issues, as there is no way to capture a historical value in Smartsheet. You can reference a cell to get the value, but you cannot take a value from the Modified date cell and store it.

With these "challenges" in mind, there is a workaround of sorts but it requires some flexibility on your part. I'd try this:

1. In addition to other columns in your sheet (e.g. Job Name), create your 2 checkbox columns and your counter column (let's call them Start Job, Finish Job & Workdays)
2. Lock the Workdays column
3. Add in 2 System columns (Created & Modified)
4. In your Workdays column enter this formula:
   
   =IFERROR(IF(AND([Finish Job]@row = 0, [Start Job]@row), NETWORKDAYS(Created@row, TODAY()), IF(AND([Finish Job]@row, [Start Job]@row), NETWORKDAYS(Created@row, Modified@row), "")),"")
5. Create a new Lock a Row rule and base it on the criteria When Finish Job is Checked

What this will do is calculate the number of workdays between the Created date and TODAY() when the Start Job checkbox is ticked and when the Finish Job checkbox is ticked it will calculate the number of workdays between the Created date and the Modified date and then lock the row. This will essentially freeze the row (including the Modified value).

This also assumes that the Created date is the same date as the Start Job date. As you cannot share the Modified Date between Start Job and Finish Job, (because the Start Job date will keep shifting if you base it on Modified) this is your only option.

Hope this helps.

Kind regards,

Chris McKay