Sign in to join the conversation:
Are you able to provide a formula that will for for the following: to start counting the number of work days up from a [Start Date] column, but ending it when the job is completed (whether is be an action or another date is reached)?
Much thanks
Hi Jen,
You are looking for a formula to calculate the number of work days between [Start Date] and Today, until the job is complete? Once it's complete, the count would stay fixed as the difference between the start and complete date?
What's the indicator to show that the job is complete? Is there a status column or something similar?
Assuming that I understand correctly, and there is a status, this should work:
=IF(Status1 = "Complete", (NETWORKDAYS([Start Date]1, [Complete Date]1)), (NETWORKDAYS([Start Date]1, TODAY())))
Dave
David,
You have a few extra parenthesis tucked away in there...
=IF(Status1 = "Complete", NETWORKDAYS([Start Date]1, [Complete Date]1), NETWORKDAYS([Start Date]1, TODAY()))
Ah, yes I have been tinkering with it for a while and still get #UNPARSEABLE error. I appreciate your catch of the missing bracket, unfortunately I still have the error message. Any Ideas? Thank you
=IF(Status1 = "Complete", NETWORKDAYS([Kick Off Date]1, [Projected Live Date]1), NETWORKDAYS([Kick Off Date]1, TODAY()))
Yes, that is what I am trying to accomplish thank you for this formula, still trying to work out the error #unparseable
Your syntax is correct. Have you checked the spelling and spaces in your column names? Are all columns set as Date type columns?
Thanks for verifying the syntax. I went back and copied/pasted the column names to ensure they are correct. "Kick Off Date" and "Projected Live Date" are "Current type: Date" and the column that has this formula is "Current type: Text/number "I am still getting an error. I will upload a screenshot in hopes something jumps out that might be causing the error, and thanks again for your time
I don't see your Status Column, but everything else looks like it should be working...
Hmm this might be the key - so I do not have a "Status Column" - perhaps this is what I am missing? I apologize I am new at using SS :S
Oh! My "Total Days" column should be called "Status"?
That would be the issue. By using Status1 in your formula, Smartsheet is looking for a column actually named "Status". What are you using as a status column to determine whether or not it is complete?
Ah... I see "Total Days" is the name of the column - so I have now changed the column with the formulas applied to "Status", and it turned blue which is great because it means something is happening, however now I have the "# INVALID DATA TYPE" error So perhaps I should change the column "Current type: Text/number" to a different type? - sorry for the trouble.
You can leave the column that the formula is in as "Total Days". What I need to know is how you are determining something is "Complete".
I see, in my mind I thought the projected live date would determine it is complete. However sometimes the projected live date is surpassed and so I should use the "Live Date" Column to determine it is complete (this is only determined when product actually goes live so wont be filled in until then). Is that possible, or would it be better to create a column called "Status" with a different function other than date, like a drop down?
Ah. Ok. I'm starting to understand a little better how things are working. So if the Live Date column has a date in it, you want the NETWORKDAYS between it and the Kickoff Date? Otherwise you want the NETWORKDAYS beween Kickcoff and Projected Live?
Does that sound about right?
Yes that is correct. Though that sounds complicated - can it be done? My apologies for creating confusion.
Hi there, I've created a sheet to where my team is tracking information received. In one column, we are logging the date and time information came in (ie: November 21, 2025 8:30 AM). I would like to add a checkbox column, with a formula specifying that the box be checked if the logged time is AFTER 8:30 AM, and left…
I have a list of properties that I'm keeping track of. I have each building divided into suites (children) and each suite has a cell with its square footage. The total square footage rolls up to the building name. I also have a drop down column that shows if the suite is occupied or vacant. I need a to calculate the…
Hi! I'm fairly new to SmartSheets but have been trying a number of things that havent yet worked. I want to be able to show for each parent row, the number of child rows completed as a %. So for the parent row Process Overview there are 7 tasks and 6 tasks are complete so I want it to show as 90%. Can anyone help me?
