Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date of next birthday
Perhaps a strange type of request however I have a list of 500 birthdates
and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date of next birthday
On 22/12/08 2:52 PM, in article , "Ken
Lumley" wrote: Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks Sorry all, I had a blank moment. The answer is relatively simple for anyone who may be interested: =((DATEDIF(F4,TODAY(),"y")+1)*365.25)+F4-30 where F4 contains the date of birth |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date of next birthday
I think you'll find that you'll often be a day out with that formula. As an
example, try it out with 1st Jan of various years as the DoB (and you may want to remove the -30 term for the test to make it clearer). Try =DATE(YEAR(F4)+(DATEDIF(F4,TODAY(),"y")+1),MONTH(F 4),DAY(F4))-30 -- David Biddulph "Ken Lumley" wrote in message ... On 22/12/08 2:52 PM, in article , "Ken Lumley" wrote: Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks Sorry all, I had a blank moment. The answer is relatively simple for anyone who may be interested: =((DATEDIF(F4,TODAY(),"y")+1)*365.25)+F4-30 where F4 contains the date of birth |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate the age from 2 dates
calcuate the age from 2 different dates
Ken Lumley wrote: Calculating Date of next birthday 21-Dec-08 Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks Previous Posts In This Thread: On Sunday, December 21, 2008 11:52 PM Ken Lumley wrote: Calculating Date of next birthday Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks On Monday, December 22, 2008 12:12 AM Ken Lumley wrote: Calculating Date of next birthday On 22/12/08 2:52 PM, in article , "Ken Lumley" wrote: Sorry all, I had a blank moment. The answer is relatively simple for anyone who may be interested: =((DATEDIF(F4,TODAY(),"y")+1)*365.25)+F4-30 where F4 contains the date of birth On Monday, December 22, 2008 1:33 AM David Biddulph wrote: I think you'll find that you'll often be a day out with that formula. I think you'll find that you'll often be a day out with that formula. As an example, try it out with 1st Jan of various years as the DoB (and you may want to remove the -30 term for the test to make it clearer). Try =DATE(YEAR(F4)+(DATEDIF(F4,TODAY(),"y")+1),MONTH(F 4),DAY(F4))-30 -- David Biddulph "Ken Lumley" wrote in message ... EggHeadCafe - Software Developer Portal of Choice RemoteSoft Decompiler,Obfuscator, Protector http://www.eggheadcafe.com/tutorials...mpilerobf.aspx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate the age from 2 dates
=IF(DATE(YEAR(A2),MONTH(A2),DAY(A2))TODAY(),
DATE(YEAR(TODAY()),MONTH(A2),DAY(A2)), DATE(YEAR(TODAY())+1,MONTH(A2),DAY(A2)))-30 -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) <s. suganthi wrote in message ... calcuate the age from 2 different dates Ken Lumley wrote: Calculating Date of next birthday 21-Dec-08 Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks Previous Posts In This Thread: On Sunday, December 21, 2008 11:52 PM Ken Lumley wrote: Calculating Date of next birthday Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks On Monday, December 22, 2008 12:12 AM Ken Lumley wrote: Calculating Date of next birthday On 22/12/08 2:52 PM, in article , "Ken Lumley" wrote: Sorry all, I had a blank moment. The answer is relatively simple for anyone who may be interested: =((DATEDIF(F4,TODAY(),"y")+1)*365.25)+F4-30 where F4 contains the date of birth On Monday, December 22, 2008 1:33 AM David Biddulph wrote: I think you'll find that you'll often be a day out with that formula. I think you'll find that you'll often be a day out with that formula. As an example, try it out with 1st Jan of various years as the DoB (and you may want to remove the -30 term for the test to make it clearer). Try =DATE(YEAR(F4)+(DATEDIF(F4,TODAY(),"y")+1),MONTH(F 4),DAY(F4))-30 -- David Biddulph "Ken Lumley" wrote in message ... EggHeadCafe - Software Developer Portal of Choice RemoteSoft Decompiler,Obfuscator, Protector http://www.eggheadcafe.com/tutorials...mpilerobf.aspx |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date of next birthday
I went a slightly different route.
This is based on putting the persons date of birth i.e. 1/1/1980 in B4 and depending on todays date working out if the birthday is this year or next and adding the appropriate year =IF(DATEVALUE((DAY(B4)&"/"&MONTH(B4)&"/"&YEAR(TODAY())))TODAY(),DAY(B4)&"/"&MONTH(B4)&"/"& (YEAR(TODAY())),DAY(B4)&"/"&MONTH(B4)&"/"&YEAR(TODAY())+1) If it works for you then grand. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date of next birthday
rolwey.stats wrote:
I went a slightly different route. Okay, but... the original post was 8 years ago. -- Goddammit grad school, you're making me overanalyze funny pictures of cats. |
#8
|
|||
|
|||
Quote:
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Date of next birthday
On Monday, 22 December 2008 08:52:48 UTC+4, Ken Lumley wrote:
Perhaps a strange type of request however I have a list of 500 birthdates and I need to calculate 30 days before the next birthday for each and display that as a date. I'm using excel 2003 for windows and excel 2004 for mac. I can calculate the age as at the next birthday in years and of course the current age. What I can't seem to figure out how to do is calculate the date of the next birthday. Any help would be most appreciated. Thanks =EDATE(A1,CEILING.MATH(YEARFRAC(A1,TODAY()),1)*12)-30 where A1 is the birthdate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating if today is someone's birthday | Excel Discussion (Misc queries) | |||
Calculating birthday - yet another newcomer!! | Excel Worksheet Functions | |||
Can anyone calculate age next birthday from date field? | Excel Worksheet Functions | |||
Calculating Age Based on Birthday | Excel Discussion (Misc queries) | |||
Birthday date formula? | Excel Worksheet Functions |