Calendarize invoice data into standard calendar months

[djwilliams]

Hello,

I'm working with a large number of invoices that do not follow standard calendar dates (i.e. the first day of the month to the last day - 8/1/13-8/31/13). I'm trying to find a systematic way to convert these invoices into standard calendar dates so I can evaluate various locations against normalized months.

Invoices cover a range of billing periods (some invoices cover only a few days in a month and some invoices span several months worth of billing).

What I'm trying to do is count the number of days within an invoice and split up the cost into the correct standardized months. Here are some example invoices:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Start date[/TD]
[TD]End Date[/TD]
[TD]Cost[/TD]
[TD]Invoice days[/TD]
[TD]Cost per day[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Denver
[/TD]
[TD]1/1/2013[/TD]
[TD]1/4/2013[/TD]
[TD]$550[/TD]
[TD]4[/TD]
[TD]$137.5[/TD]
[TD]$550[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]New York[/TD]
[TD]1/15/2013[/TD]
[TD]2/15/2013[/TD]
[TD]$2500[/TD]
[TD]31[/TD]
[TD]$80.65[/TD]
[TD]$1290.4[/TD]
[TD]$1209.75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Portland[/TD]
[TD]1/21/2013[/TD]
[TD]3/20/2013[/TD]
[TD]$5000[/TD]
[TD]58[/TD]
[TD]$86.21[/TD]
[TD]$862.09[/TD]
[TD]$2413.78[/TD]
[TD]$1724.14[/TD]
[/TR]
</tbody>[/TABLE]

Does anyone have a formula that can calculate these values dynamically I don't need a different formula for each invoice type (invoices within same month, invoices spanning two months, and invoices spanning multiple months)?

I've been searching everywhere and can only find proprietary software that will perform this function. Any help would be most appreciated.

Best,
David