Formula Help

Options

Hi All,

I am trying to create a formula within a dropdown column that automatically marks a column as declined, if it meets the following criteria:

Days since request = < =7

Type of request = "Day Switch"

I have tried a few variations of the following and nothing seems to work,

=IF([Days Since Submission]@row, <=7, [Type of Absence Request (FT)]@row, "Day Switch", "Declined"))

I was also thinking i could add a count ifs column beside it and if it counts 1, then declined. thoughts or suggestions?

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Jessica Howell,

    So you want the formula you provided to be in the dropdown? If that's the case, there's a couple fixes you'll want to make to the formula:

    • You'll want to remove the commas so that there's only 2 in the whole formula, one to show the end of the logic statement and another to separate the true and false results
    • You appear to have an extra parenthesis at the end of the formula, there should only be one set of parenthesis required in the formula

    IF statement structures follow this: =IF(logic statement, value_if_true, value_if_false). So, your formula should look something like this:

    =IF([Days since submission]@row <= 7, "Day Switch", "Declined")

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Jessica Howell
    Options

    @bisaacs

    Thanks for the suggestion. Unfortunately this formula resulted in "day switch" instead of declined.

    I need it to decline the request based on the Type of Request column saying "Day Switch" as long as the days since request is < =7

    But i only want it to do this given it meets the criteria above. otherwise id like it to remain as submitted, or blank for approval. I would post an image, but there are so many columns with private information that i cant share

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Jessica Howell,

    Could you provide the layout of the columns, and what column the formula is supposed to be in? Here's kind of what I have at the moment:

    I did switch around the logic so that if Days since submission is blank the column says "Submitted". The formula will first check to see if it's been more than 7 days, then it'll check if it's blank, THEN it'll check if it's been at least a day since submission.

    Is there other criteria that would determine if the dropdown is Submitted vs blank?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Jessica Howell
    Options

    @bisaacs the other options in the dropdown are Approved and Cancelled, i believe submitted is automatic after the form has been submitted

    Criteria other than day switch via a dropdown is,

    Vacation (Change)
    In Lieu (Stat)
    Time Off Without Pay (5 days or less)
    Leave of Absence (6 days or more)
    Shift Switch


    Parental/ Maternity Leave

    If one of these is selected id like it to be left up to a managers approval, so Submitted status or blank

    I hope that makes sense!

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    @Jessica Howell got it!

    I realized I misunderstood what your original request was, but now that I understand it, I think this is what you're looking for:

    =IF(AND([Days since submission]@row <= 7, [Type of Absence Request]@row = "Day Switch"), "Declined", "Submitted")

    I don't think you'd be able to convert this to a column formula though as doing so wouldn't allow managers to change the approval status. You'd have to copy/paste it into each row unfortunately.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Jessica Howell
    Options

    @bisaacs Thank you! that worked

    For the most part the formula is carrying down with each form submitted, but eventually it stops. Is there a way i can modify the formula so that when i pull it down it doesn't change the perimeters of the other cells- meaning if they already have a response?

    For example, someone has submitted a request for "time off" and a manager has already gone in and approved it. Then when i pull the formula down into that column it changes it back to submitted- removing the approval.

    Is there a way to stop it from doing that?

    Thanks!

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @Jessica Howell,

    Unfortunately you would have to skip over the rows that have already been decisioned when copy/pasting. I'm not aware of a way to have a formula be ignored where data has already been entered. The only way to really overwrite the formula is to enter data into the cell that the formula is already in.

    The other option you could look at is conditional formatting. It doesn't actually change the cell data itself, but you could have rows be highlighted a specific color where it would match the conditions listed in formula I previously provided.

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!