Conditional Formatting

[Donna]

I have a column of numbers that represent the year. I have it formatted as a
number. I want to compare that number to the current year. What I did was in
another column I used year(today()) to get the current year. It comes out as
2010 I did a right function to the the 10. I then changed it to a number
format and I am trying to do a conditional formatting to highlight all the
numbers in col a that are 10. It does not work unless I manually type in a
10. Any help will be greatly appreciated

col a
9
10
3

 

[Per Jessen]

Hi Donna

=YEAR(DATE(A1,1,1))+100

compared against;

Year(Today())

Regards,
Per

 

[Donna]

Hi Jessen,
I guess I have another problem that I need to solve before comparing the
current year. The problem is although I formatted my date column all the
same way dd/mm/yy - when I just look at the date, its correct. But when I
click on some they will have yyyy and when I do the right function the year
becomes 4001. So I can’t compare the year with the current year. A little
better explanation may by I have a column with dates and other test that I
have pulled out just the dates into a separate column. Some of the dates may
have 07/14/09 and some may just have 7/14/09. What I want to do is pull out
the year, 09 and compare it to the current year 10. I am very flustered with
myself, I know there is just something I must be doing wrong.

 

[Per Jessen]

Donna,

If you dates are 'true dates', you can use the Year function to
extract the year from the date:

=Year("07/14/09") will equal to 2009, which can be compared against
=Year(Today())

Using the Right function on a true date value, will not yield the
expected result.

Hopes this helps.
....
Per

 

[David Biddulph]

If you have used year(today()) to get 2010, right(...) will give a *text*
value of "10". If you want the *number* 10, try --right(...) or
right(...)*1 or (right...)+0.