Conditional formatting date formula based on another celll

[SephJo]

Cell H2 will either contain a Y or N.
Cell I2 will contain a date.

I want to highlight I2 if it is in the current week only if H2 contains Y and I2 is minus one month.

So I guess I want reminded one month before the date in I2 but only in the current week.

Not sure if I'm on the right track.

=AND(H2="Y",EDATE(I2,1))

Thanks

 

[James006]

Hi,

Not sure to fully understand the "current week" constraint ...

You can try =AND(H2="Y",TODAY()-30<=I2)

HTH

 

[SephJo]

Thanks for the reply.

You right that does not make sense the way I have it written.

=AND(H2="Y",EDATE(I2,-1)=Today())

The above formula works but if I'm not at work that day or if it is a weekend I won't see the reminder. Is there anyway to have the above formula to highlight I2 when it is within the current workweek or current month? I tried the below but it did not work.

=AND(H2="Y",EDATE(I2,-1)=MONTH(I2))

 

[James006]

Re,

It would seem to me the current week would be "included" in the current month ...

Here are four formulas to help :

1. Current Week :
a) previous Monday : =TODAY()-WEEKDAY(TODAY(),2)+1
b) next Sunday : =TODAY()-WEEKDAY(TODAY(),2)+7

2. Current Month
a) first day of month : =DATE(YEAR(TODAY()),MONTH(TODAY()),1)
b) last day of month : =DATE(YEAR(TODAY()),MONTH(TODAY()+1),0)

HTH

 

[SephJo]

Thanks J006,

That pointed me in the right direction. I tend to over think these things.... or I'm just not that bright.

This is what I came up with.

=AND(H2="Y",I2-TODAY()>=0,I2-TODAY()<=30)

Although not perfect it will stay highlighted for 30 days.

 

[James006]

Glad your could fix your problem ...

Thanks for your thanks