Calculating HH:MM between two dates

[jason.hampton]

Group,

This (simple one) has me stumped. I would like to calculate (column F) the hours & minutes between two dates. In the attached, Event Start Time - Event End Time.

Columns B & E are formatted as follows, and can not be altered (data extracted from our turbine control system)

DD-MMM-YY HH:MM:SS

Test Worksheet.xlsx

[MrShorty]

It should be a simple subtraction =E3-B3 formatted as elapsed hours [hh]:mm. Recognize that the underlying value is still a number representing a fraction of a day. For example, the second entry is about 24 days apart, so the underlying value is about 24. The elapsed hours number format causes the number 24 to be displayed as about 570 hours. If this value is needed for further downstream calculations, it may be important to recognize the difference between the displayed value and the actual underlying value in the cell.

[jason.hampton]

MrSHorty,

I was WAY over thinking this! Sorry I am an "engine-nerd"... Let me experiment and get back to you.

Thanks for the rapid response...

[jason.hampton]

Sorry I tried to delete the second posted image. Disregard that. (short between the keyboard and chair)

[shg]

end - start, not start - end

[jason.hampton]

shg,

thanks that was my 'short'....

[jason.hampton]

What about the attached? I added DD:HH:MM to the cell formatting.

The end goal is to subtract the summary hours (down time) from maximum 'up time' hours for each gas turbine. Let me know if the new attached makes sense.

Test Worksheet.xlsx

[MrShorty]

The result is a total of 24 days, 5 hours, and 1 minute, (about 24.2 days) which seems to correctly represent the sum of the values above it. I guess it makes some kind of sense.

[jason.hampton]

All,

Attached is what I finally came up with. And wanted to share with this awesome group. My end goal is to calculate the red cell value (C30) for my monthly reporting.

I will mark this as solved. Thanks to all the feedback and support.

Test Worksheet.xlsx

[shg]

Just be aware that the "days" portion of that is actually day of the month, and won't go beyond 31 (the number of days in January).

[jason.hampton]

shg,

Correct. I had to reopen this thread due to not being able to get the final calculations to work. After my posting yesterday, I added real data from my power plant historian, and realized an error in the final calculations.

Attached is the same spreadsheet with real data in it. Test Worksheet NEW.xlsx

Upon further examination I realized the following:

Cell C52 - formatted for [hh].mm (cumulative hours) ~ sometimes our units are shut down for several days. (obviously 1 day = 24 hours)
Cell C53 - formatted for numbers, no decimals
Cell C54 - formatted for hh.mm

This presents a problem. For the month of July there is a max of 720 hours (30 days X 24hrs). The current "Service Hours" ('up time' for each turbine) value in C54 is 717.90.

When input on a calculator (C53-C52) this variance is 669.81.

I feel confident this is a simple cell formatting issue. However, I've yet to figure out the correct combination.

Any help would be appreciated.

[shg]

For the month of July there is a max of 720 hours (30 days X 24hrs)

There are 31 days in July.

[CK76]

So it's 2 days 2 hours 19 min. i.e. ≈ 2.09652 days (or Date value stored in Excel).

Which is stored in C3 & C52. Multiply by 24 to get number in hours.
2.09652*24 ≈ 50.32

Service hours = 669.68

See attached.

Alternately you can just use # of days (in C53) and format C54 as [hh]:mm (or [hh].mm).
669 hours and 41 min.

Edit: Oh right, 31 days. So 693 hours 41 min.

[jason.hampton]

There are 31 days in July.

sorry typo, but you get the drift...

[MrShorty]

It's a little bit more than a formatting issue. Remember that Excel stores times as fractions of a day. So the actual values behind [hh].mm values is a fraction of a day -- 629.01 (meaning 629 hours and 1 minute) is stored in the cell as 26.209... (meaning ~26 1/5 days). By contrast, the value in row 29 is a decimal hours value (720 meaning 720 hours). In order to perform the subtraction correctly, you must make sure that the actual value in both cells mean the same (you can't subtract 26.2 days from 720 hours and get anything meaningful).

We've gone to a lot of trouble to use the sexigessimal [hh].mm number formatting notation, so I assume that is the preferred way to move forward. Assuming that is the case, the key will be to convert your total hours per month into days per month. Either change the values in the lookup table (hidden sheet1) by dividing the times in the table by 24, or perform the division in row 20 after the lookup =VLOOKUP(...)/24.

ETA: If you are doing this kind of calculation in Excel, it really helps to understand how Excel stores date and time values. I recommend something like this: http://www.cpearson.com/Excel/datetime.htm

[jason.hampton]

CK76,

Thank you that seems to work perfectly! Mind BLOWN!!! I knew I was getting close.. Just couldn't fit the final pieces..


MrShorty,

Thank you as well. I was not aware of how Excel stores dates and time values. I will have to read the link you've submitted.