Formula Help

Jessica Howell

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?

bisaacs

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!

Jessica Howell

@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

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?

Jessica Howell

@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

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

Jessica Howell

@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

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.