Expert: Tom Ogilvy - 11/7/2009

Question
Hi Tom,


I have a large spreadsheet with production times, ie time job started and time job finished. Unfortunately, all of the data has been keyed in decimal rather than a time format. I now need to add a column and calculate the time a job has taken. For example.

Time started       Time Finished    Time Taken
  8.55               11.20            2.25

I have looked at the TRUNK and RIGHT functions but have been unable to calculate the "Time Taken" as the time has is in decimal and converting decimal to a time format gives a strange result.

Is there any way the start times and finish times can be converted into "minutes" then subtracted from each other and then converted back into hours and minutes for the "Time Taken".

In the above example, converting to minutes would result in 535 and 680 minutes respectively. So the time taken to do the job would be 145 minutes or 2 hours 25 minutes.  


I would be most grateful if you could help with this one.

Kind Regards

Malcolm  

Answer
Malcom,

E2:  8.55
E3:  11.2
E4: =SUBSTITUTE(TEXT(SUBSTITUTE(TEXT(E3,"#.00"),".",":")-SUBSTITUTE(TEXT(E2,"#.00"),".",":"),"h:mm"),":",".")*1

produces 2.25
 for me.

Basically, use substitute to replace the period with a colon, then subtract two time values, then convert the answer back to a decimal representation.
--
Regards,
Tom Ogilvy