Date difference

[Art Wrok]

I would like a formula to determine if someone is 70 1/2 at the end of
a year. I used =DATEDIF(DateofBirth,EndofYear­,"y") but the result is
only accurate to a whole year, for example DateofBirth = 6/30/1937 and
EndofYear = 12/31/2007 gives 70.0 rather than 70.5. How can I do this?

 

[Ron Rosenfeld]

I would like a formula to determine if someone is 70 1/2 at the end of
a year. I used =DATEDIF(DateofBirth,EndofYear­,"y") but the result is
only accurate to a whole year, for example DateofBirth = 6/30/1937 and
EndofYear = 12/31/2007 gives 70.0 rather than 70.5. How can I do this?

=EndOfYear>=DATE(YEAR(dob)+70,MONTH(dob)+6,DAY(dob))

will return TRUE or FALSE depending on the age being 70.5 at the end of the
year, or not
--ron

 

[T. Valko]

Calculate the total months then divide by 12:

=DATEDIF(A1,B1,"m")/12

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


I would like a formula to determine if someone is 70 1/2 at the end of
a year. I used =DATEDIF(DateofBirth,EndofYear­,"y") but the result is
only accurate to a whole year, for example DateofBirth = 6/30/1937 and
EndofYear = 12/31/2007 gives 70.0 rather than 70.5. How can I do this?

 

[Rich/rerat]

Art Wrok,
You can try the following:

A1 B1 C1 D1 E1
Name DOB YREnd Age Qualified
Column A & B should be formatted as Date mm/dd/yyyy

A2=Name
B2=6/30/1937
C2=12/31/2007 or =IF($A2="","",DATE(YEAR(NOW()),12,31) {if you want the
current year end}&{and drag down column}
D2: Put in formula: =IF($A2="","",ROUND(($C2-$B2)/365.25,1) {and drag
down column}
E2: Put in formula: =IF($A2="","",IF($C2>=70.5,"Qualified","Not Qualified"))
{and drag down column}

If you want to use DateDif formula you can try this:
In Cell D2: =IF($A2="","",DATEDIF($A2,$B2,"m")) {and drag down column}
In Cell E2: =IF($A2="","",IF($D2>=846,"Qualified","Not Qualified")) {and
drag down column}
--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


I would like a formula to determine if someone is 70 1/2 at the end of
a year. I used =DATEDIF(DateofBirth,EndofYear­,"y") but the result is
only accurate to a whole year, for example DateofBirth = 6/30/1937 and
EndofYear = 12/31/2007 gives 70.0 rather than 70.5. How can I do this?