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 <elsewhere(a)anothertime.com>
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
<elsewhere(a)anothertime.com> 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