COLOR CODES FOR DATE TRACKING

[hsarrategui]

I am trying to track our leases that are 6 months long. I need 3 formulas.

I need a formula that will change the date in the cell to green when up to date.

A formula that changes those cells to yellow if we have 3 month left in the 6 months

And a formula that turns red the month it expires.

So if the cell says 3/31/24 it should be yellow now and turn red on march 1st. but be green or colorless from October 1st until January 1st.

The columns are "C" and "H" starting on line "4".

 

[dreid1011]

What is in the columns C and H?

 

[hsarrategui]

What is in the columns C and H?

dates. i didn't know if the columns mattered. But thats where the expiration date is being entered

 

[dreid1011]

dates. i didn't know if the columns mattered. But thats where the expiration date is being entered

Okay, I suppose I should be a little more specific. Which date is in Column C and which date is in Column H? Is there a start date and expiration date, or is the expiration date compared to today's date?

 

[awoohaw]

Not really sure what you have going on completely. But here is a basic comparison conditional formatting mini worksheet:

Book1
	C					H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4	2024-03-31
5							2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01
6	Lease Date
7	2024-01-31						2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01
8	Red					=EOMONTH(I$7,-1)<=$C$7
9	Yellow					=EOMONTH(I$7,-3)<=$C$7
10	Green					=EOMONTH(I$7,-3)>$C$7
Sheet1

Cell Formulas
Range	Formula
I5	I5	=EDATE(J5,-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I7:R7	Expression	=EOMONTH(I$7,-1)<=$C$7	text	NO
I7:R7	Expression	=EOMONTH(I$7,-3)<=$C$7	text	NO
I7:R7	Expression	=EOMONTH(I$7,-3)>$C$7	text	NO

.



Mr. Excel has a tool called xl2bb add in that allows you to post mini worksheets like I did above. (link is below) Please use it. The forum can make errors and is wasting it's time trying to guess what your scenario looks like. If you cannot use the tool, the post your data as a table (include row and column headers). Images are marginally helpful as the forum must type in all of the scenario.

Thanks in advance.

 

[awoohaw]

deleted. sorry.

 

[hsarrategui]

Okay, I suppose I should be a little more specific. Which date is in Column C and which date is in Column H? Is there a start date and expiration date, or is the expiration date compared to today's date?

Not really sure what you have going on completely. But here is a basic comparison conditional formatting mini worksheet:

Book1
	C					H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4	2024-03-31
5							2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01
6	Lease Date
7	2024-01-31						2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01	2024-09-01	2024-10-01	2024-11-01
8	Red					=EOMONTH(I$7,-1)<=$C$7
9	Yellow					=EOMONTH(I$7,-3)<=$C$7
10	Green					=EOMONTH(I$7,-3)>$C$7
Sheet1

Cell Formulas
Range	Formula
I5	I5	=EDATE(J5,-1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I7:R7	Expression	=EOMONTH(I$7,-1)<=$C$7	text	NO
I7:R7	Expression	=EOMONTH(I$7,-3)<=$C$7	text	NO
I7:R7	Expression	=EOMONTH(I$7,-3)>$C$7	text	NO

.



Mr. Excel has a tool called xl2bb add in that allows you to post mini worksheets like I did above. (link is below) Please use it. The forum can make errors and is wasting it's time trying to guess what your scenario looks like. If you cannot use the tool, the post your data as a table (include row and column headers). Images are marginally helpful as the forum must type in all of the scenario.

Thanks in adv

Okay, I suppose I should be a little more specific. Which date is in Column C and which date is in Column H? Is there a start date and expiration date, or is the expiration date compared to today's date?

so the only dates provided are the expiration date itself. so the dates change when the expiration date changes. I have added a screen shot of the columns. We have 2 properties so each column is not related. I want to be able to open the worksheet and have the dates colored based on how far today is from the expiration date. So if I put 7/1 then the date should be green until april and then red starting june.

 

[awoohaw]

What date are you comparing the expiration dates to? Are you typing a date in somewhere?

Did you read my statement at the bottom of POST #5. Please post a mini worksheet or a table. Images are not helpful.
What are your expectations based upon the data you have in the workbook? Which cells should be yellow, red, or green?

 

[hsarrategui]

What date are you comparing the expiration dates to? Are you typing a date in somewhere?

Did you read my statement at the bottom of POST #5. Please post a mini worksheet or a table. Images are not helpful.
What are your expectations?

I've tried the mini worksheet and it isn't opening for me. It never does. If you click on the picture you can see what I uploaded. I also explained what I needed along with the photo. The only dates in the worksheet are the expiration dates.

"so the only dates provided are the expiration date itself. so the dates change when the expiration date changes. I have added a screen shot of the columns. We have 2 properties so each column is not related. I want to be able to open the worksheet and have the dates colored based on how far today is from the expiration date. So if I put 7/1 then the date should be green until April and then red starting June."

With the mini worksheet not working for me I don't know how else to explain it.

 

[awoohaw]

There is a debugging section for xl2bb on the forum.

The forum are excel lovers and volunteers. The easier you make the job for them the better. Images are almost the worst thing to ask forum member to debug.
Putting aside a forum members question about what something means is discourteous to the people who are trying to help you. We ask because we want to help.

The solution i offered in POST #5 had the concepts you needed.

Here is the update:
(I"m not sure if I have the months counting the way you want, but you can figure that out.)

Book1
	C					H
6	Lease Date
7	2024-05-01
8	Red					FALSE
9	Yellow					FALSE
10	Green					TRUE
Sheet1

Cell Formulas
Range	Formula
H8	H8	=C7 <= EOMONTH(TODAY(),1)
H9	H9	=AND(C7 >= EOMONTH(TODAY(),1),C7 <= EOMONTH(TODAY(),3))
H10	H10	=C7>=EOMONTH(TODAY(),3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7	Expression	=AND(C7 >= EOMONTH(TODAY(),1),C7 < EOMONTH(TODAY(),3))	text	NO
C7	Expression	=C7>=EOMONTH(TODAY(),3)	text	NO
C7	Expression	=C7 <= EOMONTH(TODAY(),1)	text	NO