Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Hello All,
How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Use the DATEDIF function.
=DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
A1: 12/20/2005
A2: 12/20/1955 formual =year(a1)-year(a2) gives you 50. "AB" wrote: Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Chip,
Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
I guess I don't really understand your problem. If you have a
birthdate, the formula =DATEDIF(birth_date,TODAY(),"Y") will return the age in years. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Chip, Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Nikki,
Thanks. I tried that but it gave me 02/19/1900. Adam "Nikki" wrote: A1: 12/20/2005 A2: 12/20/1955 formual =year(a1)-year(a2) gives you 50. "AB" wrote: Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
you need to change your formatting for the resula cell so it won't give you a
date: change the formatting for 02/19/1900 cell by: format--cell--number--change to number "AB" wrote: Nikki, Thanks. I tried that but it gave me 02/19/1900. Adam "Nikki" wrote: A1: 12/20/2005 A2: 12/20/1955 formual =year(a1)-year(a2) gives you 50. "AB" wrote: Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Hi Chip,
I think I have it. I tried using the formula listed below however what stumps me is the follow: =DATEDIF(09/17/1943,TODAY(),"Y") I tried putting todays date in between the brackes next to TODAY but it di d not work. What am I doing wrong? Thanks again for all of your help. Adam "Chip Pearson" wrote: I guess I don't really understand your problem. If you have a birthdate, the formula =DATEDIF(birth_date,TODAY(),"Y") will return the age in years. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Chip, Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
In your formula,
=DATEDIF(09/17/1943,TODAY(),"Y") the / characters are treated as division operators, not date separators. Use something like =DATEDIF(DATE(1943,9,17),TODAY(),"y") or =DATEDIF("09/17/1943",TODAY(),"Y") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hi Chip, I think I have it. I tried using the formula listed below however what stumps me is the follow: =DATEDIF(09/17/1943,TODAY(),"Y") I tried putting todays date in between the brackes next to TODAY but it di d not work. What am I doing wrong? Thanks again for all of your help. Adam "Chip Pearson" wrote: I guess I don't really understand your problem. If you have a birthdate, the formula =DATEDIF(birth_date,TODAY(),"Y") will return the age in years. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Chip, Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Here is an example:
Here is the chart with the ages and their coresponding rates. Per $1000 of coverage <25 $1.20 25-29 $0.96 30-34 $1.20 35-39 $1.44 40-44 $1.80 45-49 $2.76 50-54 $4.68 55-59 $8.64 60-64 $13.08 65-69 $19.68 70+ $46.68 What I want to be able to do is the following DOB Age Rate 09/17/1943 ? $13.08/1000 I want Excel to be able to take the birth date calucalte the age then find the coresponding rate. Thanks. Adam Chip Pearson" wrote: I guess I don't really understand your problem. If you have a birthdate, the formula =DATEDIF(birth_date,TODAY(),"Y") will return the age in years. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Chip, Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Perfect. That worked. One last question. Can I set it up where I can
reference the cell that the date is in so that I can use the formula for the other birthdates and keep TODAY's date as an absolute? Thanks again for all of your help. Adam "Chip Pearson" wrote: In your formula, =DATEDIF(09/17/1943,TODAY(),"Y") the / characters are treated as division operators, not date separators. Use something like =DATEDIF(DATE(1943,9,17),TODAY(),"y") or =DATEDIF("09/17/1943",TODAY(),"Y") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hi Chip, I think I have it. I tried using the formula listed below however what stumps me is the follow: =DATEDIF(09/17/1943,TODAY(),"Y") I tried putting todays date in between the brackes next to TODAY but it di d not work. What am I doing wrong? Thanks again for all of your help. Adam "Chip Pearson" wrote: I guess I don't really understand your problem. If you have a birthdate, the formula =DATEDIF(birth_date,TODAY(),"Y") will return the age in years. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Chip, Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Question
Chip,
Thanks for all of your help. I figured it out and it worked. Adam "Chip Pearson" wrote: In your formula, =DATEDIF(09/17/1943,TODAY(),"Y") the / characters are treated as division operators, not date separators. Use something like =DATEDIF(DATE(1943,9,17),TODAY(),"y") or =DATEDIF("09/17/1943",TODAY(),"Y") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hi Chip, I think I have it. I tried using the formula listed below however what stumps me is the follow: =DATEDIF(09/17/1943,TODAY(),"Y") I tried putting todays date in between the brackes next to TODAY but it di d not work. What am I doing wrong? Thanks again for all of your help. Adam "Chip Pearson" wrote: I guess I don't really understand your problem. If you have a birthdate, the formula =DATEDIF(birth_date,TODAY(),"Y") will return the age in years. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Chip, Thanks for your response. I tried that but it did not do what I wanted it to. What I am trying to do is the following. I have a list of about 600 employees. I am trying to put together a formular where I can tell Excel to calculate the birthdate and then based on its results do a VLookup to see what the coresponding rate is and then have it pull that rate into a specified cell. For example, is Bob Smith was born on 12/20/1950, I need exce to recognize that he is 50 years of age. Then look at what rate corresponds to 50 years of age and then put it in Cell A4. Please let me know if this makes sense or if you need additional info. Thanks. Adam Chip Pearson" wrote: Use the DATEDIF function. =DATEDIF(start_date,end_date,"Y") For more info about DATEDIF, see www.cpearson.com/excel/datedif.htm . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "AB" wrote in message ... Hello All, How can I subtract dates to get whole numbers in Excel? I am trying to link DOB's with a chart that rates based on age. example: 12/20/2005 - 12/20/1955 = 50 years old. Any and all assistance is greatly appreciated. Thanks. Adam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel files in separate program windows | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
eXceL 2003 - list or combo box question | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |