I have a date in cell d6 and a number representing a day in cell d4.
For example, the date is 03/15/2009 in d6 and the number in d4 is 20.
I need a formula to return the next occurence of the 20th from the date in
d6. In the example above the next occurence would be 3/20/2009. If the date
were 12/22/2009, the formula should return 1/20/2010. If the date is on the
number in d4 (20th), for example 05/20/2009, the formula should return
5/20/2009. Any help is greatly appreciated!

Thanks in advance.

=IF(D4=DAY(D6),DATE(YEAR(D6),MONTH(D6),D4),DATE(Y EAR(D6),MONTH(D6)+1,D4))
--
David Biddulph

"Gretchster" wrote in message
...
I have a date in cell d6 and a number representing a day in cell d4.
For example, the date is 03/15/2009 in d6 and the number in d4 is 20.
I need a formula to return the next occurence of the 20th from the date in
d6. In the example above the next occurence would be 3/20/2009. If the
date
were 12/22/2009, the formula should return 1/20/2010. If the date is on
the
number in d4 (20th), for example 05/20/2009, the formula should return
5/20/2009. Any help is greatly appreciated!

Thanks in advance.

Thank you very much! It worked perfectly!

"David Biddulph" wrote:

=IF(D4=DAY(D6),DATE(YEAR(D6),MONTH(D6),D4),DATE(Y EAR(D6),MONTH(D6)+1,D4))
--
David Biddulph

"Gretchster" wrote in message
...
I have a date in cell d6 and a number representing a day in cell d4.
For example, the date is 03/15/2009 in d6 and the number in d4 is 20.
I need a formula to return the next occurence of the 20th from the date in
d6. In the example above the next occurence would be 3/20/2009. If the
date
were 12/22/2009, the formula should return 1/20/2010. If the date is on
the
number in d4 (20th), for example 05/20/2009, the formula should return
5/20/2009. Any help is greatly appreciated!

Thanks in advance.

Hi,

Try this

=EOMONTH(D6,IF(DAY(D6)<20,-1,0))+D4

The EOMONTH function is part of the ATP in 2003 and earlier so you would
need to attach it by choosing Tools, Add-ins, and checking the Analysis
ToolPak.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Gretchster" wrote:

I have a date in cell d6 and a number representing a day in cell d4.
For example, the date is 03/15/2009 in d6 and the number in d4 is 20.
I need a formula to return the next occurence of the 20th from the date in
d6. In the example above the next occurence would be 3/20/2009. If the date
were 12/22/2009, the formula should return 1/20/2010. If the date is on the
number in d4 (20th), for example 05/20/2009, the formula should return
5/20/2009. Any help is greatly appreciated!

Thanks in advance.