calculation of age in vba [SOLVED]

[ganeshm]

how do i calculate age in dd/mm/yyyy format through vba code considering A1 is my input cell.


rgds,

ganeshm

 

[webmax]

Hi find below the macro code


After running the below macro you have to change the format as number in B1 Cell


Sub calculateage()

Range("B1").Formula = "=Int((TODAY() - A1) / 365.25)"

End Sub

 

[ganeshm]

Thank you for your prompt reply.


I substituted the formula. I am unable to get the result.


Below is the file link for your reference.


http://www.adrive.com/public/n82xpD/Staff Details format.xlsm


Is it possible to show result in F6 only when input is given in E6.


Otherwise, F6 should remain empty.

 

[Hui]

F6: =IF(E6>0,INT((TODAY() - E6) / 365.25),"")

Copy down


VBA isn't required

Original question asked for A1, not E6 ?

 

[ganeshm]

thanks


it works great.

 

[Hui]

or in F6 you could have something like:

=TEXT(TODAY()-E6,"yy ""Years"" mm ""Months"" dd ""Days""")

 

[webmax]

Hi use this if you need macro


Sub calculateage()

If Range("E6") = "" Then

Range("F6") = ""

Else

Range("F6").Formula = "=Int((TODAY() - E6) / 365.25)"

End If

End Sub

 

[DaveTurton]

Hi Hui


I know the post is now marked as solved, but I like to read though them and try peoples answers out. The last one you provided wouldn't quite work for me:


=TEXT(TODAY()-E6,"yy ""Years"" mm ""Months"" dd ""Days""")


Against a date of "09/07/1983" (UK format) gave me "29 Years 12 Months 06 Days"


I can see what it is doing, 29 years since 83, 12 months since July and 6 days since the 9th.

 

[Debraj]

Hi Dave..


Try this..

=INT((TODAY()-A1)/365.25) & " years , " & INT(MOD((TODAY()-A1)/365.25,1)*12) & " months and " & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & " days"

Regards!

Deb

 

[DaveTurton]

Works a charm Deb, as usual. Congratulations on the Ninja title by the way.

 

[ganeshm]

hi web,


I believe hui "=IF(E6>0,INT((TODAY() - E6) / 365.25),"") " is right.


if i run macro, empty the date column it shows 113.


But if i apply 'if condition' and empty the date column. It works.

 

[ganeshm]

solved.

it works, now.


thank for all, who contributed to this post.

 

[oldchippy]

Hi ganeshm,


I also tried Hui's formula on 19/07/1951 (I'm more than twice has old as Dave!) against today's date 14/06/2013 and got


61 Years 11 Months 26 Days which is incorrect, using Debraj Roy's formula I get


61 years 10 months 26 days which is correct

 

[NARAYANK991]

Hi All ,


The formula posted by Deb works in probably every case except a few , but one should be aware of it.


Today being June 14 , what is the result expected for the same date in earlier months ? For example what should we get for May 14 , April 14 , March 14 , February 14 and January 14 ?


For February 14 , we get 3 months and 28 days ; probably 4 months might be what we expect.


This difference of a day or two exists for some dates ; probably because of the use of fractional values such as 365.25 , 30.4375.


Narayan

 

[oldchippy]

@Narayan,


There's always the DATEDIF function?


http://chandoo.org/forums/topic/how-to-calculate-age-from-their-dob

 

[SirJB7]

@olchippy

Hi!

Beware of that function...

http://chandoo.org/forums/topic/excel-2007#post-16879

Regards!

 

[oldchippy]

@SirJB7,


You've pointed me to that one before


Can the Ninja's come up with the ultimate formula for working out someone age?

 

[SirJB7]

@oldchippy

Hi!

Sorry for the redundancy but each time I read "that" function name a red alert light turns on and triggers the auto response event. And regarding age calculations...

http://chandoo.org/forums/topic/finding-ages#post-93752

Regards!

 

[NARAYANK991]

Hi oldchippy ,


As far as I know , the DATEDIF function gave problems in one application ; however , if you google this topic , you will see that almost every link uses this function.


http://dmcritchie.mvps.org/excel/datetime.htm


There is Chip Pearson's VBA procedure , which appears to be sound ; again , once we go back in time , it becomes that much more difficult to verify any algorithm for all possible start and end dates.


http://www.cpearson.com/excel/DateTimeVBA.htm


Narayan

 

[SirJB7]

@NARAYANK991

Hi!

Once proven that erratically and sporadically doesn't work, then it's use should be avoided from then on in the interest of safety, reliability and stability.

And I suppose that Redmond dumb&deaf guys responsible for that implementation neither acknowledged the non-working issues nor had the intention of solving it. It should be added too the exile from any Excel user documentation and help from version 2000, 13 years and 5 versions ago!, which leads to the conclusion that they still allow its use so as to don't make older and obsolete workbooks crash.

I wouldn't use anywhere any function whose behavior couldn't be predictable and I think that none of my users (either individuals or corporations) would approve me doing so. At least the serious ones.

Regards!