On-peak, and Off-peak calculations - Please help with the formulae

**helloganesh** · 03-12-2013, 11:23 AM

I need help in sorting out a logic to calculate the on-peak, off-peak and mid-peak hours.
Please see the attached file for the inputs.
Thank you very much for your help and God bless you.
GA

**Pete_UK** · 03-12-2013, 11:50 AM

First of all, you need to correct the entry in L23 as it doesn't make sense - I changed it to 18:00:00.

Put this formula in G28:

Formula:

Please Login or Register  to view this content.

and copy across to L28 (i.e. excluding the Sunday column).

Then put this formula in G29:

Formula:

Please Login or Register  to view this content.

and copy across to L29.

Put this formula in G30:

Formula:

Please Login or Register  to view this content.

and copy across to L30. Then put this formula in M30:

Formula:

Please Login or Register  to view this content.

Hope this helps.

Pete

**shg** · 03-12-2013, 11:54 AM

G28 and across: =MAX(0, MIN(G16, G23) - MAX(G15, G22))

G29 and across: =MAX(0, MIN(G17, G23) - MAX(G16, G22))

G30 and across: =G23-G22 - (G29+G28)

**helloganesh** · 03-12-2013, 12:11 PM

Thanks a ton for your reply. How can this be modified to handle None (for example, there are no on-peak and mid-peak hours on Sundays).

**Pete_UK** · 03-12-2013, 12:19 PM

Don't know who you are replying to, but my first three formulae specifically exclude Sundays and the fourth one is for Sunday only. Perhaps you can apply shg's formulae in the same way.

Hope this helps.

Pete