Conditional Formatting Dates

[jrhall621]

I am somewhat new to Excel and formulas, so please forgive me if I am a little slow on the uptake.

What I am trying to accomplish is I want the cells in column B to turn yellow if the date in the cell is within 1 month of the current date and red if the date is within 14 days of the current date (or passed the current date) but I only want it to turn colors if the aforementioned is true AND column B is NOT blank AND column M IS blank.

Can someone help me with a formula on this?

Thanks,

James

[Roel Jongman]

Hi jrhall621

Welcome, It is kind of expected you upload an example sheet with your questions (forum rule)
but in this case it was not to difficult to build the formula in a blank sheet with self created testdata.
So i did that..

You will need separate CF lines for the yellow and red coloring.
but then the formula is relative simple

for Yellow this is the formula

Please Login or Register  to view this content.

Where the Applied to range in the CF should start with B3
(the end of the applied to range does not matter)
i.e. when you apply to your sheet make sure that the startpoint of your applied range is the testcell in the formula and make sure the $ signs are applied the same way.

I used applied range columns B:m but if you only want to color 1 cell per row simple only use col B for applied range.

see attachment for some testing of your own

[jrhall621]

Okay, so I think I explained it wrong, so sorry.. I am going to go ahead and upload the spreadsheet, so that you can possibly see what I am trying to do, and I am going to explain it better below:

So, in column B we have apartment numbers, then in column G we have the date the lease expires, then in Column M we have the decision the tenant has made (yes for renewal no for notice and BLANK if they have not decided).

What I am trying to do, is if a cell in column B is NOT blank, that means that that row has an entry (apartment), so then I want to check to see if the "result" (Column M) is Blank, because if it is blank then the resident has not decided what they want to do yet, and if both of those are true, I want it to look at column G to see if any of those rows which have the above true, have any dates that are coming within 14 days or one month and color them Red and Yellow respectively. This will help us keep track of who has not put in a notice or renewed who has a lease that will be expiring soon.

I will upload the sheet now.

In the case of this example, I would want row 9 and row 13 to be red

[Bo_Ry]

Please try CF formula applies to $B$3:$T$42

=($B3<>"")*($M3="")

[Roel Jongman]

You were very close to the result you wanted..

- you forgot to set the format color.
- I cleaned up the applied to range (when you copy and paste info or delete rows after setting a CF often the applied to range gets messedup a bit. bit of a sloppy handling by excel)
- The test for cell M3 missed the dollar sign changed to $M3 this makes sure only column M is tested.

see attachement for result

[jrhall621]

Yay!! Thanks so much you all! I finally got it... It was weird when I would paste the formula it would change the range like Roel said.