Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help
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 the =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 -----Original Message----- 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|