Excel: Cumulative hours from text string

Question

Hello,

For my job, trip pay is calculated in "flight hours." I can export an excel spreadsheet that has many trips listed with properties like start and end date, trip pay, etc. In the exported file, everything is formatted generically. I've had no problems converting the dates from general into a formatted date and time.

What I'm trying to do is calculate the flight hour pay per day. I have successfully gotten the trip length by subtracting the start and end dates and come up with a decimal result. If a trip is 3 and a half days, my calculated column reports "3.5."

The problem I'm having is getting the flight hours pay into a usable format. Say that 3.5 day trip pays 28 hours and 15 minutes. The flight pay column (formatted as general) from the exported spreadsheet shows "28:15" I have tried the timevalue function and that works for anything under 24 hours but anything higher than that reported values are incorrect. I have the output Trip Pay column formatted as [h]:m. If I manually type 28:15 into that column, my "Pay per Day" column which is Trip Pay / Trip Length reports a correct value such as "8:06" per day.

All the articles I have found deal mainly in converting strings into a clock time vs a cumulative hours and minutes.... Any assistance on how to get the general "28:15" into a time value that can be used for mathematical calculations would be greatly appreciated!

Cheers.

Answer 1

Let's say you have 28:15 as a text value in D2. In E2, enter the formula =D2+0 and apply the custom number format [h]:mm or [hh]:mm to the cell with the formula.

Answer 2

Adding 0, or multiplying with 1 forces Excel to convert the text string to a number.

Answer 3

Honestly, how can the solution be so simple and why does that convert it?? Not asking for an answer, just can't believe that worked. Thank you very much for your assistance, I appreciate it!