monthly split of contract value

**nazimscr** · 09-13-2010, 02:56 PM

Hi All,

I was able to come up with below formula for splitting contract value into months for revenue based on start and end date of the contract.

SUMPRODUCT(--(ROW(INDIRECT($D7&":"&$E7))>=G6)*(--ROW(INDIRECT($D7&":"&$E7))<H6))*($F7/360)

D7 = start Date
E7 = end date
G6 = beg of current month
H7 = end of current month (ie beg of next month)
F7 = annaul contract value.

This formula works perfectly for 365 days calculation, i want to make it into 360 days calculation, so that revenue amount is same for all the months.

I have attached the file.
would really appreciate your help

Thks

Nazim

**Richard Buttrey** · 09-13-2010, 03:06 PM

Hi,

It's not clear, at least to me, exactly what you're asking for. If you want the revenue the same for each month then why not just divide F7 by 12.

Using a divisor of 360 when you're using dates which cover 365 days are always going to result in a difference.

Regards

**nazimscr** · 09-13-2010, 03:20 PM

divinding by 12 will not work firstly it only works when dates are matching (manual excercise) and secondly contract date can start from the mid of the month. i am looking for function like days360 which convert 365 days into 360 days.