microsoft excel - How to change background colour of column based upon column header date

I have a table in Excel which looks like

        A            B            C            D
1    01/01/2018  07/01/2018   14/01/2018   21/01/2018   
2    some vale                               detail
3                  value          

For this question, we can assume the date is 10/01/2018 (DD/MM/YY)

Row 1 are my headers. Row 2 and 3 are the table body (content).

As you can see, column A and B are before 10/01/2018 where as column C and D are after 10/01/2018

What I'd like to do is change the background colour of the column based on this, so it's easy to see which column are in the past.

A google search suggests Conditional formatting and there is an option called A Date Occuring, but that only has pre-defined date values, such as yesterday, today, tomorrow etc, and no option for before today! As such I'm now totally lost and not sure if this can be achieved.

Answer

I assume that your dates are set to the format DD-MM-YYYY in your regional settings in the Operating System so that Excel shall understand 1-12-2018 as 1st Dec 2018.

You have an actual table in Excel not just a set or rows and columns.

First remove any formatting from the table. Set the color formatting as None.

Now select Cells A2:A3 go to Conditional Formatting -->New Rule --> Use a Formula to determine which cells to format option.

Now enter the formula.

=DATE(YEAR(A$1),MONTH(A$1),DAY(A$1))

Select the color format that you desire.

Click OK and exit. Now the rule shall be applied to cells A2:A3.

Use a format painter and apply it to all other cells from rest of the columns.

Since it's a Table, as you add rows to it the Conditional Formatting shall get auto applied thereon.

If instead of TODAY() you wish to specify some other date e.g. 10-01-2018 then in place of TODAY() Use DATE function specifying Year, Month, Day as parameters to it.