need help

[Guest]

Hello,

I am trying to create a spreadsheet that will determine the # of invoices
for a contract that will happen in 2005. Primarily will be, how many invoices
will happen in quarterly installments. This is easy if you have say a 12
month contract starting in January. There will be 4 invoices, an invoice
occuring on Jan, April, July and October. But now say the contract starts in
April and it is a 7 month contract. An invoice will occur in April, July and
October. So only 3 invoices will occur. Is there any way to set this up in
Excel to automatically calculate this either through VBA or directly as a
cell formula? Hope this makes sense.

 

[Walt Weber]

Hi Brad,

You can do this without VBA. Check out the EDate
function that is included with the Analysis ToolPac addin.

Something like the following will get you there:
=IF(YEAR($E9)=YEAR(Edate($E9,3)),1,0)

If the date is in E9, copy this formula to cells F9, G9,
and H9, then change the number 3 to 6 in G9 and to 9 in
H9. The 3 is the number of months from the date in E9.

Your result will be 1+F9+G9+H9

Best Regards,
Walt