Calculating average of ages (written as datedif)

B

balena

New Member
Joined
Apr 17, 2023
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
Hi, I have all the ages written as differences between two dates (the date of an event and their birthday) and I would like to calculate their average, but I'm not sure if that's possible with the words "years" and "months" written into the formula. I'm completely new to Excel and would really appreciate the help. Thank you in advance!
 

Attachments

  • Screenshot 2023-04-17 at 13.11.40.png
    Screenshot 2023-04-17 at 13.11.40.png
    24.9 KB · Views: 16
  • Screenshot 2023-04-17 at 13.11.56.png
    Screenshot 2023-04-17 at 13.11.56.png
    35.8 KB · Views: 13

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Welcome to the forum, perhaps the below will help:
Book1
AUVWXY
212/08/198317/04/202339839 years, 8 months
313/08/198318/04/202339839 years, 8 months
414/08/198019/04/202342842 years, 8 months
515/08/198320/04/202339839 years, 8 months
616/04/198321/04/202340040 years, 0 months
717/08/197622/04/202346846 years, 8 months
818/08/198323/04/202339839 years, 8 months
919/08/194224/04/202380880 years, 8 months
1020/08/198325/04/202339839 years, 8 months
1121/08/198326/04/202339839 years, 8 months
12Average44.27.244 years, 7 months
Sheet1
Cell Formulas
RangeFormula
V2:V11V2=DATEDIF(A2:A11,U2:U11,"y")
W2:W11W2=DATEDIF(A2:A11,U2:U11,"ym")
Y2:Y12Y2=LET(y,DATEDIF(A2:A11,U2:U11,"y"),m,DATEDIF(A2:A11,U2:U11,"ym"),VSTACK(y&" years, " & m& " months",ROUND(AVERAGE(y),0) & " years, "& ROUND(AVERAGE(m),0)&" months"))
V12:W12V12=AVERAGE(V2#)
Dynamic array formulas.
 
Upvote 1
Solution
Georgiboy said:
Welcome to the forum, perhaps the below will help:
Book1
AUVWXY
212/08/198317/04/202339839 years, 8 months
313/08/198318/04/202339839 years, 8 months
414/08/198019/04/202342842 years, 8 months
515/08/198320/04/202339839 years, 8 months
616/04/198321/04/202340040 years, 0 months
717/08/197622/04/202346846 years, 8 months
818/08/198323/04/202339839 years, 8 months
919/08/194224/04/202380880 years, 8 months
1020/08/198325/04/202339839 years, 8 months
1121/08/198326/04/202339839 years, 8 months
12Average44.27.244 years, 7 months
Sheet1
Cell Formulas
RangeFormula
V2:V11V2=DATEDIF(A2:A11,U2:U11,"y")
W2:W11W2=DATEDIF(A2:A11,U2:U11,"ym")
Y2:Y12Y2=LET(y,DATEDIF(A2:A11,U2:U11,"y"),m,DATEDIF(A2:A11,U2:U11,"ym"),VSTACK(y&" years, " & m& " months",ROUND(AVERAGE(y),0) & " years, "& ROUND(AVERAGE(m),0)&" months"))
V12:W12V12=AVERAGE(V2#)
Dynamic array formulas.
Click to expand...
Thank you so much for your help!
 
Upvote 0
As far as I can tell this works too.

20230417 Average Age Calc balena.xlsx
EFGHVWXYZAA
1Start DateEnd DateOverall average
212/08/198317/04/202344 Years, 9 Months
313/08/198318/04/2023
414/08/198019/04/2023
515/08/198320/04/2023
616/04/198321/04/2023
717/08/197622/04/2023
818/08/198323/04/2023
919/08/194224/04/2023
1020/08/198325/04/2023
1121/08/198326/04/2023
My Test (2)
Cell Formulas
RangeFormula
AA2AA2=LET(avgMths,AVERAGE(((YEAR(Y2:Y11)-YEAR(E2:E11))*12+(MONTH(Y2:Y11)-MONTH(E2:E11)))), Yrs, INT(avgMths/12), Mths,INT(MOD(avgMths/12,1)*12), Yrs & " Years, " & Mths & " Months")
 
Upvote 1
Alex Blakenburg said:
As far as I can tell this works too.

20230417 Average Age Calc balena.xlsx
EFGHVWXYZAA
1Start DateEnd DateOverall average
212/08/198317/04/202344 Years, 9 Months
313/08/198318/04/2023
414/08/198019/04/2023
515/08/198320/04/2023
616/04/198321/04/2023
717/08/197622/04/2023
818/08/198323/04/2023
919/08/194224/04/2023
1020/08/198325/04/2023
1121/08/198326/04/2023
My Test (2)
Cell Formulas
RangeFormula
AA2AA2=LET(avgMths,AVERAGE(((YEAR(Y2:Y11)-YEAR(E2:E11))*12+(MONTH(Y2:Y11)-MONTH(E2:E11)))), Yrs, INT(avgMths/12), Mths,INT(MOD(avgMths/12,1)*12), Yrs & " Years, " & Mths & " Months")
Click to expand...
Thank you!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Analytics Project - Sumifs
Replies
1
Views
250
bobaol
B
C
  • Question
Conditional Formatting based on age range
2
Replies
17
Views
961
CMW
C
Y
  • Question
How to have a formula auto update based off the number of filled rows.
Replies
0
Views
208
Yaeger38
Y
M
  • Solved
Get values from specific date ranges
Replies
5
Views
357
kevin9999
K
B
  • Question
VBA to save as 'Excel Workbook' (not Macro enabled)
Replies
4
Views
279
frabulator
frabulator

Forum statistics

Threads
1,215,219
Messages
6,123,691
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top