What formula to use for days remaining before due/start date?

laura.sandoval

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.

Amber Eakin

@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

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.

laura.sandoval

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"?

Amber Eakin

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!

laura.sandoval

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?

Amber Eakin

@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))

laura.sandoval

It worked thank you!!!