Needs to distribute spent hours to pick, normal and low hours.

Hello friends,
My friend asked me to produce a spent hour consumption distribution for Pump-engine.
Total spent hours are defined from date (A7) start time (cell B7) to end time (cell C7).If end time was less than start time, is known as end time of next day
Also, depending on weekdays, I have a time range for pick hour, normal and low hour.
Now, I need to distribute the # spent hours into each pick, normal and low hour.
Because the hourly power rate tariff is different between pick, normal and low hour, the hour distribution will helps to calculate how much we have to pay to the power supplier.
At the moment, I am stuck and could not get around it.
Thanks in advance for your assistance.

Can we count on all Start / End times to be rounded to nearest half hour as in the upload?

Yes, it is set of half hour.

Low hours is weekdays only ... no Sunday hours, yet the expected returns show 7:00 during Low hours for 9/6/2015 which is a Sunday.

And do you only include M-F for weekdays in Low hours?

Originally Posted by FlameRetired

Low hours is weekdays only ... no Sunday hours, yet the expected returns show 7:00 during Low hours for 9/6/2015 which is a Sunday.
And do you only include M-F for weekdays in Low hours?

Thank FR,
Low and normal hours is from monday to sunday.
Pick hour is from monday to saturday.
No pick hour on sunday.

Originally Posted by José Augusto

Hi
See the file with the following formula

Formula:

Please Login or Register  to view this content.

Thanks a lot, Jose Agusto
I will pass it to my friend to check with atual data, then come back soon.

Good one, José. Really liked your lookup table as well as formula.