| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
convert date of birth to current age
is there an easy way to do this through a formula? I tried using the year
function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#2
|
|||
|
|||
|
Try this:
=ROUNDDOWN((TODAY()-A1)/365,0) A1 is where the Bdate is entered. HTH, Paul "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#3
|
|||
|
|||
|
=IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW() )),YEAR(NOW())-YEAR(A1),YE
AR(NOW())-YEAR(A1)-1) All on one line, watch out for email wordwrap.......... Vaya con Dios, Chuck, CABGx3 "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#4
|
|||
|
|||
|
Though I didn't really see the need to use such a complicated formula, your
formula was slightly off. The "-1" at the end appears to be in the wrong place. I've adjusted it below. =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW() )),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1)) "CLR" wrote in message ... =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW() )),YEAR(NOW())-YEAR(A1),YE AR(NOW())-YEAR(A1)-1) All on one line, watch out for email wordwrap.......... Vaya con Dios, Chuck, CABGx3 "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#5
|
|||
|
|||
|
Assume birthdate in A6 and today date in A7
=DATEDIF(A6,A7,"y") & "years," & DATEDIF(A6,A7,"ym") & "months,"& DATEDIF(A6,A7,"md") & "days," Watch for word wrap. Formula is one line. Gord Dibben Excel MVP On Tue, 19 Apr 2005 13:40:05 -0700, paralegal wrote: is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#6
|
|||
|
|||
|
Try your formula with a birthdate of 4/29/1920 and the current date of
4/19/2005. I get 85.03, which rounds down to 85. The person won't be 85 for another 10 days. Then try =DATEDIF(A1,TODAY(),"y") On Tue, 19 Apr 2005 17:17:18 -0400, "PCLIVE" wrote: Try this: =ROUNDDOWN((TODAY()-A1)/365,0) A1 is where the Bdate is entered. HTH, Paul "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#7
|
|||
|
|||
|
See my response to your first post for reasons why a more complicated formula
is needed. The AVERAGE number of days per year is 365.25, not 365, but you can't modify your formula to substitute 365.25 for 365, as it still gives errors when the current date is very close to the anniversary of the birthdate. On Tue, 19 Apr 2005 17:41:41 -0400, "PCLIVE" wrote: Though I didn't really see the need to use such a complicated formula, your formula was slightly off. The "-1" at the end appears to be in the wrong place. I've adjusted it below. =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW( ))),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1)) "CLR" wrote in message ... =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW() )),YEAR(NOW())-YEAR(A1),YE AR(NOW())-YEAR(A1)-1) All on one line, watch out for email wordwrap.......... Vaya con Dios, Chuck, CABGx3 "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#8
|
|||
|
|||
|
You are correct. Though I found and corrected the mistake in the formula
that was on the right track, I forgot to take into account that every year does not have 365 days. Thank you for pointing that out. Regards, Paul "Myrna Larson" wrote in message ... See my response to your first post for reasons why a more complicated formula is needed. The AVERAGE number of days per year is 365.25, not 365, but you can't modify your formula to substitute 365.25 for 365, as it still gives errors when the current date is very close to the anniversary of the birthdate. On Tue, 19 Apr 2005 17:41:41 -0400, "PCLIVE" wrote: Though I didn't really see the need to use such a complicated formula, your formula was slightly off. The "-1" at the end appears to be in the wrong place. I've adjusted it below. =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW ())),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1)) "CLR" wrote in message ... =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW() )),YEAR(NOW())-YEAR(A1),YE AR(NOW())-YEAR(A1)-1) All on one line, watch out for email wordwrap.......... Vaya con Dios, Chuck, CABGx3 "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
|
#9
|
|||
|
|||
|
Note that there is NO WAY to divide elapsed days by number of days in a year
and get a result that will increment the year EXACTLY on the anniversary date (i.e. not a day early or a day late). One needs specialized functions that deal with dates and the vagaries of the calendar to accomplish this. On Wed, 20 Apr 2005 09:51:12 -0400, "PCLIVE" wrote: You are correct. Though I found and corrected the mistake in the formula that was on the right track, I forgot to take into account that every year does not have 365 days. Thank you for pointing that out. Regards, Paul "Myrna Larson" wrote in message .. . See my response to your first post for reasons why a more complicated formula is needed. The AVERAGE number of days per year is 365.25, not 365, but you can't modify your formula to substitute 365.25 for 365, as it still gives errors when the current date is very close to the anniversary of the birthdate. On Tue, 19 Apr 2005 17:41:41 -0400, "PCLIVE" wrote: Though I didn't really see the need to use such a complicated formula, your formula was slightly off. The "-1" at the end appears to be in the wrong place. I've adjusted it below. =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NO W())),YEAR(NOW())-YEAR(A1)-1,YEAR(NOW())-YEAR(A1)) "CLR" wrote in message ... =IF(AND(MONTH(A1)=MONTH(NOW()),DAY(A1)=DAY(NOW() )),YEAR(NOW())-YEAR(A1),YE AR(NOW())-YEAR(A1)-1) All on one line, watch out for email wordwrap.......... Vaya con Dios, Chuck, CABGx3 "paralegal" wrote in message ... is there an easy way to do this through a formula? I tried using the year function and subtracting from current date but is doesn't caluculate correctly if the birthday hasn't yet occured in current year. Thank you. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting data from the current date | Excel Worksheet Functions | |||
| extract date from the most current date | Excel Discussion (Misc queries) | |||
| How to convert string to a date | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
| How do I convert a Julian date into a regular date? | Excel Discussion (Misc queries) | |||
Linear Mode
