Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating Age
If I have a person's birthday, how can I calculate their current age based on
today's date? Thanks! |
#2
|
|||
|
|||
You can use the DATEDIF function. E.g.,
=DATEDIF(birthdate,TODAY(),"y") See www.cpearson.com/excel/datedif.htm for more information about DATEDIF. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "devinm21" wrote in message ... If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! |
#3
|
|||
|
|||
"devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! |
#4
|
|||
|
|||
"ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1),1,IF(DAY(NOW())<DAY(A1),1,0))) |
#5
|
|||
|
|||
On Thu, 9 Jun 2005 04:54:09 -0500, "Vic Sowers"
wrote: "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: Somewhat shorter formula: =DATEDIF(A1,TODAY(),"y") --ron |
#6
|
|||
|
|||
There's been a big discussion on this already.
http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#7
|
|||
|
|||
My version of Excel does not have the "DATEIF" function. I was bale to cut
and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#8
|
|||
|
|||
Then you simply need to subtract the earlier date from the recent, and
format as number. =A1-B1 Mangesh "junepbug" wrote in message ... My version of Excel does not have the "DATEIF" function. I was bale to cut and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#9
|
|||
|
|||
It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change the
formating but it's not coming up correctly. Is there anything else I can do? "Mangesh Yadav" wrote: Then you simply need to subtract the earlier date from the recent, and format as number. =A1-B1 Mangesh "junepbug" wrote in message ... My version of Excel does not have the "DATEIF" function. I was bale to cut and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#10
|
|||
|
|||
Could you post your data.
And the formula you are using. Mangesh "junepbug" wrote in message ... It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change the formating but it's not coming up correctly. Is there anything else I can do? "Mangesh Yadav" wrote: Then you simply need to subtract the earlier date from the recent, and format as number. =A1-B1 Mangesh "junepbug" wrote in message ... My version of Excel does not have the "DATEIF" function. I was bale to cut and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#11
|
|||
|
|||
=DATEDIF(I2,(H2),"y") & " years, " & DATEDIF(I2,(H2),"ym") & "
months, " & DATEDIF(I2,(H2),"md") & " days" In this case I2 = 11/2/1964 and H2 = 5/2/1996. Formatting cells as a 2 decimal place number fills in "31 years, 6 months, 0 days". Formatting any other way doesn't return an age. There was a way to do a "yearif" formula, however since I have had an upgrade I cannot find that formula. The "yearif" returned 31.50. Thanks! "Mangesh Yadav" wrote: Could you post your data. And the formula you are using. Mangesh "junepbug" wrote in message ... It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change the formating but it's not coming up correctly. Is there anything else I can do? "Mangesh Yadav" wrote: Then you simply need to subtract the earlier date from the recent, and format as number. =A1-B1 Mangesh "junepbug" wrote in message ... My version of Excel does not have the "DATEIF" function. I was bale to cut and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#12
|
|||
|
|||
Ok. So you are looking for the YEARFRAC function.
=YEARFRAC(I2,H2) which returns 31.5 Mangesh "junepbug" wrote in message ... =DATEDIF(I2,(H2),"y") & " years, " & DATEDIF(I2,(H2),"ym") & " months, " & DATEDIF(I2,(H2),"md") & " days" In this case I2 = 11/2/1964 and H2 = 5/2/1996. Formatting cells as a 2 decimal place number fills in "31 years, 6 months, 0 days". Formatting any other way doesn't return an age. There was a way to do a "yearif" formula, however since I have had an upgrade I cannot find that formula. The "yearif" returned 31.50. Thanks! "Mangesh Yadav" wrote: Could you post your data. And the formula you are using. Mangesh "junepbug" wrote in message ... It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change the formating but it's not coming up correctly. Is there anything else I can do? "Mangesh Yadav" wrote: Then you simply need to subtract the earlier date from the recent, and format as number. =A1-B1 Mangesh "junepbug" wrote in message ... My version of Excel does not have the "DATEIF" function. I was bale to cut and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
#13
|
|||
|
|||
Forgot to mention that you need the Analysis Toolpak to use the YEARFRAC
function. Mangesh "junepbug" wrote in message ... =DATEDIF(I2,(H2),"y") & " years, " & DATEDIF(I2,(H2),"ym") & " months, " & DATEDIF(I2,(H2),"md") & " days" In this case I2 = 11/2/1964 and H2 = 5/2/1996. Formatting cells as a 2 decimal place number fills in "31 years, 6 months, 0 days". Formatting any other way doesn't return an age. There was a way to do a "yearif" formula, however since I have had an upgrade I cannot find that formula. The "yearif" returned 31.50. Thanks! "Mangesh Yadav" wrote: Could you post your data. And the formula you are using. Mangesh "junepbug" wrote in message ... It comes up as 5545 instead of 58 yrs,7 mos. (58.58). I tried to change the formating but it's not coming up correctly. Is there anything else I can do? "Mangesh Yadav" wrote: Then you simply need to subtract the earlier date from the recent, and format as number. =A1-B1 Mangesh "junepbug" wrote in message ... My version of Excel does not have the "DATEIF" function. I was bale to cut and paste the formula in, and it works fine. However it dispays the results as Xyears, Xmonths, Xdays. I used to have it displayed as a four digit (##.##) number, which made finding the average age eay. Any suggestions on how to get that format back? "Mangesh Yadav" wrote: There's been a big discussion on this already. http://excelforum.com/showthread.php...light=bluenose Mangesh "Vic Sowers" wrote in message ... "ertug" wrote in message ... "devinm21" wrote: If I have a person's birthday, how can I calculate their current age based on today's date? Thanks! In whole years: =YEAR(NOW())-YEAR(A1)-IF(MONTH(NOW())MONTH(A1),0,IF(MONTH(NOW())MONTH( A1), 1,IF(DAY(NOW())<DAY(A1),1,0))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating growth with negative numbers | Excel Worksheet Functions | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Formulas not calculating??? | Excel Discussion (Misc queries) | |||
calculating averages | Excel Worksheet Functions | |||
Periodic Calculating | Excel Worksheet Functions |