Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
I am trying to calculate the age of a person using the current "today's" date
and their birthdate without typing today's date into a cell. I do not need the current date in a cell, due to having it in a footer. My thinking is that it should look something like {=year(today)-year(birthdate)} I cannot get this to work. I have their birthdates typed into a cell, but do not have the current date. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
See this:
http://cpearson.com/excel/datedif.htm Biff "NCCDRLEE" wrote in message ... I am trying to calculate the age of a person using the current "today's" date and their birthdate without typing today's date into a cell. I do not need the current date in a cell, due to having it in a footer. My thinking is that it should look something like {=year(today)-year(birthdate)} I cannot get this to work. I have their birthdates typed into a cell, but do not have the current date. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Hi
If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 If you require the age split out into years, months days then follow Biff's lead to Datedif, but do take note of some of its vagaries with certain dates. -- Regards Roger Govier "NCCDRLEE" wrote in message ... I am trying to calculate the age of a person using the current "today's" date and their birthdate without typing today's date into a cell. I do not need the current date in a cell, due to having it in a footer. My thinking is that it should look something like {=year(today)-year(birthdate)} I cannot get this to work. I have their birthdates typed into a cell, but do not have the current date. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Anyone who is interested in C. Pearson's link and wonders if it is faster to use TODAY( ) instead of NOW( ) in the age calculating formula, here is a thread for additional information.
http://groups.google.ca/group/micros...c4fb16 323022 Epinn "Biff" wrote in message ... See this: http://cpearson.com/excel/datedif.htm Biff "NCCDRLEE" wrote in message ... I am trying to calculate the age of a person using the current "today's" date and their birthdate without typing today's date into a cell. I do not need the current date in a cell, due to having it in a footer. My thinking is that it should look something like {=year(today)-year(birthdate)} I cannot get this to work. I have their birthdates typed into a cell, but do not have the current date. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
"Roger Govier" wrote in message
... "NCCDRLEE" wrote in message ... I am trying to calculate the age of a person using the current "today's" date and their birthdate without typing today's date into a cell. I do not need the current date in a cell, due to having it in a footer. My thinking is that it should look something like {=year(today)-year(birthdate)} I cannot get this to work. I have their birthdates typed into a cell, but do not have the current date. If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). If you require the age split out into years, months days then follow Biff's lead to Datedif, but do take note of some of its vagaries with certain dates. -- David Biddulph |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Hi David
You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) =INT(YEARFRAC(birthday,today(),3)) (from the Analysis Toolpak, will also give the wrong result because of leap years) so maybe the best for whole year ages would be =INT((Today()-Birthday)/365.25) -- Regards Roger Govier "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... "NCCDRLEE" wrote in message ... I am trying to calculate the age of a person using the current "today's" date and their birthdate without typing today's date into a cell. I do not need the current date in a cell, due to having it in a footer. My thinking is that it should look something like {=year(today)-year(birthdate)} I cannot get this to work. I have their birthdates typed into a cell, but do not have the current date. If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). If you require the age split out into years, months days then follow Biff's lead to Datedif, but do take note of some of its vagaries with certain dates. -- David Biddulph |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
"Roger Govier" wrote in message
... "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... .... If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). Hi David You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) You've confused me there, Roger. I can't see why that should be the case (as the calculation of the month doesn't come into my formula), and when I test it with birthdays in October it shows the age changing for dates on or before 12th October, as I would expect. The only situation where I can foresee that there might be a difficulty is around leap years, with the question of when someone born on 29th Feb celebrates their birthday (without resorting to the Pirates of Penzance!). -- David Biddulph |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Hi David
Don't know what's going on here. When I enter 11/10/1944 in A1 =YEAR(TODAY()-(A1))-1900 returns 61 30/09/1944 returns 62 but all October dates return 61 -- Regards Roger Govier "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... ... If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). Hi David You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) You've confused me there, Roger. I can't see why that should be the case (as the calculation of the month doesn't come into my formula), and when I test it with birthdays in October it shows the age changing for dates on or before 12th October, as I would expect. The only situation where I can foresee that there might be a difficulty is around leap years, with the question of when someone born on 29th Feb celebrates their birthday (without resorting to the Pirates of Penzance!). -- David Biddulph |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
I see what you mean, but are you sure that it's all dates in October, and
not just yesterday (11th Oct)? Doesn't 9/10/44 or 10/10/44 give 62? I get the impression that we may just be a day adrift for some years, and my guess is that it's tied up with leap years, but you're right that my simple formula isn't 100% reliable. Thanks for pointing it out. -- David Biddulph "Roger Govier" wrote in message ... Hi David Don't know what's going on here. When I enter 11/10/1944 in A1 =YEAR(TODAY()-(A1))-1900 returns 61 30/09/1944 returns 62 but all October dates return 61 -- Regards Roger Govier "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... ... If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). Hi David You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) You've confused me there, Roger. I can't see why that should be the case (as the calculation of the month doesn't come into my formula), and when I test it with birthdays in October it shows the age changing for dates on or before 12th October, as I would expect. The only situation where I can foresee that there might be a difficulty is around leap years, with the question of when someone born on 29th Feb celebrates their birthday (without resorting to the Pirates of Penzance!). -- David Biddulph |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Hi David
Just tried it again and it returns 62 up to 10 Oct 1944 and 61 thereafter. Not consistent with what I was seeing before. I set up a table of dates from 30/09/44 through 11/11/44 down column A, then filled through for 12 columns so I have from 30/09/44 to 11/11/56. Copied the formula across as appropriate and then switch from 1 year to one lower year seems to occur after 11/10 for years 44 and 45, 12/10 for year 46 and 47, back to 11/10 years 48 and 49 and continues this pattern of switching after every pair of years It must be something to do with leap year and Rounding in Excel's calculation. I'm not going to give it any more thought!!!! I have long since discovered that there is no foolproof method of consistent calculation of date intervals other than the straight subtraction of one date from another and returning the number of days. "Sod" was obviously alive and well when our planetary system was formed giving rise to a cycle around the Sun that is not an exact number of days, let alone weeks and with a calendar devised which incorporates such a disparate set of day ranges, how on earth can anyone expect to get it right<vbg -- Regards Roger Govier "David Biddulph" wrote in message ... I see what you mean, but are you sure that it's all dates in October, and not just yesterday (11th Oct)? Doesn't 9/10/44 or 10/10/44 give 62? I get the impression that we may just be a day adrift for some years, and my guess is that it's tied up with leap years, but you're right that my simple formula isn't 100% reliable. Thanks for pointing it out. -- David Biddulph "Roger Govier" wrote in message ... Hi David Don't know what's going on here. When I enter 11/10/1944 in A1 =YEAR(TODAY()-(A1))-1900 returns 61 30/09/1944 returns 62 but all October dates return 61 -- Regards Roger Govier "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... ... If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). Hi David You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) You've confused me there, Roger. I can't see why that should be the case (as the calculation of the month doesn't come into my formula), and when I test it with birthdays in October it shows the age changing for dates on or before 12th October, as I would expect. The only situation where I can foresee that there might be a difficulty is around leap years, with the question of when someone born on 29th Feb celebrates their birthday (without resorting to the Pirates of Penzance!). -- David Biddulph |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Do you want to know what function I prefer?
I still like DATEDIF even though I don't need the number of months and the number of days. =DATEDIF(A1,TODAY(),"y") I tested this formula on July which has passed, on December which is to happen and even on 10/12/2005. The formula passed all three tests and I got one year for October 12, 2005 (birthdate). I hope MS won't take DATEDIF away in the future. Last night, I was wondering whether it is faster to use DATEDIF or to just do date2 - date1. I think I should give my brain a rest. Epinn "Roger Govier" wrote in message ... Hi David Just tried it again and it returns 62 up to 10 Oct 1944 and 61 thereafter. Not consistent with what I was seeing before. I set up a table of dates from 30/09/44 through 11/11/44 down column A, then filled through for 12 columns so I have from 30/09/44 to 11/11/56. Copied the formula across as appropriate and then switch from 1 year to one lower year seems to occur after 11/10 for years 44 and 45, 12/10 for year 46 and 47, back to 11/10 years 48 and 49 and continues this pattern of switching after every pair of years It must be something to do with leap year and Rounding in Excel's calculation. I'm not going to give it any more thought!!!! I have long since discovered that there is no foolproof method of consistent calculation of date intervals other than the straight subtraction of one date from another and returning the number of days. "Sod" was obviously alive and well when our planetary system was formed giving rise to a cycle around the Sun that is not an exact number of days, let alone weeks and with a calendar devised which incorporates such a disparate set of day ranges, how on earth can anyone expect to get it right<vbg -- Regards Roger Govier "David Biddulph" wrote in message ... I see what you mean, but are you sure that it's all dates in October, and not just yesterday (11th Oct)? Doesn't 9/10/44 or 10/10/44 give 62? I get the impression that we may just be a day adrift for some years, and my guess is that it's tied up with leap years, but you're right that my simple formula isn't 100% reliable. Thanks for pointing it out. -- David Biddulph "Roger Govier" wrote in message ... Hi David Don't know what's going on here. When I enter 11/10/1944 in A1 =YEAR(TODAY()-(A1))-1900 returns 61 30/09/1944 returns 62 but all October dates return 61 -- Regards Roger Govier "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... ... If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). Hi David You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) You've confused me there, Roger. I can't see why that should be the case (as the calculation of the month doesn't come into my formula), and when I test it with birthdays in October it shows the age changing for dates on or before 12th October, as I would expect. The only situation where I can foresee that there might be a difficulty is around leap years, with the question of when someone born on 29th Feb celebrates their birthday (without resorting to the Pirates of Penzance!). -- David Biddulph |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formulas
Further test with my formula ......
10/15/2005 yields 0 whereas 10/15/2004 yields 1. I think this is acceptable. I won't go into leap year here as I started a thread on DATEDIF and leap year yesterday. Thank you for reading. Epinn "Epinn" wrote in message ... Do you want to know what function I prefer? I still like DATEDIF even though I don't need the number of months and the number of days. =DATEDIF(A1,TODAY(),"y") I tested this formula on July which has passed, on December which is to happen and even on 10/12/2005. The formula passed all three tests and I got one year for October 12, 2005 (birthdate). I hope MS won't take DATEDIF away in the future. Last night, I was wondering whether it is faster to use DATEDIF or to just do date2 - date1. I think I should give my brain a rest. Epinn "Roger Govier" wrote in message ... Hi David Just tried it again and it returns 62 up to 10 Oct 1944 and 61 thereafter. Not consistent with what I was seeing before. I set up a table of dates from 30/09/44 through 11/11/44 down column A, then filled through for 12 columns so I have from 30/09/44 to 11/11/56. Copied the formula across as appropriate and then switch from 1 year to one lower year seems to occur after 11/10 for years 44 and 45, 12/10 for year 46 and 47, back to 11/10 years 48 and 49 and continues this pattern of switching after every pair of years It must be something to do with leap year and Rounding in Excel's calculation. I'm not going to give it any more thought!!!! I have long since discovered that there is no foolproof method of consistent calculation of date intervals other than the straight subtraction of one date from another and returning the number of days. "Sod" was obviously alive and well when our planetary system was formed giving rise to a cycle around the Sun that is not an exact number of days, let alone weeks and with a calendar devised which incorporates such a disparate set of day ranges, how on earth can anyone expect to get it right<vbg -- Regards Roger Govier "David Biddulph" wrote in message ... I see what you mean, but are you sure that it's all dates in October, and not just yesterday (11th Oct)? Doesn't 9/10/44 or 10/10/44 give 62? I get the impression that we may just be a day adrift for some years, and my guess is that it's tied up with leap years, but you're right that my simple formula isn't 100% reliable. Thanks for pointing it out. -- David Biddulph "Roger Govier" wrote in message ... Hi David Don't know what's going on here. When I enter 11/10/1944 in A1 =YEAR(TODAY()-(A1))-1900 returns 61 30/09/1944 returns 62 but all October dates return 61 -- Regards Roger Govier "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... "David Biddulph" wrote in message ... "Roger Govier" wrote in message ... ... If you only want the number of years, then you were almost there with your formula =YEAR(TODAY())-YEAR(A1) where A1 is holding the birthdate. You need to format the cell with the formula as General FormatCellsNumberGeneral otherwise an age of say 44 would show as 13/02/1900 That would give the age which will be achieved by the end of this year, rather than the more usual usage which is the number of whole years already achieved by today's date, which you could get by =YEAR(TODAY()-(A1))-1900 (formatted as number with no decimal places). Hi David You are absolutely correct. I had tested using my own birthday, which has already occurred this year. However, your formula will also give the wrong result when the month today() is the same as the month(birthday) You've confused me there, Roger. I can't see why that should be the case (as the calculation of the month doesn't come into my formula), and when I test it with birthdays in October it shows the age changing for dates on or before 12th October, as I would expect. The only situation where I can foresee that there might be a difficulty is around leap years, with the question of when someone born on 29th Feb celebrates their birthday (without resorting to the Pirates of Penzance!). -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Date formulas needed | Excel Discussion (Misc queries) | |||
Min/Max formulas using cells with date format | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |