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

Options

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.


Tags:

Best Answer

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    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

  • laura.sandoval
    Options

    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
    Amber Eakin ✭✭✭✭✭✭
    Options

    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

  • laura.sandoval
    Options

    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
    Amber Eakin ✭✭✭✭✭✭
    Answer ✓
    Options

    @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

  • laura.sandoval
    Options

    It worked thank you!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!