If Formula

Jennifer Parins

I have a sheet that has a score (0-5) for an inspection performed. If the score is <=3, the re-inspection date needs to be 2 weeks from the original date of inspection. If the score is <= 4, re-inspect date is 30 days. If the score is <= 5, re-inspect date is 120 days. I an attempting to use multiple if statements but not having success.

=IF([Score@row],<=3,[Inspection Date]+14)

This is the format I am using for the first condition. Please let me know how to create this formula without receiving an error message.

Jennifer Parins

You are awesome and amazing. I was looking at that formula so much that I didn't see such an easy error I was making.

Works PERFECTLY now. A million Thank Yous.

David Tutwiler

I think there is a logic issue in your statement, as <=4 would be inclusive of the number 3. I think you can just say = 4 and = 5.

Also, I think you are looking for a nested if statement. From what I can tell, your formula for <=3 and =4 would be something like:

=IF(Score@row <= 3,[Inspection Date]@row+14, IF(Score@row = 4, [Inspection Date]@row + 30))

Jennifer Parins

This helps a lot. Thank you. You definitely cleared up some questions, but I am still having concerns.

The average score is a decimal number between 0 and 5 so I need to incorporate all values in the following ranges: 0-3, through 4, and through 5.

The other issue I am having is that I continue to receive an Invalid Data Type error. The Score is set as a text/Number and Date of inspection and re-inspection are both date fields.

Any help would be much appreciated.

David Tutwiler

Here is the exact formula I have set on a test sheet where I tried to match your columns as close as I could. I also modified the formula for decimal values. I set this to a column formula and it appears to have entered dates for all fields. If you still get an error after inserting this, can you share a screenshot?

=IF(Score@row <= 3, [Inspection Date]@row + 14, IF(Score@row <= 4, [Inspection Date]@row + 30, IF(Score@row <= 5, [Inspection Date]@row + 120)))

Jennifer Parins

I have the "Next Inspection" column set up as a date field. I see that you have it set up differently and I am wondering if that is the cause of my problems. I continue to receive "UNPARSEABLE" error.

David Tutwiler

It's set up as a date, but once I got the formula working I right-clicked it and set it up as a column formula so it does the same calculation for every row. That is why it looks different.

I think I see your problem. In your last two calculations you have an extra @row in your formula. That will not parse through.

Jennifer Parins

You are awesome and amazing. I was looking at that formula so much that I didn't see such an easy error I was making.

Works PERFECTLY now. A million Thank Yous.

David Tutwiler

Hey, no problem. Glad you got it working!