fixing a duration in mm:ss entered as a time of day (hh:mm:ss)

we use an online 'job sheet' of sorts that provides various details of time based travel jobs.
The data can be downloaded as n excel sheet, and I want to make some calculations.

however one column 'D' provides the start time of the job (e.g. 30/11/2018 8:29) and 'E' gives the duration 17:22. the 17:22 is minutes and seconds but is formatted as hh:mm:ss i.e. it thinks that 17:22 is 5:20 PM.
is there an easy way to convert it to mm:ss? obviously just changing the format is not the solution unfortunately!

many thanks, Theo

with excel sheet attached and the actual outcome I'm working towards! (thanks)

we use an online 'job sheet' of sorts that provides various details of time based travel jobs.
The data can be downloaded as n excel sheet, and I want to make some calculations.

however one column 'D' provides the start time of the job (e.g. 30/11/2018 8:29) and 'E' gives the duration 17:22. the 17:22 is minutes and seconds but is formatted as hh:mm:ss i.e. it thinks that 17:22 is 5:20 PM.
is there an easy way to convert it to mm:ss? obviously just changing the format is not the solution unfortunately!
I am wanting to ultimately use the corrected minutes and seconds values to calculate a job cost where the job is valued at 2 x number of minutes + 10 which is the dollar value.

e.g. 14:37 being 14 minutes 37 seconds would be (14 + 37/60) x 2 + 10 = $24.61 but rounded up to $25.00

the attached sheet has the desired outcome plus some of the erroneous time fields.

thanks!

Theo

I am being a bit thick here- why is the multiplier 48?
such a simple solution thanks!

yes, I see what you mean- thanks again, works well.

Hi.
Find attached an approach to what I think you are trying to achieve.
I have only formatted the first ten rows.
Columns F,G,H, number format 2 decimal places.
Columns I,J,L,M, Text format
Columns K,N, Custom format dd/mm/yyyy hh:mm
Columns O,Q, Number format zero decimal places
Column P, Custom format [h]:mm this may not be in your standard selections, you will maybe have to create this.
Column Q Number format zero decimal places
Columns K & N are set at zero width (they are 'helper' columns)
As 'time $ rate' and additional $ charge (surcharge) would be potential variables that altered over time I have taken the fixed values out of the formula and allocated them in the sheet.
Columns A to F I presume are for reference and did not play apart in the time/cost calcs.
If this is a labour intensive tedious daily chore it would probably lend itself to UserForm/VBA approach.
However hope you solve your problem soon.
regards.
Torachan.