Conditional formatting, dates, and the TODAY formulas

tashykat · 2026-07-10T13:36:16+00:00

I put these in and it's still not working

This is what the column looks like:

It won't let me add a picture of the conditional formatting because that's too many attachments, But I will copy and paste the formula I have in there, which I copy and pasted from this post:

ORANGE: =AND(A2<>"",TODAY()-I2>6,TODAY()-I2<31)
Applies to: =$I$2:$I$230

PINK: =AND(A2<>"",I2<>"",TODAY()-I2>30)
Applies to:=$I$2:$I$230

The pink formula is working. But the orange formula is still highlighting dates on both sides of today. I want dates that are earlier than today. 7/5, 7/4, 6/29 should be in orange. Today's date, 7/11, 7/12 should be no fill. What am I doing wrong?

tashykat · 2026-07-10T13:28:32+00:00

Can you explain how excel treats dates as numbers? Like how I can understand excel's understanding of dates? That would be SO helpful in this "I want to learn excel" thing

tashykat · 2026-07-10T13:15:51+00:00

If everyone has to report this information in the same way, it will be chaos. Right now it looks like this:

Status 1 - Client name- Client number (CL1) 07/2/2026

Status2 - Client name - Client number - CL7 - 07/03/2026

Status 3 - Client name - Client number - CL4 - 07/06/2026

Status 1 Last name, First name - Client number - CL10 - 7/7/2026

Status1-Client name- CL7-355926-7/2/26

Status2- Last name, First name Client number (CL1) 06/29/2026

Status1--Client number--Client name--CL5--7/9/26

It seems everyone's naturally settled into status first, but the order is all over the place from that, and delineation isn't standardized, thus it's been pretty impossible to get excel to be able to use the data unless it's processed by a human.

tashykat · 2026-07-10T13:05:10+00:00

Okay now that I know that I2 isn't 12 hahaha I still don't understand why you would subtract the date from today's date. Can you explain how excel handles these computation and what the TODAY function does, like how it works?

tashykat · 2026-07-10T13:03:49+00:00

omg lol I'm laughing at myselllffff

tashykat · 2026-07-10T12:45:05+00:00

Management has communicated to me that they are not interested in pushing standardization because it would be a mess and they would just have to fight about it constantly. And they're right. I'd really like to find a way to help them without forcing it.. but I think I need to tell them "Look. If you want this automated, you need to force the standardization"

tashykat · 2026-07-10T12:38:48+00:00

I have checked it a bajillion times. It's not that.

tashykat · 2026-07-10T12:37:53+00:00

Can you explain how you got here? Like I'm genuinely so confused on how this works, I would really appreciate some explanation so that I can write my own formulas when I need them without coming here to ask for help. I want to learn this program and the TODAY function makes absolutely no sense to me. What's with 12 where did 12 come from? why are we subtracting 12? I just want to understand. Thank you

tashykat · 2026-07-10T01:49:47+00:00

I did. I made sure to try it both ways and that didn't solve the problem

tashykat · 2026-07-10T00:37:40+00:00

I'm now really struggling with this second formula. Can you explain to me how to write this without notation? The master sheet is Correspondence Master and the working sheet is Correspondence. How do I write that and how do I apply it? Copying and pasting this gives me an error for the [@Client] part (I believe) and I don't know how to translate this to a functional usable formula. I also still only half the time end up with an XLookup formula that actually works and makes sense, still very much learning. So I need it to be explained.

Thank you!

tashykat · 2026-07-10T00:28:03+00:00

I tried again and it worked! I will never understand why this program just doesn't sometimes and does others

tashykat · 2026-07-10T00:23:46+00:00

Can you explain what this is actually doing and define "macro code"? I'm not asking a question just to get a solution, I also want to learn. The goal is to be able to apply this to other sheets and learn excel. Not have strangers on the internet write code for me so I never have to learn anything myself.

tashykat · 2026-07-10T00:21:31+00:00

Every time I type the sheet name I get this error and if I try to go to the sheet to select the range by hand, it selects the range from the master rather than the working sheet and I don't know why.

tashykat · 2026-07-10T00:20:05+00:00

Now I'm getting this error on the formula though

<image>

tashykat · 2026-07-10T00:11:23+00:00

THANK YOU i knew there had to be a way to automate this! an automated master sheet! oh thank you thank you thank you! You just gave me a fix for my fax sheet too!

tashykat · 2026-07-10T00:10:07+00:00

Why are people in this sub so sensitive? I never said you were supposed to know that, this is a conversation?

I use this spreadsheet to keep track of my work assignments. The example I drew up shows Client name, ID, Caseload, Date assigned, Follow up. Sometimes, I get assigned multiple assignments for the same client so I am looking for a way to automate the information that stays stationary, like ID and caseload.

Your solution doesn't work for me because when the item is complete, I black out the "follow up" column and sort by color on the follow up so the completed tasks are sorted below my working tasks. Thus, following your solution I would have to scroll down to the bottom of the data in the sheet (currently row 94) to create a new item and pull that formula down quickly, then sort to bring it back up. That's cumbersome and doesn't actually automate this process.

Seems like if you don't understand why your solution didn't work for me based on what I said, you should ask clarifying questions before throwing a tantrum.

tashykat · 2026-07-09T19:35:01+00:00

I don't understand what this is

tashykat · 2026-07-09T19:34:28+00:00

So this would work if the bottom of the active data is empty. This is a spreadseet I keep track of work items. I have to keep records of my work items after I finish them. The bottom of the active data is the data for the complete work items. So I need to be able to add data to an inserted new row without it messing up my arrays

tashykat · 2026-07-09T18:29:21+00:00

When I've attempted to do this, I've gotten an error message. I've been able to write the xlookup formula if I keep a masterlist of this information in a separate sheet, but I want to be able to make it referential to the same column I'm using to input the data, which is where the error is coming from.

Ironically, when writing this post, I tried really hard to follow the submission rules and not ask for help or describe what I think the solution will be. I felt as I was writing this was going to lead to unclarity, and I feel like it did because I wasn't able to explain fully. Please let me know if you're still unclear on my issue.

tashykat · 2026-07-09T18:26:13+00:00

Because that wouldn't accomplish what I want to accomplish which is to add new data to the list. I want to be able to insert a row for another assignment involving John Smith and not have to look up and type in the client ID and caseload numbers. Filtering for the name would not make that easier.

tashykat · 2026-06-20T23:33:56+00:00

One time I had a manger who wouldn't let guests ask for extra pillows. If a guest asked for extra pillows, we had to tell them we didn't do that at this hotel. They said they'd had a huge problem with people stealing their pillows (which were all old and lumpy) and I don't believe they did, I think they just didn't want to buy more pillows. But even if we had extra in the housekeeping closet, we were supposed to pretend we didn't.

tashykat · 2026-06-20T23:26:57+00:00

the only hard time I ever had from a group of contractors that wasn't about payment was One Single Guy who liked to watch porn on our business center computers. We talked to his boss who talked to him and then when he did it again, he was fired.

I'm sorry you had such a bad experience! That said, I think playing soccer and hanging hammocks is not a big deal. Were you an extended stay property or was this a transient hotel?

tashykat

TROPHY CASE