If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting
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 |
#2
|
|||
|
|||
Conditional Formatting
Hi Donna
=YEAR(DATE(A1,1,1))+100 compared against; Year(Today()) Regards, Per On 23 Mar., 01:03, Donna wrote: 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 |
#3
|
|||
|
|||
Conditional Formatting
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" wrote: Hi Donna =YEAR(DATE(A1,1,1))+100 compared against; Year(Today()) Regards, Per On 23 Mar., 01:03, Donna wrote: 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 . |
#4
|
|||
|
|||
Conditional Formatting
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 On 23 Mar., 03:07, Donna wrote: 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" wrote: Hi Donna =YEAR(DATE(A1,1,1))+100 compared against; Year(Today()) Regards, Per On 23 Mar., 01:03, Donna wrote: 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 .- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#5
|
|||
|
|||
Conditional Formatting
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. -- David Biddulph "Donna" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|