Time analysis requirement for electricity charging

Hi Everyone,

Although I have been using excel for many years I am unable to solve the following.......

I am trying to do an analysis of my electricity charges (I run accommodation business from home in France) and there are two bands of time for charging from EDF:

10:00pm until 6:00am - Off peak (cheap) electricity
6:01am until 9:59pm - Peak (More expensive) electricity

I have completed a long spreadsheet schedule of all my electrical appliances with a column showing the time they "come on" and a column showing the time the "go off".

Depending on those times, I need a colum(s) which calculartes the elapsed hours or minutes split between (and two columns would therefore be better) peak usage and off peak usage. These columns need to show hours, as they are used to calculate Kilowatt hours (depending on rating of the appliance) and therefore price.....

Please can someone help - I am using an immense amount of time trying to figure my way round IF formulae etc....

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

edit: please soft delete...


If we assume start/end times in A2:B5 of:

14:00	23:00
01:00	06:00
02:00	12:00
22:00	05:00

we can calculate the Peak in C2:C5 with:

=IF(B2>A2;MIN("22:00";B2)-MAX("06:00";A2);MAX(0;B2-"06:00")+MAX(0;"22:00"-A2))
copied down

we can calculate the Off Peak in D2:D5 with:

=MOD(B2-A2;1)-C2
copied down

Sorry DonkeyOte, when I replicate your suggestions the formula flags on fx as invalid and therefore produces no result. I copied formula direct to avoid 'typos'.

Any suggestions?

No need to quote my post in your reply...

I assumed you were running on a French configuration so I used ; delimiter as opposed to , as we would use here in the UK

UK settings:

C2:
=IF(B2>A2,MIN("22:00",B2)-MAX("06:00",A2),MAX(0,B2-"06:00")+MAX(0,"22:00"-A2))
copied down

D2:
=MOD(B2-A2,1)-C2
copied down