conditional formatting of dates

  • Thread starter Thread starter Gretchster
  • Start date Start date
G

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.
 
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.
 
Back
Top