Forum Discussion
Cell Populating Based of Other Cell Conditions
Here you go. I've created what I think is your desired formula. I had to do a little reading between the lines of your description to come up with what I think are the several column headings. All you need to do is enter dates in the two yellow columns.
The formula in column J calculates the days since open
Column K figures how many days left until the boundary of "Past Due" is reached
Column N contains the formula you were trying to create. I suspect your difficulty was that IFS starts calculating from the left and STOPS once it reaches a condition that is met. That's why the new formula,
=IFS(F3="","",O3>F3,"Closed",AND(F3>0,K3>=0),"Open",J3>90,"Past Due")
gives as the first meaningful condition the entry of a date into the "Closed" column, where that date is greater than the date "Opened" and then, if it's not Closed, proceeds to the other conditions.
Rather than hard-code the 90day limit, I made it a separate field (B5), so that you could change your policy to, say, 60 days simply by changing that one number; the formulas would adjust automatically because they refer to that B5 field.
Here you go. I've created what I think is your desired formula. I had to do a little reading between the lines of your description to come up with what I think are the several column headings. All you need to do is enter dates in the two yellow columns.
The formula in column J calculates the days since open
Column K figures how many days left until the boundary of "Past Due" is reached
Column N contains the formula you were trying to create. I suspect your difficulty was that IFS starts calculating from the left and STOPS once it reaches a condition that is met. That's why the new formula,
=IFS(F3="","",O3>F3,"Closed",AND(F3>0,K3>=0),"Open",J3>90,"Past Due")
gives as the first meaningful condition the entry of a date into the "Closed" column, where that date is greater than the date "Opened" and then, if it's not Closed, proceeds to the other conditions.
Rather than hard-code the 90day limit, I made it a separate field (B5), so that you could change your policy to, say, 60 days simply by changing that one number; the formulas would adjust automatically because they refer to that B5 field.
Reading it aloud helps too, I am learning. Thank you so much for taking the time to help and teaching as well.
All the Best,
Travis
You were definitely in the ballpark. That website where the IFS function was described for you is a good one for your continued learning. There are also many very helpful YouTube videos that I'd commend to you.
Reading it aloud helps too, I am learning.
Good insight on your part. Yes. And describing out loud in plain English when trying to develop the formula in the first place.