Need Formula / Conditional formatting - Dates

jeetusaini85

Board Regular
Joined
Aug 9, 2013
Messages
131
Dear All,

I have a data file in which i have two column, 1st is Expiry Date column in which past, present and future dates are mentioned and 2nd is Status column in which user will mention status either "OK" or "Pending".

I need a formula / conditional formatting which will start highlight ( in Red Color ) Expiry date before 15 days of the Expiry Date till the user mark status as "OK" and change the color ( in Green Color ).

I tried many formula's and conditions but fails. Please help.

Thanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I need a formula / conditional formatting which will start highlight ( in Red Color ) Expiry date before 15 days of the Expiry Date till the user mark status as "OK" and change the color ( in Green Color ).
Can you clarify this a bit? Sounds like some circular logic ("Expiry date before 15 days of the Expiry Date"???)
Perhaps posting a small example will help clarify it.
 
Upvote 0
Hey Joe, thanks for reply...

Expiry Dates in 1st column should highlight before 15 days till the user mark status as "OK" in 2nd column.

Example: - Let say expiry date is 10-Nov-13 so it should highlight from 26-Oct-13 till the 2nd column status is "OK" and when "OK" hits in 2nd column expiry date color turn into green from red.

Thanks
 
Upvote 0
So like you will need a two conditional formatting formulas.

so it should highlight from 26-Oct-13 till the 2nd column status is "OK
The first one (assuming entries in cell A1 and B1):
Code:
=AND((A1-TODAY())<15,B1<>"OK")
and apply your highlighting format.

till the user mark status as "OK" and change the color
The second one:
Code:
=B1="OK"
and apply your format.

I am not sure if you want to include date logic or not with the second one. If so, follow the format of the first one.

I am also not sure what you want to do if the expiry date has passed. If you want to exclude those dates from the first check, you would just add another condition to the AND formula, i.e.
Code:
=AND((A1-TODAY())<15,(A1-TODAY())>0,B1<>"OK")
 
Upvote 0
Hey Joe,

If the expiry date has passed and status is pending it ( Expiry date ) should reflect till the status mark as OK.

Thanks....
 
Upvote 0
Hey Joe, tried your formula but not succeed. Let's take an example:

Expiry Date = 12-Nov-13
Status = Pending
What i want is = Expiry date should start reflecting from 28-Oct-13 (before 15 days of the expiry date) in Red color till the status mark as "OK". If status is pending and expiry date has passed it should reflect RED continue till the status mark as "OK". There are lots of dates in expiry date column which are future dates and past dates also with status.

Thanks
 
Upvote 0
The first formula I posted will work with one very small minor modification. It set to return the formatting when the date is less than 15 days. It sounds like you want less than or equal to. So we just need to adjust that formula accordingly.

Code:
=AND((A1-TODAY())<[B]=[/B]15,B1<>"OK")
 
Upvote 0
Hey Joe,

i tried this formula but i m confused.... It is changing the formatting of A2 When i mark "OK" in B1, Why? It should change only of A1.
 
Upvote 0
Sorry Joe, my mistake....

I m taking this in a wrong way, now it works. A little help more, a second formula required that when 15 days has been passed it should change the second color.

I m assuming that if i change the condition of 1st formula this will work like this=>

=AND((A1-TODAY())>=15,B1<>"OK")</pre>
 
Upvote 0
Code:
[COLOR=#333333]=AND((A1-TODAY())[/COLOR][B]>15,B1<>"OK")[/B]
Since the first formula is "less than or equal", you would want the second formula to just be "greater than" (not "greater than or equal to"). You do not want overlap with the equals condition. It should just be in one condition, not both.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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