There are number of way to calculate age in excel, below are the example to do calculation:
example 1:
=INT((today() - A1)/365)
example 2: Use DATEDIF() function
Note: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3, possible in some case this does not work.
Syntex : DATEDIF(start_date,end_date,unit)
unit :
"Y" : The number of complete years in the period.
"M" : The number of complete months in the period.
"D" : The number of days in the period.
"MD": The difference between the days in start_date and end_date. The months and years of the dates are ignored.
Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.
"YM": The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD": The difference between the days of start_date and end_date. The years of the dates are ignored.
Reference Link
=DATEDIF(A1,TODAY(),"Y") & " Years, " & DATEDIF(A1,TODAY(),"YM") & " Months, " & DATEDIF(A1,TODAY(),"MD") & " Days"
example 3: Use YEARFRAC()
SYNTEX : YEARFRAC(start_date, end_date, [basis])
YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.
Basis Day count basis
0 : US (NASD) 30/360
1 : Actual/actual
2 : Actual/360
3 : Actual/365
4 : European 30/360
=ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)
| A | B | C | |
|---|---|---|---|
| 1 | 8/18/1979 | ||
| 2 |
example 1:
=INT((today() - A1)/365)
example 2: Use DATEDIF() function
Note: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3, possible in some case this does not work.
Syntex : DATEDIF(start_date,end_date,unit)
unit :
"Y" : The number of complete years in the period.
"M" : The number of complete months in the period.
"D" : The number of days in the period.
"MD": The difference between the days in start_date and end_date. The months and years of the dates are ignored.
Important: We don't recommend using the "MD" argument, as there are known limitations with it. See the known issues section below.
"YM": The difference between the months in start_date and end_date. The days and years of the dates are ignored
"YD": The difference between the days of start_date and end_date. The years of the dates are ignored.
Reference Link
example 3: Use YEARFRAC()
SYNTEX : YEARFRAC(start_date, end_date, [basis])
YEARFRAC calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term.
Basis Day count basis
0 : US (NASD) 30/360
1 : Actual/actual
2 : Actual/360
3 : Actual/365
4 : European 30/360
=ROUNDDOWN(YEARFRAC(A1, TODAY(), 1), 0)
No comments:
Post a Comment
Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.