What formula to use for days remaining before due/start date?
Hello community,
I am currently a little stuck trying to figure out what formula to use for the "days remaining before conference" column. I'd like to have a number there to know how many days are left before the 'start date' of the conference.
Do I need to add a hidden TODAY column or automation? I'm still fairly new to SmartSheets and I a in the process of taking courses...lots to learn. Thanks in advance.
Best Answer

@laura.sandoval You forgot a single quotation mark. A common and fortunately simple mistake to fix! You need a quotation mark between "Consideration" and the parenthesis. I've added it below. It should work now!
=IF(OR(Status@row="In Progress", Status@row="Pending Consideration"), NETDAYS(TODAY(),[Start Date]@row))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast
Answers

Good morning! You don't necessarily need a new hidden column. The NETDAYS formula will calculate the number of days between two dates.
=NETDAYS(TODAY(), [Start Date]@row)
If you specifically need to know the number of workdays, you could use this.
=NETWORKDAYS(TODAY(),[Start Date]@row)
In the example below, we have NETDAYS in the bottom and NETWORKDAYS in the top.
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

This was so much easier thank you! I have one further question that I forgot to mention. What do I add to the formula for it to apply the formula only if the status is "In Progress" or "Pending Consideration"?

No problem, @laura.sandoval . Did you have some alternative you want the cell to read if the status isn't either of those?
Here's the formula nested in an IF statement with a picture below. In the row that says "Not Started," the formula calculates nothing (the FALSE part of the IF statement).
=IF(OR(Status@row = "In Progress", Status@row = "Pending Consideration"), NETWORKDAYS(TODAY(), [Start Date]@row))
If you want the column to have something, you would add it like this formula with a picture below.
=IF(OR(Status@row = "In Progress", Status@row = "Pending Consideration"), NETWORKDAYS(TODAY(), [Start Date]@row), "False Statement Here")
I hope that helps!
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

I'd like it to be blank if the status isn't either of those, it says #UNPARSEABLE :( I may have input it wrong. This is the formula I added:
=IF(OR(Status@row="In Progress", Status@row="Pending Consideration), NETDAYS(TODAY(),[Start Date]@row))
Is NETWORKDAYS for work week days? I'd like it to include the weekend days too so I put NETDAYS instead. What do you think?

@laura.sandoval You forgot a single quotation mark. A common and fortunately simple mistake to fix! You need a quotation mark between "Consideration" and the parenthesis. I've added it below. It should work now!
=IF(OR(Status@row="In Progress", Status@row="Pending Consideration"), NETDAYS(TODAY(),[Start Date]@row))
Amber Eakin, MSLS, M.Ed.
Adult Education Specialist  Process Improvement Enthusiast

It worked thank you!!!
Help Article Resources
Categories
Check out the Formula Handbook template!