Excel: overlapping date intervals into months

2

I have heating bills indicating voicing period (eg. from 04-feb to 23 feb - white in table, blue in timeline-) and expense (eg. 95€). SOME of them do also have a voice indicating "already paid" (in previous bill) expenses (eg. 4-feb to 7-feb: 40€). These periods and expenses are indicated in yellow in the table and in orange in the timeline. I need to recalculate these expenses "de-overlapping" the periods, subtracting the "already paid in previous bill" (when needed) from the previous bill, calculating day by day expenses and then monthly expenses.

Input data:

enter image description here

Input timeline: enter image description here

Desired outpud data:

enter image description here

Output timeline: enter image description here

PS. English is not my first language

nicari

Posted 2016-11-29T11:20:42.113

Reputation: 31

Answers

1

You can use SUMPRODUCT for that:
=SUMPRODUCT((Table1[start]<=F2)*(Table1[end]>=F2)*Table1[daily amount])

This formula checks each interval whether it contains the actual day, and if yes, then includes its daily amount (added helper column) to the total.

Formula used in "daily amount column":
=[@amount]/([@end]-[@start]+1)

enter image description here

Máté Juhász

Posted 2016-11-29T11:20:42.113

Reputation: 16 807

I forgot to mention that the amount for of the overlapped periods are already included in the preceding period. For example: 21/01/2016 -> 15/02/2016 : € 50,00. 10/02/2016 -> 15/02/2016 : € 10,00. 10/02/2016 -> 25/02/2016 : € 30,00.

sould become: 21/01/2016 -> 09/02/2016 : € 40,00. 10/02/2016 -> 15/02/2016 : € 10,00. 16/01/2016 -> 25/02/2016 : € 30,00. – nicari – 2016-12-02T10:13:15.887

It's difficult to understand your comment. Please update your question with desired results. – Máté Juhász – 2016-12-02T13:05:00.607

I can't. These amounts refer to heating bills. Every 1/2/3 months I receive a bill with period (eg. 10 gen->10feb), cost (100€), and another cost indicating "already paid 20€ referring to 10-gen->20gen". The previous bill was something like 20dec->20gen 150€, already paid 30€ 20dec->25dec. If you look at the timeline in the question it is more clear. The actual bills are the blue bars. The orange ones are the "already paid amounts". To calculate correct daily amount the "already paid" should be subtracted from preceding period and dates should be rearranged to not be overlapping anymore. – nicari – 2016-12-02T16:50:23.353

What do you mean by "I can't"?? Your question is intended to help also others with similar problems. It's should be clear and contain all important information. Please edit it. – Máté Juhász – 2016-12-02T18:52:57.197

Sorry, I thought I needed 2000 of reputation to edit my questions. – nicari – 2016-12-02T22:08:38.160

Asked: 2016-11-29T11:20:42.113

Viewed: 372 times

Active: 2016-12-02T22:12:58.150