I need to create a revenue schedule:

Invoice
Created on date
Start date of service
End date of service
Amount

My problem is I am trying to add a variable to my current or create a new formula that would take into consideration the create on date of the invoice as sometimes the customer is invoiced late so the service start period may be before the created on date. This means that something with the service date starting in July could be billed for in Sept – so the Sept column should show 3 mths of service and then the other months would should 1 mth each until the end service date.

In addition, I am using 360 days not 365 days (see attached example) and I think for what I am trying to do that the year may need to be in the formula.

Attached Files

Book1.xlsx (23.0 KB, 20 views)

Hi

I just edited the formula in cell BO 10 and copied it across and It think it does what you require

=IF(SUM($AH10:BN10)=$Z10,0,IF(DAYS360($D10,BO$1)>1 ,$Z10/$S10*INT(DAYS360($C10,BO$1)/30)-SUM($AH10:BN10),0))

Roger,

Like the formula however it doesn't work, please see attachment.

Thoughts?

Attached Files

Book1.xlsx (28.7 KB, 9 views)