Conditional Format of Current Month

[ir121973]

Hi, I wonder whether someone may be able to help me please.

I'm using the following three formulas to perform conditional formatting on cell B when cell A contains a date. Each condition depicts a cell colour depending on the following:

Within The Current Month
=datevalue(day(today())&"/"&month($q$3)&"/"&year($q$3))=(today())

Prior To The Current Month
=datevalue(day(today())&"/"&month($q$3)&"/"&year($q$3))<(today())

Greater Than The Current Month
=datevalue(day(today())&"/"&month($q$3)&"/"&year($q$3))>(today())

It took me so time to put this together, but I've one other condition which I need to add to the above which I'm having difficulty in solving.

In addition to setting the cell colour of cell B, i would also like to set a text value.

So if the date is:

Within The Current Month, I would like the cell to say "Act'ls",
Prior To The Current Month, I would like the cell to say "Act'ls", and
Greater Than The Current Month, I would like the cell to say "A'able"

As I said earlier, it's left me a little stumped, but I just wondered whether someone could possibly take a look at this please and offer a little guidance on how I may go about this.

Many thanks and kind regards

Chris

 

[Scott R]

You could enter a formula in B1 and copy down to generate text.

B1: =IF(A1<=Compare_Date,"Act'ls","A'able")

where Compare_Date is your date formula. Better yet, put =DATE(YEAR(Q3),MONTH(Q3),DAY(TODAY())) in a cell named 'Compare_Date' then use that name in your formula and your conditional formatting formulas.

 

[ir121973]

Hi @Scott R, thank you very much for taking the time to reply to my post and post the solution, it works a treat.

Many thanks and kind regards