Help Creating Conditional Formula

[Jessietm]

I have, in column A, the species of a pet (Either Cat or Dog). Column B is the client's name. In column C is the date that the pet was last seen by me. Column D contains the formula within each cell for the number of days since the pet's last visit. 

So:

Dog - John Doe - 3/1/2024 - =TODAY()-C2

Cat - Jane Doe - 3/1/2024 - =TODAY()-C3

I would like to figure out how to change the cell color of column D (days since last appointment) based on the species so I know when to send booking reminders to the clients. Dogs should be every 21 days or more and Cats every 35 or more. I would like column D (days since last appointment) to gray out after 90 days regardless of the species. I would like this to apply to the whole sheet and work for when I insert a new row so I don't have to keep updating the rules. There are currently 50 clients, but this is a growing business and I add someone new every couple of days. Also, I would like it to be Blue if the appointment is upcoming (this would be a negative number. 

I hope this makes sense!

Thanks!

[#2]

@Jessietm 

It totally makes sense. I've attached a spreadsheet that does what you've asked.

This all uses the conditional formatting ability of Excel. Here's a reference that describes that if you want to refine things further. And here's a picture of the Conditional Formatting dialog box as it currently exists in the attached spreadsheet.

I've also made the range that includes the data into an Excel table. Done this way, the rules will apply to each new row added (Add any new row immediately below the current last row; no blank rows)

[#3]

@mathetes 

Thank you so much!

The only other question I had was that some of the D cells aren't looking like I need them to. I have the rules ordered and input as shown, so I'm confused, haha. 

[#4]

@mathetes

[#5]

@Jessietm 

I'm not sure exactly what you're seeing that's not as you want (I'm not patient enough to go through each line on my own, when you could just tell me and others going through this thread).

I will say that I've always found Conditional Formatting to be very much a matter of trial and error, especially in the early learning stages. I can see that you've made some changes to the rules (which is fien).

What I notice, and perhaps this is the source of the "not working": your formula as it applies to the "Cat" species is specifying the wrong columns (I've copied YOUR image below)

You've written : =AND(C7="Cat",F7>=35)  but those cell references should be the same as the ones for "Dog" other than specifying the different species and different number.

So it should read =AND(A2="Cat",D2>=35)

[#6]

Ah! I fixed the typo, but the first entry and the third from the bottom in the second image are still not conforming to those rules. Both are dogs with values over 21.

I ended up just using the one you sent and manually re-entered everything. My sheet was in the form of a table and I wonder if that was part of the issue.

[#7]

@Jessietm 

Ah! I fixed the typo, but the first entry and the third from the bottom in the second image are still not conforming to those rules. Both are dogs with values over 21.

I can't see your actual rules in those rows. My guess is that somehow they're different.

OR, another possibility, depending on how you enter the word "Dog" in column A is that you might have entered "Dog " or some such. Excel is very literal. Blank spaces don't confuse you and me, but they do not look the same to Excel.

If you're not using Data Validation to enter the Dog or Cat value in column A, I highly recommend implementing that.

If you remain stymied, may I suggest that you remove the actual names of the owners and post a copy of the actual spreadsheet without those names. I'll take a look at it and see if I can diagnose and treat. I could then just send you the prescription. (Sorry; couldn't resist.)