need help

  • Thread starter Thread starter Guest
  • Start date Start date
G

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