Excel conditional format based on IF current year

chris pike
chris pike used Ask the Experts™
on
Hi Experts,
I need a formula for a conditional format.
If "D5" equals current year then color conditional cell.
Formula for conditional formatcoditional-date.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
Couple of options:

=VALUE(D5)=YEAR(TODAY())  or =D5*1=YEAR(TODAY())

or

=YEAR(B5)=YEAR(TODAY())

Problem with D5 is that it is text so can't be compared directly with today function so needs converting to a value either using VALUE function or just by multiplying by 1

Alternatively, just use B5 instead.
Rob HensonFinance Analyst

Commented:
Couple of options:

=VALUE(D5)=YEAR(TODAY())  or =D5*1=YEAR(TODAY())

or

=YEAR(B5)=YEAR(TODAY())

Problem with D5 is that it is text so can't be compared directly with today function so needs converting to a value either using VALUE function or just by multiplying by 1

Alternatively, just use B5 instead.
Finance Analyst
Commented:
Alternative...
Change formula in D5 to:
=YEAR(B5)  and format as General.

Then Conditional Format can be just
=D5=YEAR(TODAY())

Custom format wasn't working correctly in case you had tried that.  Using =YEAR(B5) gives result of 2019 but then custom formatting of "yyyy" looks at the number and evaluates it as a serial number for a date which evaluates to 11 July 1905 so just displays the year 1905.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I get it
you are right didn't work
=VALUE(D5)=YEAR(TODAY())    …. works.
 I can do a conditional format on the entire column and it will just highlight this year

Author

Commented:
Thanks Rob
Another quick fix.
Cheers
Rob HensonFinance Analyst

Commented:
No worries, happy to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial