Age calculation in Text Format, in year, month and day.

[harzer]

I am facing a problem despite my research on the net, I confess that I can not find it.
Namely that I am unable to handle array variables correctly, which complicates my task.

I have a large amount of row data, in 10 columns, the ones that concern the present case, are columns E and H.
for now, column E is empty.
Column H contains dates of birth.
How to proceed, either by table or table combined with dictionary to indicate the age in: year, month and days in column E, according to the current date.
The goal is to have the result in text format, (Example: 15y 5m 10d), I thought of making a call to a function which would give the result just in text so as not to use formulas (No formulas) Please .
Unless I am mistaken, the desired result for the first 10 lines is in column L, based on the date that the calculation was made is: 30-01-2023
Thank you very much in advance for your suggestions.
PS: I completely emptied my table and I simply left the two columns concerned so that you can (eventually) test your code.

CalculAge1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					Age			Né(e)				Age
2								16-03-1957				65 a 10 m 14 j
3								17-04-2012				10 a 9 m 13 j
4								14-04-2012				10 a 9 m 16 j
5								25-03-1966				56 a 10 m 5 j
6								30-05-1959				63 a 8 m 0 j
7								17-02-1962				60 a 11 m 13 j
8								22-01-1955				68 a 0 m 8 j
9								15-02-1961				61 a 11 m 15 j
10								18-04-2012				10 a 9 m 12 j
11								1-01-2010				13 a 0 m 29 j
12								22-03-2019
13								15-04-2007
14								15-04-2007
15								15-04-2007
16								16-04-2007
17								15-04-2008
18								15-04-2008
19								15-04-2008
20								15-04-2008
21								15-04-2008
22								15-04-2008
23								15-04-2008
24								15-05-2008
25								15-04-2008
Parents

 

[James006]

By the way, just out of curiosity ...

What is precisely the formula you are currently working with ? say, in cell L2 ...

 

[James006]

For your info, the macro CalcAges was tested and re-tested with many different Date formats for column H ... with no problem ...

 

[harzer]

Hello Alex Blankenburg and James006,
Waw, Bravo and thank you both, your two proposals meet my expectations and are executed quickly.
My proposal works line by line, it's slower!
An additional thank you to James006 because he has been present since the beginning of my request.
Sincerely.

 

[James006]

Thanks a lot for your Thanks

Just out of curiosity, what is precisely the formula you are currently working with ? say, in cell L2

 

[harzer]

Big thanks

 

[Alex Blakenburg]

As far as speed is concerned
arr(i) = Evaluate("=DATEDIF(" & CLng(Cells(i + 2, 8).Value) & ",TODAY(),""y"")&"" a ""&DATEDIF(" & CLng(Cells(i + 2, 8).Value) & ",TODAY(),""ym"")&"" m ""&DATEDIF(" & CLng(Cells(i + 2, 8).Value)

I didn't get an obvious errors but it is generally not advisable to use the "md" version of DateDif.
Per Microsoft here: DATEDIF function - Microsoft Support

Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.

@harzer in checking for issue with md in James's code I had a look at the actual calculation you were using. I can see that it comes from Chip (Charles) Pearon's site but it would seem to have an error in it.
I believe that this line should not have a "+ 1" in the month section. We want the last day of the previous month not of the current month.
Happy to be proven wrong on this, if someone wants to confirm.

' Currently
'D = Day(DateSerial(Year(DateCurrent), Month(DateCurrent) + 1, 0)) + D + 1
' Should be
D = Day(DateSerial(Year(DateCurrent), Month(DateCurrent), 0)) + D + 1

PS: the + 1 at the very end is your call, by adding 1 to the end it is including the Start Date as well as the End Date.

 

[harzer]

Hello,
I took note of your remark and I put the old line in comment and I implemented yours.
I would see by testing, if problem, I put in application the code that I put in comment. The one with the (+1).
Cheers.

 

[Alex Blakenburg]

Let us know how you go. I am off to bed. Its quite late in Australia.

 

[harzer]

Hello,
I tested your code, it works well and I am satisfied, thank you and congratulations.

 

[Alex Blakenburg]

Thanks for letting me know. Glad we could help.