Conditional Formatting to Highlight a Range when Today is between Two Dates DD/MM

Afternoon, all!

I'm scratching my head and wondering if this is possible using a conditional formatting formula: I want to highlight a range of cells based on today being between two non-year-specific dates (e.g. between 01 January and 14 February, and the range would be highlighted between those dates every year). I want to specify the dates in the formula, not in cells on the worksheet. Is it possible to do this? I hope I've made the query clear!

want to specify the dates in the formula, not in cells on the worksheet.

Not entirely sure what you mean by that, and I don't even recommend it. But if you mean that you want those dates to be baked into your conditional formatting formulas, then here is your CF formula:

Formula:

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

Not entirely sure what you mean by that, and I don't even recommend it. But if you mean that you want those dates to be baked into your conditional formatting formulas, then here is your CF formula:

Formula:

Please Login or Register  to view this content.

Thanks for your response, but I have two questions:

1. Why do you recommend not doing this via a conditional formatting formula?
2. Why have you given a cell reference? Perhaps I wasn't clear: I simply want the formula to determine whether today() is between two dates (without the year) and format the cells if that condition is true. The formula you have given is rejected if I copy it into the conditional formatting dialog - too many arguments.

If I understand what you mean by "range would be highlighted between those dates every year" then it seems like 6StringJazzer's formula will work fine in Conditional Fomatting, although it is missing a ")" after the first TODAY function call. Here is the formula with that modification:

Please Login or Register  to view this content.

Here is the formula applied to a range of dates:
CF a range of dates.xlsx
If I am missing the point then perhaps attaching a spreadsheet will help make the query clearer.
Let me know if you have any questions.

Unfortunately, I don't have a spreadsheet to attach, as this is currently just a concept! It's pretty obvious that I'm not making myself clear, and I apologise for this. Let me try again!

Suppose you have three columns in a spreadsheet for data entry: one is to be used between 01 Jan and 14 Feb, the second is to be used between 15 Feb and 31 March and the third between 01 April and 15 June. All I want to do using conditional formatting is to highlight (i.e. fill with a colour) the cells in the column that is current. There are no dates anywhere on the spreadsheet. Does this seem any clearer? If not, I'll create a sample showing the sort of thing I want.

Thanks for your patience!

My take is that you want column A to be highlighted if today's date is between 01 Jan and 14 Feb, column B to be highlighted if today's date is between 15 Feb and 31 March, etc. Take a look at this file (does have dates in the first two rows of each column) which employees the CF formula:

Please Login or Register  to view this content.

CF a range of dates.xlsx

Without using any dates it seems to me like that would would require VBA with code something like IF(AND(TODAY()>= 1 Jan,TODAY()<= 14 Feb), apply fill to all non-blank cells in column A, IF...
I am not familiar enough with VBA to go further than just my conceptualization of how this might be done.

Let me know if you have any questions.

Originally Posted by AliGW

Let me try again!

That is much clearer than your original description, thanks.

First, doing this with conditional formatting is fine. What I don't recommend is that you put dates directly in your formulas and have no dates in your file. That is because if you ever need to change anything, it is much easier to change your worksheet than to edit conditional formatting formulas.

This is the conditional formatting formula for the first column. Unfortunately you won't be able to test it until New Year's Day :-)

Formula:

Please Login or Register  to view this content.

Simply use the same format and copy the formula to use in the other columns, changing the dates appropriately.


But here is the way I would do it. See attached file. This method allows you to use a single conditional formatting rule, instead of one for each column. And if your period dates change, it's a quick change to the spreadsheet. highlight current column.xlsx

Added: Regarding JeteMc's suggestion, you can see by my formula above that VBA is not required; it can be done with CF. Also, I looked at JeteMc's file, which is very similar to my preferred solution, although is still sensitive as to the year. My version works no matter what year it is.

Thank you both very much for your replies! 6StringJazzer has hit the nail on the head, and the CF formula works beautifully for what I require. I use a lot of CF in my tracking workbooks, as we use a traffic light system to show the progress our students are making, so I'm perfectly happy editing CF formulae, and having the dates on the sheet itself (or anywhere in the workbook) was not my preferred solution. Thanks again!