Prev: Function similar to "LEFT"Next: SUM From: WilDeliver on 15 Apr 2010 16:59 I need to calculate the interval between a date next year and a person's next birthday after that. Sorry if this has been answered, but I can't find it. Many thanks. From: Roger Govier on 15 Apr 2010 18:46 Hi Take a look at the undocumented Datedif function. Chip Pearson has information on his site http://www.cpearson.com/Excel/datedif.aspx -- Regards Roger Govier WilDeliver wrote:> I need to calculate the interval between a date next year and a person's next > birthday after that. Sorry if this has been answered, but I can't find it. > Many thanks. > > From: Gord Dibben on 15 Apr 2010 19:13 Interval in what units? Days? Months? See Chip Pearson's site for the undocumented DateDif function. http://www.cpearson.com/excel/datedif.aspx Gord Dibben MS Excel MVP On Thu, 15 Apr 2010 13:59:01 -0700, WilDeliver wrote: >I need to calculate the interval between a date next year and a person's next >birthday after that. Sorry if this has been answered, but I can't find it. >Many thanks. > From: Chip Pearson on 16 Apr 2010 08:37 I'm not sure exactly what you want, but consider the following. Suppose in cell A1 you have some date next year, and in B1 you have the person's date of birth. The following formula will calculate the person's birthday after the value in A1. =DATE(YEAR(A1)+(A1>DATE(YEAR(A1),MONTH(B1),DAY(B1))),MONTH(B1),DAY(B1)) So, for example, if A1 = 15-May-2011 and B1 = 2-Sept-1966, the formula returns 2-Sept-2011, the first birthday after 15-May-2011. If A1 = 15-December-2011, the formula will return 2-September-2012, the next birthday after 15-December-2011. If you want to calculate the number of days between A1 and the person's next birthday after the date in A1, use =DATE(YEAR(A1)+(A1>DATE(YEAR(A1),MONTH(B1),DAY(B1))),MONTH(B1),DAY(B1))-A1 For example, if A1 = 15-December-2011, the formula will return 262, the number of days between 15-December-2011 and 2-September-2012. If this is not what you want, please post back with considerably more detail. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 15 Apr 2010 13:59:01 -0700, WilDeliver wrote: >I need to calculate the interval between a date next year and a person's next >birthday after that. Sorry if this has been answered, but I can't find it. >Many thanks. >  |  Pages: 1 Prev: Function similar to "LEFT"Next: SUM