Data and time math

Hey,

Bit confused on how to make this work right.

I have a date and time list and an electrical consumption list.

I want to take the date and time, subtract it from the following date and time to get the time passed. Then multiply it by the electrical consumption to get an estimate of usage.
If I just use the time then when one day goes to the next I get a negative number because I'm not taking into account the date, but I'm not sure how I put the date into my calculations.

The attached sheet is the raw data, perhaps that will be helpful to see what I'm trying to accomplish.

Thanks

An addition to the post

Formula:

=CONCATENATE(A196,B196)

Where A196 is 6/14/2012
And B196 is 11:58:41 PM
The result is 410740.999085648148148

Formula:

=CONCATENATE(A197,B197)

A197 = 6/15/2012
B197 = 12:04:52 AM
The result is 410750.00337962962962963

Subtracting these two you get 9.004293981 which in my understanding is just over 9 days.
I must be missing a step or not understanding the format.

Thanks

Originally Posted by thisandthat

The result is 410750.00337962962962963

Subtracting these two you get 9.004293981 which in my understanding is just over 9 days.
I must be missing a step or not understanding the format.

Thanks

Dates are stored internally within Excel as the number of elapsed days since some starting date (1st Jan 1900). As such, they are always integer, and so 41074 is the number of days from that reference date up to 14th June 2012.

Times, however, are stored as fractions of a 24-hour day, so 12 noon would be stored internally as 0.5, and 6:00am would be 0.25, and so on.

Because these are just numbers, they can be added and subtracted to get consistent results (NOTE: Concatenation joins text values together, and results in a text value), and can be used in other arithmetic expressions.

Hope this helps.

Pete

Basically you want (end_date + end_time - start_date - start_time) to get the time difference (although this will be in days - you need to multiply by 24 to convert it to hours). In your spread sheet you can put this formula in E3:

=D3*(A3+B3-A2-B2)*24

and then copy it down.

Hope this helps.

Pete