Stringing Logical Arguments

Shawn R

Hello,

 

I have been struggling with this formula for a few days now and can only get bits and pieces of it to work. So I am hoping I can get some help here. Essentially I am trying to create a formula that includes 5 fields and depending on what is in them, give me three possible outcomes. The fields I have are:

 

1. Approved?

2.First Date Absent

3.Return Date

4.Calendar Start Date

5.Calendar End Date

 

So what I am trying to accomplish is create a formula to first check if the approved field is checked, I want nothing to happen if that field is not checked. Then I want the formula to check if the First Date Absent is less than the Return Date. If that is true then I want the sheet to give me the Return Date -1. If that statement isn't true then I want it to go to the second part and check if the First Date Absent is equal to the Return date. If that is true then I want the sheet to just give me the Return Date. If no conditions are met, I want it to return blank.

 

Here is an example of one of the many forumals I have tried.

 

=IF(Approved?141 = 1, AND([First Date Absent]141 < [Return Date]141, [Return Date]141 - 1, OR(IF(Approved?141 = 1, AND([First Date Absent]141 = [Return Date]141, [Return Date]141, " ")))))

 

I've tried to simplify this code to just two varibles, and a multitude of other variations but I cannot seem to get this to work when I add more than one logical function. Is what I am trying to accomplish even possible within Smartsheet?

Greg Gates

You can definitely do this! I think the problem you might have is that you're trying to use an OR-function, but this scenario doesn't require it. The function will involve nested if-statements (https://www.smartsheet.com/blog/support-tip-build-nested-IF), and will probably look something like this:

 

=IF(Approved?141 = 0, "", IF([First Date Absent]141 < [Return Date]141, [Return Date] - 1, IF([First Date Absent]141 = [Return Date]141, [Return Date]141, "")))

 

I hope that helps! Let me know if you have any questions or if it doesn't work for you.

Shawn R

Alright,

 

I think I understand the logic behind the forumal now. But now instead of invalid data type error I get unparseable. Here is the formula I am currently using.

 

=IF(Approved?82 = 1,"" IF([First Date Absent]82 < [Return Date]82, [Return Date]82 - 1,"" IF([First Date Absent]82 = [Return Date]82, [Return Date]82, "")))

 

Shawn R

Scratch that, I was able to get it to work by using this:

 

=IF(Approved?82 = 1, IF([First Date Absent]82 < [Return Date]82, [Return Date]82 - 1, IF([First Date Absent]82 = [Return Date]82, [Return Date]82, "")))

 

Thanks for the help! All that C# programming makes me want to use irrelevant functions!