I am trying to do some analysis on my electricity usage, just simply taking a meter reading morning and night and working out average hourly daily use and night use.
So I have a column for each day and time is in HH:MM format
My units are just the difference between meter readings so simple numeric number eg 7.1
so if morning time was 08:00 and evening time was 22:30, and used units was 10.0, then I would expect the answer to be 0.689 units per hour - can someone give me a formula to do this?
Also if I want to do a full 24 hour calculation ie from 22:15 to 23:10 the following night, can I have a formula for that as well that takes into account that we have rolled over a day.
I have tried the following AI suggestions and it doesnt seem to be working for me
If your Start Time is in cell A2, End Time is in B2, and Units is in C2, type this formula into your result cell:=C2 / ((B2 - A2) * 24
If tracking across midnight: Use the MOD function so the math does not break: =C2 / (MOD(B2 - A2, 1) * 24)
Help!
|
