conditional formatting of dates

[Gretchster]

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.

 

[David Biddulph]

=IF(D4>=DAY(D6),DATE(YEAR(D6),MONTH(D6),D4),DATE(YEAR(D6),MONTH(D6)+1,D4))

 

[Gretchster]

Thank you very much! It worked perfectly!

=IF(D4>=DAY(D6),DATE(YEAR(D6),MONTH(D6),D4),DATE(YEAR(D6),MONTH(D6)+1,D4))

 

[Shane Devenshire]

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.