Birthday calculations
Hi,
I am setting up a spreadsheet to calculate people's age and the number of days to their next birthday. Calculating their age I can do but calculating the number of days until their next birthday is proving a little difficult. I am currently using the formula =365-DATEDIF($C3,TODAY(),"yd") where the full dd/mm/yyyy birth date is in c3. It occurred to me that the formula will be correct for non leap years but will probably be one day out on leap years. I would welcome any suggestions on how to correct the above formula to compensate for leap years? -- Cheers . . . JC |
Birthday calculations
=DATE(YEAR(TODAY())+IF(TEXT(TODAY(),"mmdd")TEXT(C 3,"mmdd"),1,0),MONTH(C3),DAY(C3))-TODAY()
Works OK for leap years, depending on what your definition is of the birthdays in non-leap years for those born on Feb. 29 -- Kind regards, Niek Otten "JC" wrote in message ... Hi, I am setting up a spreadsheet to calculate people's age and the number of days to their next birthday. Calculating their age I can do but calculating the number of days until their next birthday is proving a little difficult. I am currently using the formula =365-DATEDIF($C3,TODAY(),"yd") where the full dd/mm/yyyy birth date is in c3. It occurred to me that the formula will be correct for non leap years but will probably be one day out on leap years. I would welcome any suggestions on how to correct the above formula to compensate for leap years? -- Cheers . . . JC |
Birthday calculations
Hi Niek,
It took a little while to puzzle your formula out but I now understand it. Many thanks for your assistance. I ran a few tests and found that it gives the correct answer if both birthdate and today() are in non leap years, if both birthdate and today() are in leap years or when the birthdate is in a non leap year and today() is a leap year. When the birthdate is in a leap year and today() is in a non leap year it is still giving correct answers except when the birthdate is 29th February. If the birthdate is 29th February it calculates as if the non leap year birthday is 1st March. Thus on the 25th February, 2006 for a 29th February 1996 birthdate it calculates that the next birthday will be in 4 days time. I now understand what you meant when you wrote "Works OK for leap years, depending on what your definition is of the birthdays in non-leap years for those born on Feb. 29". I have no experience with this - are birthdays for those born on 29th February celebrated on 28th February or 1st March in non leap years? JC On Sat, 25 Feb 2006 10:49:14 +0100, "Niek Otten" wrote: =DATE(YEAR(TODAY())+IF(TEXT(TODAY(),"mmdd")TEXT( C3,"mmdd"),1,0),MONTH(C3),DAY(C3))-TODAY() Works OK for leap years, depending on what your definition is of the birthdays in non-leap years for those born on Feb. 29 "JC" wrote in message ... Hi, I am setting up a spreadsheet to calculate people's age and the number of days to their next birthday. Calculating their age I can do but calculating the number of days until their next birthday is proving a little difficult. I am currently using the formula =365-DATEDIF($C3,TODAY(),"yd") where the full dd/mm/yyyy birth date is in c3. It occurred to me that the formula will be correct for non leap years but will probably be one day out on leap years. I would welcome any suggestions on how to correct the above formula to compensate for leap years? |
Birthday calculations
<I have no experience with this - are birthdays for those born on 29th
February celebrated on 28th February or 1st March in non leap years? This is what Wikipedia tells us: A person who was born on 29 February may be called a "leapling". In non-leap years they usually celebrate their birthday on 28 February or 1 March. -- Kind regards, Niek Otten "JC" wrote in message ... Hi Niek, It took a little while to puzzle your formula out but I now understand it. Many thanks for your assistance. I ran a few tests and found that it gives the correct answer if both birthdate and today() are in non leap years, if both birthdate and today() are in leap years or when the birthdate is in a non leap year and today() is a leap year. When the birthdate is in a leap year and today() is in a non leap year it is still giving correct answers except when the birthdate is 29th February. If the birthdate is 29th February it calculates as if the non leap year birthday is 1st March. Thus on the 25th February, 2006 for a 29th February 1996 birthdate it calculates that the next birthday will be in 4 days time. I now understand what you meant when you wrote "Works OK for leap years, depending on what your definition is of the birthdays in non-leap years for those born on Feb. 29". I have no experience with this - are birthdays for those born on 29th February celebrated on 28th February or 1st March in non leap years? JC On Sat, 25 Feb 2006 10:49:14 +0100, "Niek Otten" wrote: =DATE(YEAR(TODAY())+IF(TEXT(TODAY(),"mmdd")TEXT (C3,"mmdd"),1,0),MONTH(C3),DAY(C3))-TODAY() Works OK for leap years, depending on what your definition is of the birthdays in non-leap years for those born on Feb. 29 "JC" wrote in message ... Hi, I am setting up a spreadsheet to calculate people's age and the number of days to their next birthday. Calculating their age I can do but calculating the number of days until their next birthday is proving a little difficult. I am currently using the formula =365-DATEDIF($C3,TODAY(),"yd") where the full dd/mm/yyyy birth date is in c3. It occurred to me that the formula will be correct for non leap years but will probably be one day out on leap years. I would welcome any suggestions on how to correct the above formula to compensate for leap years? |
Birthday calculations
If you want to assume that leapling birthdays are celebrated on 28th feb in non leap years then =IF(TEXT(C3,"ddmm")<TEXT(NOW(),"ddmm"),EDATE(C3,( DATEDIF(C3,NOW(),"y")+1)*12)-TODAY(),0) EDATE is part of Analysis ToolPak add-in -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516469 |
Birthday calculations
As fate would have it, I was attempting to do basically the same thing as JC was asking for. So I stole that formula for my use, but Excel clanged up a dialog box with a change in it. It replaced a comma with a *. I have no clue as to why it would do this but it still seems to work anyway. If I can, I'll copy and paste Excel's dialog box formula, so here goes: =DATE(YEAR(TODAY())+IF(TEXT(TODAY()""mmdd"")TEXT( C 3,""mmdd"")*1,0),MONTH(C3),DAY(C3))-TODAY() For what it is worth; That "star" falls in place between the last ""mmdd"") and the 1,0). So far it hasn't seemed to throw a wrench into the job but I guess time will tell. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=516469 |
Birthday calculations
The formula is correct. Do you happen to have a system where the list
separator is a semicolon (;) instead of a comma (,)? -- Kind regards, Niek Otten "lsmft" wrote in message ... As fate would have it, I was attempting to do basically the same thing as JC was asking for. So I stole that formula for my use, but Excel clanged up a dialog box with a change in it. It replaced a comma with a *. I have no clue as to why it would do this but it still seems to work anyway. If I can, I'll copy and paste Excel's dialog box formula, so here goes: =DATE(YEAR(TODAY())+IF(TEXT(TODAY()""mmdd"")TEXT( C 3,""mmdd"")*1,0),MONTH(C3),DAY(C3))-TODAY() For what it is worth; That "star" falls in place between the last ""mmdd"") and the 1,0). So far it hasn't seemed to throw a wrench into the job but I guess time will tell. -- lsmft ------------------------------------------------------------------------ lsmft's Profile: http://www.excelforum.com/member.php...o&userid=30678 View this thread: http://www.excelforum.com/showthread...hreadid=516469 |
Birthday calculations
JC wrote
I have no experience with this - are birthdays for those born on 29th February celebrated on 28th February or 1st March in non leap years? Such people will usually only celebrate their birthday every 4 years. The disadvantages (getting fewer presents) is greatly outweighed by the advantage of surviving more than 300 normal years. -- Marc. |
Birthday calculations
On Sat, 25 Feb 2006 08:32:10 -0600, lsmft
wrote: As fate would have it, I was attempting to do basically the same thing as JC was asking for. So I stole that formula for my use, but Excel clanged up a dialog box with a change in it. It replaced a comma with a *. I have no clue as to why it would do this but it still seems to work anyway. If I can, I'll copy and paste Excel's dialog box formula, so here goes: =DATE(YEAR(TODAY())+IF(TEXT(TODAY()""mmdd"")TEXT( C3,""mmdd"")*1,0),MONTH(C3),DAY(C3))-TODAY() For what it is worth; That "star" falls in place between the last ""mmdd"") and the 1,0). So far it hasn't seemed to throw a wrench into the job but I guess time will tell. The correct formula is =DATE(YEAR(TODAY())+IF(TEXT(TODAY(),"mmdd")TEXT(C 3,"mmdd"),1,0),MONTH(C3),DAY(C3))-TODAY() You will note a few differences between it and your one - the use of one pair of 'double quotes' (i.e. "mmdd" not ""mmdd"") and a comma after TEXT(TODAY() and where your * is placed. Try copying the formula from this message and pasting it into your cell in Excel. You may have to make corrections for the column name and row number - I have my spreadsheet set out as Column Data A Last name B First name C Birth date D Calculated age E Days to next birthday I added a note re the assumption that the next birthday for those born on 29th Feb is 1st March in non leap years. I hope that this helps. -- Cheers . . . JC |
Birthday calculations
Hi JC, What do you expect the formula to return on 1st March (in a non leap year) for a 29th February birthdate? btw you could simpify to =DATE(YEAR(TODAY())+(TEXT(TODAY(),"mmdd")TEXT(C3, "mmdd")),MONTH(C3),DAY(C3))-TODAY() -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516469 |
Birthday calculations
On Sat, 25 Feb 2006 16:01:58 -0600, daddylonglegs
wrote: Hi JC, What do you expect the formula to return on 1st March (in a non leap year) for a 29th February birthdate? btw you could simpify to =DATE(YEAR(TODAY())+(TEXT(TODAY(),"mmdd")TEXT(C3 ,"mmdd")),MONTH(C3),DAY(C3))-TODAY() Hi Daddylonglegs, That depends on what the convention is for those born on 29th Feb. Personally, I would celebrate my birthday on 28th Feb in non leap years if I was born on 29th Feb thus keeping it in the same month so I would expect the formula to return 364 days on 1st March but others may celebrate their birthday on 1st March in which case 365 would be the answer. I haven't tried your simplified formula yet but don't see how the IF(TEXT(TODAY(),"mmdd")TEXT(C3,"mmdd"),1,0) part can be simplified to (TEXT(TODAY(),"mmdd")TEXT(C3,"mmdd")). That doesn't make sense to me. -- Cheers . . . JC |
All times are GMT +1. The time now is 04:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com