On Fri, 30 Jan 2009 13:01:02 -0800, NFL wrote:

I have a cell reference that has a begining date (for example Apr 1, 1994)
and payments should have been made on the 1st and 15 of the month every month
up to the present. Is there a way a formula that can be written that way?

I also need a similar formula, but the start date would be the 15th of the
month. These payments will be made on the 15th and the end of the month.
The end of the month could be 30, 31, 28, or 29 (depending on leap year).

With this formula, we could gather totals on the other column to check to
see when payments were made and keep a running total.

Thank you for your help!

Assuming you either have the Analysis Tool Pak installed or are using Excel
2007 or later:

Starting at the first pay date in the month that is in Start_Date

With any date in Start_Date

For 1st and 15th


B2:
=EDATE(Start_Date-DAY(Start_Date)+1,(ROWS($1:1)-1)/2)+14*(INT(ROWS($1:1)/2)=ROWS($1:1)/2)

and fill down as far as required.


For 15th and EOM:

C2:
=EOMONTH(Start_Date-DAY(Start_Date),(ROWS($1:1))/2)+15*(INT(ROWS($1:1)/2)<ROWS($1:1)/2)

and fill down as far as required.
--ron