Opening and closing times past midnight

[Thoron6]

Morning,

I have posted this before but not been to clear as I'm not sure how to attach an example spreadsheet.

I have tried to simplify the problem.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]00:00[/TD]
[TD]01:00[/TD]
[TD]02:00[/TD]
[TD]03:00[/TD]
[TD]04:00[/TD]
[TD]05:00[/TD]
[TD]06:00[/TD]
[TD]07:00[/TD]
[TD]08:00[/TD]
[TD]09:00[/TD]
[TD]10:00[/TD]
[TD]11:00[/TD]
[TD]12:00[/TD]
[TD]13:00[/TD]
[TD]14:00[/TD]
[TD]15:00[/TD]
[TD]16:00[/TD]
[TD]17:00[/TD]
[TD]18:00[/TD]
[TD]19:00[/TD]
[TD]20:00[/TD]
[TD]21:00[/TD]
[TD]22:00[/TD]
[TD]23:00[/TD]
[/TR]
[TR]
[TD]Sat[/TD]
[TD]25[/TD]
[TD]22[/TD]
[TD]21[/TD]
[TD]20[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]17[/TD]
[TD]15[/TD]
[TD]62[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]35[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Sun[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Mon[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]53[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]Tue[/TD]
[TD]Etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thur[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sat[/TD]
[TD]08:00[/TD]
[TD]02:00[/TD]
[/TR]
[TR]
[TD]Sun[/TD]
[TD]08:00[/TD]
[TD]23:00[/TD]
[/TR]
[TR]
[TD]Mon[/TD]
[TD]07:00[/TD]
[TD]22:00[/TD]
[/TR]
[TR]
[TD]Tue[/TD]
[TD]08:00[/TD]
[TD]22:00[/TD]
[/TR]
[TR]
[TD]Wed[/TD]
[TD]09:00[/TD]
[TD]22:00[/TD]
[/TR]
[TR]
[TD]Thur[/TD]
[TD]10:00[/TD]
[TD]22:00[/TD]
[/TR]
[TR]
[TD]Fri[/TD]
[TD]10:00[/TD]
[TD]01:00[/TD]
[/TR]
</tbody>[/TABLE]


So I need to sum the totals outside of the opening hours. The main problem I am having is when the closing time is past midnight and not including the next days consumption in the relevant day calculations.

So the above example, I'd like to see

Sat = sum of numbers after 1am (due to Friday not closing until 1am) until it opens at 8am .. so 21 + 20+19+18+17+19...

As always, any help would be greatly appreciated.

Thank you

 

[Thoron6]

Also.... As this would be across a portfolio, the day and opening hours are not all the same. So Sunday 08:00 - 23:00 may only be for this specific example but may change at another branch.

Thank you