Hi,

I am trying to do $ calculations for subscriptions at work and was wondering if someone could help me out. It is based on the number of days in each month, which then determines the amount of revenue that can be recognized for that month.

Because the 'from' and 'to' dates vary from client to client, things are a bit complicated. One column has the 'From' date. Another column has the 'To' date. The next column has the total amount for the subscription. From there, each column represents revenue that can be recognized for one month out of the year (with each column labelled by the month).

As an example, a client could have a subscription that totals $175,000 per year that begins on 2/24/05 and ends on 2/23/06 (365 days). For February '05 the amount of revenue that can be recognized is $175,000 X (5/365) = $2,397.26 where the 5 represents the number of days remaining from the 24th to the 28th in 2005. Likewise, for February '06, the amount of revenue would be $175,000 X (23/365) = $11,027.40 where the 23 represents the number of days the subscription covered for the ending month.

The fraction for each of the middle months would be based on the total number of days in each month: March would be 31/365, April would be 30/365, May would be 31/365, etc.

The problem is that the beginning and end months vary. The subscription could be from 2/24/05 to 2/24/06 for one client, from 2/7/05 to 2/6/05 for another, or even semiannually from 3/1/05 to 8/31/05 for another.

Would anyone have any idea of how to approach this? I suspect it may involve using 'If' statements, but have no idea where to go from there if at all. I'd really appreciate any suggestions, even if just for the annual subscriptions that start in February.

Thanks a lot! And thanks for even reading all of this!