Formula with date

  • Thread starter Thread starter myname
  • Start date Start date
M

myname

Hi,
Hello,
I need help with a formula for excel ...
I tell you right away that I am a newbie to excel.
I need the following things:
Should I work with a formula so that if the date entered in a given cell A1
is older than 3 years in cell A2 is shown the text "old"

How can i do that? TIA
 
Hi,
Hello,
I need help with a formula for excel ...
I tell you right away that I am a newbie to excel.
I need the following things:
Should I work with a formula so that if the date entered in a given cell A1
is older than 3 years in cell A2 is shown the text "old"

How can i do that? TIA
Click to expand...

This might do what you want:

A2: =IF(YEARFRAC(A1,TODAY())>3,"old","")

--ron
 
I was going to post this formula...

=IF(A1<DATE(YEAR(NOW())-3,MONTH(NOW()),DAY(NOW())),"Old","")

but decided to test it out against your formula and I found a discrepancy.
Change your formula to this...

=IF(YEARFRAC(A1,$D$1)>3,"Old","")

and use this for my formula...

=IF(A1<DATE(YEAR($D$1)-3,MONTH($D$1),DAY($D$1)),"Old","")

Then put =DATE(2004,5,30) in D1 and put =DATE(2007,5,31) in A1 (you can put
the date constants for these dates in the cells if you want... I used the
DATE function to eliminate possible regional date problems). My formula says
5/30/2004 is "Old" and yours says it is not... I think it should be
considered old, right?
 
I was going to post this formula...

=IF(A1<DATE(YEAR(NOW())-3,MONTH(NOW()),DAY(NOW())),"Old","")

but decided to test it out against your formula and I found a discrepancy.
Change your formula to this...

=IF(YEARFRAC(A1,$D$1)>3,"Old","")

and use this for my formula...

=IF(A1<DATE(YEAR($D$1)-3,MONTH($D$1),DAY($D$1)),"Old","")

Then put =DATE(2004,5,30) in D1 and put =DATE(2007,5,31) in A1 (you can put
the date constants for these dates in the cells if you want... I used the
DATE function to eliminate possible regional date problems). My formula says
5/30/2004 is "Old" and yours says it is not... I think it should be
considered old, right?
Click to expand...

Well, whether it is "correct" or not depends on the year basis that the OP
wants to use.

I think if you want to use the actual/actual basis, it might work better.

So my formula could be changed to

=YEARFRAC(A1,A2,1)

Of course, then the question comes up with regard to categorizing

1/1/08 -- 1/1/11

Is that three years? Or over three years?



--ron
 
Back
Top