Calculating dates

[giggles1981]

Hi

I already have a formula to calculate how old something is from the date it was purchased to todays date, however i need to be able to find out how many of these are older than 4 years.

Could anyone help me here?

thanks

 

[fairwinds]

Welcome to the board!

=DATEDIF(A1,TODAY(),"Y")>=4

Should tell you if it is over 4 years old.

 

[giggles1981]

thanks for that equation but i couldnt get it to work

is there a way i can attach the spreadsheet to show you?

 

[bigbob]

Hi
If the two dates are in A1 and B1 then the formula =YEAR(A1)-YEAR(B1) should give the answer you need
Bob

 

[giggles1981]

sorry still not working i have 3 columns 1 with the ship date of the product (I2 all the way to I796) then a column with todays date in J2 to J796) then a column with the following equation in to determin the age of the machines =K2MONTH(J766)-MONTH(I766)&" months"&" "&DAY(J766)-DAY(I766)&" days" but i need a formula in the next colum or wherever to find out exactly how many of these are more than 4 years old so we know how many to replace - usually i would be able to figure something but its friday and my brain has turned to mush!!!

 

[bigbob]

Hi
From your second post the formula in column K calculates the age in months and days so if you take the first half of itL766 =MONTH(J766)-MONTH(I766) that will give you the number of months You now have to see if this is greater than 48 (4x12) .So what about this in column M
=IF(L766>48,"OVER","UNDER")

 

[fairwinds]

Or building on the formula i suggested before:

=SUMPRODUCT(--(DATEDIF(A1:A10,TODAY(),"Y")>=4))

Should give you the count of products older than 4 years.