Prorate/Normalization formula

First in 'Input' are text "dates". If you are not aware of it dates are numbers that are the count of days from 1/1/1900. Today 9/19/2018 is day 43362. The formatting is cosmetic.

I converted for you in the attached. In the future you will want to be alert for this as it is common for dates to be entered or imported as text.

Times are also numbers ... decimal fractions of a day. 8:00 AM = 8/24 =1/3 = 0.3333333 Noon is 12/24 = 1/2 = 0.5 and 4:00 PM = 0.66666667

In the attached made a helper column in D of 'Input' to break down the daily amounts in each date range.

Formula:

Please Login or Register  to view this content.

Then in C2 and down 'Output'

Formula:

Please Login or Register  to view this content.

A difference lies in how the number of days is calculated in the helper.

=DAYS(L16,K16) returns number of days exclusive of the start date.

In the upload the calculations were supposed to be

=(L16-K16+1)

or if you prefer

=DAYS(L16,K16)+1

Both start and end date are included.

If you "shrink" the problem I find it more intuitive. TODAY()-TODAY()+1 = 1 which is correct.

My apologies for that oversight.

Make that correction in the helper column, and please let me know how it checks out.

Also note that 5/1/2018 is included in the 'Input' list and therefore in the amount per day calculation in that row. 5/1/2018 is not included in the 'Output' ranges but the amount per day is used from the 4/3/2018 - 5/1/2018 range in the calculation of 'Output' 4/1/2018 - 4/30/2018.
I have probably explained that very badly, but if you look at both ranges and what is included and why it should be intuitive.

In the attached for the helper formula.

Formula:

Please Login or Register  to view this content.

In 'Output' column C

Formula:

Please Login or Register  to view this content.

The summary now shows Delta 0% but only because it is formatted to 0 decimal places. Formatted to 4 places 0.0003%.