Thread: Weeks left in a month after a certain date for a certain duration of time

Hi all,

I am trying to write a formula/macro that will figure out how many weeks are left in each month after a certain date (with a 1 week buffer) and lasting for a certain duration of time and then calculating the monthly revenue for each month.

For instance if I have a start date of 1/1/12 and a duration of 11 weeks and weekly revenue of 10,000. I would come up with 3 weeks left in Jan, 4 weeks left in Feb, and 4 weeks left in March, and 0 weeks left in April. This would give me $30,000 for Jan, $40,000 for Feb, $40,000 for March, and $0 for April.



ExcelFileHELP.xlsx


Thanks,
Mike

Why 3 weeks left in Jan? It has 31 days, so after the 1st there are 30 days left, which is 4 weeks and 2 days. And how do surplus days in a month get accounted for?

ExcelFileHELP.xlsx

Here is the file again with (I replaced close date with start date sorry for the confusion)

I used new numbers for the example in this sheet. But to answer your question about the 3 weeks in Jan (feb in this case) it is because after the Start date of the project I give a 1 week buffer before work acutally will begin. So, if the start date were 1/9/12 work would actually begin 1/16/12 and I would want to calculate remaining weeks in January from the 16th which would be roughly 2..

In the end I am trying to calculate monthly revenue for the life of the project. To do this I must know how many weeks of work are possible in each month within the project duration range. Then I take that number and do a simple multiplication (=numberofweeks*weelyrevenue)