Conditional Formatting for Various Dates

[GBCOACW]

I am trying to figure out formulas to create conditional formatting for dates that take into account the current date and its relation to future dates, and that are not limited to specific months, years, or number of days.
I would like these formulas to be able to carry over into future years without needing to be updated after a specific month or year has passed.
The dates are all within column C.
Specifically, I'm trying to create formulas to:

• Highlight cell if a date is before the current date
• Highlight cell if a date is within the current month
• Highlight cell if a date is after the current month, but within the current year
• Highlight cell if a date is any year after the current year

Many thanks!

 

[Joe4]

Welcome to the Board!

Looks like some sort of homework assignment.
Have a look here, which shows you how to use various methods to use dates in Conditional Formatting.

Excel conditional formatting for dates & time: formulas and rules

See how to apply Excel conditional formatting to dates and time. How to use formulas to highlight weekends and holidays, format cells when a value is changed to a date, shade upcoming dates and delays, conditionally format dates based on the current date, and more.

www.ablebits.com

It should cover most of what you want, and after you see the methods being used, you might be able to figure out the rest.
Post back if you get stuck with any certain one.

 

[MountainFog]

You might also want to think about adding a cell that has the current date in it. You can use the =TODAY() function in excel, but if you do that for all of your conditional formatting on a large number of cells, you will find that your spreadsheet slows down significantly as =TODAY() is a volatile function and will recalculate every time a volatile action is taken.

 

[Joe4]

You might also want to think about adding a cell that has the current date in it.

You mean hard-coded, right? To avoid the =TODAY() volatility?

If so, instead of manually updated that every day, they could use a Workbook_Open event procedure to automatically hard-code the value into the cell whenever the workbook is opened, i.e.

Private Sub Workbook_Open()
    Sheets("Sheet1").Range("A1") = Date
End Sub

 

[MountainFog]

You mean hard-coded, right? To avoid the =TODAY() volatility?

If so, instead of manually updated that every day, they could use a Workbook_Open event procedure to automatically hard-code the value into the cell whenever the workbook is opened, i.e.

Private Sub Workbook_Open()
    Sheets("Sheet1").Range("A1") = Date
End Sub

exactly!