Dec 14 2022 07:59 AM
Dec 14 2022 07:59 AM
Good Day All!
I have created a database that has a Conditional Formatting List in cell (N3) containing a dropdown list of "Open", "Past Due", and "Closed".
I am wanting to eliminate the dropdown list and base the status off of a few cell inputs.
I am aiming to have cell (N3) display "Open" if a value is entered into the 'Date Submitted' cell (F3),
or if my 'Days Remaining' cell (K3)=<0 , display "Past Due" in (N3),
or if the 'Date Closed' cell (O3) contains a value, (N3) displays "Closed"
I hope I explained that ok. If someone could point me in the right direction, I would appreciate it.
As always, thank you.
Dec 14 2022 08:32 AM - edited Dec 14 2022 08:33 AM
If someone could point me in the right direction, I would appreciate it.
I'm going to take you literally on that, and just point you in the right direction, assuming that you mean once so pointed you'd actually appreciate, as well, doing some figuring the rest of it out on your own.
Here's that pointer: Look at the IFS function.
If you do want more help, feel free to come back and ask for it. It would help us help you further if you accompanied that request by posting of your database on OneDrive or GoogleDrive with a link granting us edit access to that spreadsheet.
Dec 14 2022 11:23 AM
Dec 14 2022 12:37 PMSolution
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,
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.
Dec 14 2022 12:52 PM
Dec 14 2022 01:00 PM
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.