Skip to main content

Hello Airtable Community!



We have a situation where I am trying to create a “pass” of “fail” grade on wether or not an estimate was submitted by a certain date and time.



Our table is structured as follows:



There is a field called “24 Hour Deadline for Estimate” that is the deadline we are holding our estimator to as far as his deadline to submit to our customers.



There are a few other fields that show the two different estimates and their correspond totals, as well as the roll up of the two types for a grand total. We also have two lookup fields that are filled out when we actually submit each type - “(MIT) Date Submitted (LU” and ““CON” Date Submitted (LU)” are these two fields. When our estimator submits one or the other on another table, he puts in the date. That is what these two fields are looking up.



The ultimate goal of the formula that I am stuck on is to grade as “Pass” or “Fail” if we submit both of the estimates before the deadline. This field is named “Grade on 24 Hour Submittal”.



Here is what I want to see happen:



If both of the two types of estimates (MIT Estimate and CON Estimate) are submitted before the “24 Hour Deadline for Estimate”, then the grade in the “Grade on 24 Hour Submittal” would be “Pass”. If one or the other estimates is NOT submitted by the “24 Hour Deadline for Estimate” then the grade would be “Fail”. If we are before the deadline, regardless of if only one of the two are submitted, the grade field is left blank - thereby not flagging a possible “Fail” before the deadline.



I have attached a few screens shots that show the fields as mentioned above, as well as my formula field - which I know doesn’t get it done, but shows were I and stuck,



Bottom line, my issue is I that am out of my league on understanding how to write this kind of conditional logic Formula. Here is what I used:



IF({(MIT) Date Submitted (LU)}=BLANK(),"", IF({(CON) Date Submitted (LU)}=BLANK(),"",IF({(MIT) Date Submitted (LU)}>{24 Hour Deadline for Estimate Submittal},“Fail”,IF({(CON) Date Submitted (LU)}>{24 Hour Deadline for Estimate Submittal}))))



Does anyone have any suggestions for me?



Thanks in advance!







One thing that could be simplified is how you’re testing for dates in both date fields. An easier way to do it would be to use the AND() function and the shortcut of a filled field being equivalent to True. This will avoid the need for checking each field separately and comparing against the BLANK() function. Similarly, you could test for either field being past the deadline using the OR() function.



With those changes applied, the formula looks like this:



IF(


    AND(


        {(MIT) Date Submitted (LU)},


        {(CON) Date Submitted (LU)}


    ), IF(


        OR(


            {(MIT) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal},


            {(CON) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal}


        ), "Fail")


    )


)


For a deeper look at those logical functions, here’s a video that might help:



One thing that could be simplified is how you’re testing for dates in both date fields. An easier way to do it would be to use the AND() function and the shortcut of a filled field being equivalent to True. This will avoid the need for checking each field separately and comparing against the BLANK() function. Similarly, you could test for either field being past the deadline using the OR() function.



With those changes applied, the formula looks like this:



IF(


    AND(


        {(MIT) Date Submitted (LU)},


        {(CON) Date Submitted (LU)}


    ), IF(


        OR(


            {(MIT) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal},


            {(CON) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal}


        ), "Fail")


    )


)


For a deeper look at those logical functions, here’s a video that might help:






Thanks for your response! I tried the formula and for some reason it is still leaving the grade field blank. Also, would I need to add in the “Pass” option at the end of the formula? I tried that before replying here and it didn’t create an error, but the dates in the fields currently should be yielding a “Fail” grade as opposed to blank as it is currently. Any thoughts?

Thanks for your response! I tried the formula and for some reason it is still leaving the grade field blank. Also, would I need to add in the “Pass” option at the end of the formula? I tried that before replying here and it didn’t create an error, but the dates in the fields currently should be yielding a “Fail” grade as opposed to blank as it is currently. Any thoughts?


Sorry. I blocked out that formula without testing it. You’re correct, the “Pass” result is missing.



I also overlooked the fact that the two fields that you’re using for date comparison are lookup fields. Long story short, those most often return arrays, not individual values, even when you’re only looking up from a single linked record. I recommend changing them both to rollup fields using the MAX(values) aggregation formula. That will give you actual dates to compare against.



With that done, this should work:



IF(


    AND(


        {(MIT) Date Submitted (LU)},


        {(CON) Date Submitted (LU)}


    ), IF(


        OR(


            {(MIT) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal},


            {(CON) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal}


        ), "Fail", "Pass"


    )


)

Sorry. I blocked out that formula without testing it. You’re correct, the “Pass” result is missing.



I also overlooked the fact that the two fields that you’re using for date comparison are lookup fields. Long story short, those most often return arrays, not individual values, even when you’re only looking up from a single linked record. I recommend changing them both to rollup fields using the MAX(values) aggregation formula. That will give you actual dates to compare against.



With that done, this should work:



IF(


    AND(


        {(MIT) Date Submitted (LU)},


        {(CON) Date Submitted (LU)}


    ), IF(


        OR(


            {(MIT) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal},


            {(CON) Date Submitted (LU)} > {24 Hour Deadline for Estimate Submittal}


        ), "Fail", "Pass"


    )


)

Ok - I changed the Look Ups to the Rollups as suggested - for some reason the formula still isn’t working…

Ok - I changed the Look Ups to the Rollups as suggested - for some reason the formula still isn’t working…


I just tested the formula on my side, and it works fine. Would you please share a screenshot showing the formula field settings and some examples of how it’s not working in your table?



I also realized that I put one too many closing parentheses in that second version of the formula. That was written on the fly, before I began testing. I just corrected the formula above.

I just tested the formula on my side, and it works fine. Would you please share a screenshot showing the formula field settings and some examples of how it’s not working in your table?



I also realized that I put one too many closing parentheses in that second version of the formula. That was written on the fly, before I began testing. I just corrected the formula above.


Not sure what I may have done for it to not work, but this worked perfectly when I entered in the new formula.



Thank you so much for your needed help!!

Reply


Terms & Conditions