Conditional formatting date formula based on another celll

SephJo

New Member
Joined
Jun 26, 2015
Messages
3
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

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

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

HTH
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Glad your could fix your problem ...;)

Thanks for your thanks
 
Upvote 0

Forum statistics

Threads
1,226,695
Messages
6,192,481
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top