Probably impossible conditional formating

[Front]

I think this might be impossible. After trying to figure it out for a couple of hours I thought I would ask people much better than myself at excel.

I am attempting to use conditional formatting to change a cell a color if contains conditions that are true.

In the target cell I will have a future date and perhaps an acronym following the date. For example, it would be one of the three:

12/15/21 BMO
12/15/21 AMC
12/15/21

BMO means before market open, so that actually means that date should count for one day sooner, so it should technically count as 12/14/21. AMC means after market close, so this should count as that day. However, it’s just the date, we don’t know if it’s BMO or AMC, so it should put up a color warning for both or if that is an issue count as just BMO.

I want to put up the color warning one week before that date. I was thinking something like this

=AND(ISNUMBER(SEARCH($AE$39,Z3)),ISBLANK(P3))

Where AE39 is the current date and Z3 is the target cell, but that only works for one date. Plus that does not work for the acronyms. I thought to do a range for the date like this

F3>=$AE$27,F3<=$AE$30

but then the acronym runes that. I’m about rate to scrap the idea and do something inferior, but I thought a check your first.

 

[jasonb75]

See how you get on with this. There was no clarification of ISBLANK(P3) in your post so I've just added that back in as a separate criteria from the dates.

=AND(ISBLANK(P3),OR(N(Z3)-$AE$39=ROW(INDIRECT("1:7")),IFERROR(DATEVALUE(SUBSTITUTE(Z3," BMO",""))-1-$AE$39=ROW(INDIRECT("1:7")),0),IFERROR(DATEVALUE(SUBSTITUTE(Z3," AMC",""))-$AE$39=ROW(INDIRECT("1:7")),0)))

 

[Front]

Thank you, it didn't work but I'll look into what you did see if I can figure it out.

 

[jasonb75]

Try it without the ISBLANK part, does it work then?

The formula does what you've asked for but I did notice a lack if consistency in your references (Z3 or F3, AE39,AE27 or AE30)?

 

[jasonb75]

With the current date of 15/11/21 in AE39, this is what I get. Note that I'm using UK format dates, but it will work the same with any dates that are in the correct format for your region.

Book1
	Z
3	15/12/21 BMO
4	15/12/21 AMC
5	15/12/2021
6	15/11/21 BMO
7	15/11/21 AMC
8	15/11/2021
9	16/11/21 BMO
10	16/11/21 AMC
11	16/11/2021
12	17/11/21 BMO
13	17/11/21 AMC
14	17/11/2021
15	18/11/21 BMO
16	18/11/21 AMC
17	18/11/2021
18	19/11/21 BMO
19	19/11/21 AMC
20	19/11/2021
21	20/11/21 BMO
22	20/11/21 AMC
23	20/11/2021
24	21/11/21 BMO
25	21/11/21 AMC
26	21/11/2021
27	22/11/21 BMO
28	22/11/21 AMC
29	22/11/2021
30	23/11/21 BMO
31	23/11/21 AMC
32	23/11/2021
33	24/11/21 BMO
34	24/11/21 AMC
35	24/11/2021
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Z3:Z35	Expression	=AND(ISBLANK(P3),OR(N(Z3)-$AE$39=ROW(INDIRECT("1:7")),IFERROR(DATEVALUE(SUBSTITUTE(Z3," BMO",""))-1-$AE$39=ROW(INDIRECT("1:7")),0),IFERROR(DATEVALUE(SUBSTITUTE(Z3," AMC",""))-$AE$39=ROW(INDIRECT("1:7")),0)))	text	NO

 

[Front]

Thank you! I had to change something on my end to get it working, but was able to get it. Very impressive how you can whip something out like that in no time.

 

[Front]

I need one more conditional formatting. The above worked great for a week out, but I need to do a different color for one day out. I thought I could just edit the above formula, but this is one I just dont understand. So I wrote a new one because its only looking for one day.

=OR(Y3=TEXT(TODAY()+1,"m/d/y")&" BMO",Y3=TEXT(TODAY(),"m/d/y")&" AMC",Y3=TODAY()+1,Y3=TODAY())

It works fine, but there is one problem. Even though I have the cell format set to a 2 digit year, if I have the date in there, then later add BMO or AMC after the date it changes to a four digit year and then does not work. This does not happen with the formula you did for me. Is there a way to fix it so the date does not update, or can you please convert that formula you did for me above to the one day version?

 

[jasonb75]

edited post:- totally misread what was asked ?

"m/d/y" in your text functions is going to look for 2 digit year, with month and day omitting any leading zero's. Changing that to "mm/dd/yyyy" should work.

 

[Front]

That does not work as all the other dates are in two digit years, so change to mm/dd/yyyy would break those. What I really need is for excel to not update the year from two digits to four, but I dont think that's possible. The other option is for the conditional formatting to not care if its two or four. I suppose what I could do is just add two more or statements with the four digit year format, so it checks for both.

 

[Front]

Yep that worked, it looks sucky but it worked. Thank you!