2

I need cells in Excel to be highlighted if they contain 2017 in the date. For instance there is a column that has just dates. Some dates are for 2017. I need to input those dates at a later time.

The column with approved vacation for the year and includes some dates for 2017. I've tried the Highlight Cell with text that contains "2017", "m/dd/2017", "mm/dd/2017". What am I doing incorrectly?

4
  • More information is required to answer your question.
    – Ramhound
    Apr 11, 2016 at 15:57
  • 1
    @Ramhound What additional information could you possibly be seeking? Jane: info on Excel date highlighting seems to provide multiple optins. Any of them any good for you?
    – TOOGAM
    Apr 11, 2016 at 16:08
  • Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question.
    – DavidPostill
    Apr 11, 2016 at 16:10
  • Thank you for your comments. Yes, it is in an excel spreadsheet. Column with approved vacation for the year and includes some dates for 2017. I've tried the Highlight Cell with text that contains "2017", "m/dd/2017", "mm/dd/2017" What am I doing incorrectly?
    – Jane
    Apr 11, 2016 at 17:33

1 Answer 1

1

Use the conditional formatting FORMULA option. Assuming your test cell is A1 the formula would be =YEAR(A1)="2017"

The problem you will be having is that Excel is converting your dates to an integer value (the number of days since 00/01/1900) then displaying them in the form "mm/dd/yyyy"; 01/01/2017 is stored as 42736. Conditional formatting for "contains 2017" thus won't work.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .