Matt Pinkston
asked on
Age between two dates
I have a spreadsheet where column b is the birthdate and I need to determine the age of the person relative to august 31 of 2011.
Cluskitt
=2011-YEAR(B1)-IF(MONTH(B1
Change the -IF to +IF
Or change the -1,0 to 1,0 in the if.
Either way should work
Or change the -1,0 to 1,0 in the if.
Either way should work
ASKER
it comes back with a werid date 1/12/1900
I was hoping for just how of the indivudal was as of 8/31/2011
I was hoping for just how of the indivudal was as of 8/31/2011
assuming you had DOB = 31-Aug-83 (A1) and compared against 31-Aug-11 (B1)
You get 28 years do =(B1-A1)/365 and format the cell as number 0 dp
remeber both columns (A1&B1) need to be in date format
alternatively you can also do :
=(DATE(2011,8,31)-A1)/365
You get 28 years do =(B1-A1)/365 and format the cell as number 0 dp
remeber both columns (A1&B1) need to be in date format
alternatively you can also do :
=(DATE(2011,8,31)-A1)/365
ASKER
8/31/2011 is just a know date for all it is not a column I need to know the age of a person as of 8/31/2011
=(DATE(2011,8,31)-A1)/365 will get you the result you require as long as you format the cell as number 0 dp....
where A1 = the cell where DOB is held for all
where A1 = the cell where DOB is held for all
=DATEDIF(B2,date(2011,8,31
for example. Format as Number if you see a strange date.
for example. Format as Number if you see a strange date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>"Doing a simple DATEDIF for year won't work, because it would return '12/31/2010' as 1 year old, when it should return 0."
did you test that? returns 0 for me.
did you test that? returns 0 for me.
My apologies. It does seem to work. I had the feeling that it didn't work before. I know it doesn't work for SQL and I was almost sure it didn't in excel. Maybe in some previous version?
Either way, your formula works properly, so I apologize.
Either way, your formula works properly, so I apologize.