Converting Mixed Dates to Time Values for Formulas

[jaypop18]

I get a report from our ERP that generates the number of time spent on a given task and the format comes in as ddd.hh:mm (ex. 001.04:35). I would like to be able to convert this data to a value that I can use to sum all the data in the column together to find total time for a given task range. In addition to the funky formatting that the report comes with, the other issue is that 1 day of time is actually 8.5 hours, not 24 hours of time.

I tried using formulas to isolate the individual numbers (ex. 001, 04, 35) but I'm getting results that are odd. For example, if I use the formula =(LEFT(P407,3)*8.5) where P407=001.05:15 the forumla returns a value of 8.5. Great, that's what I need. But then if I try to format that cell as an [h]:mm I get 204:00. And I know that 1 day of time is 8.5 hours so my result should be 8:30? I thought if I converted the days portion of the data to hours:minutes then I could add it to the hours:minutes that are remaining in the cell to get total time and then use a simple SUM formula to give me total time.

This is a report I need to run every week so I need a simple solution to convert this time data into a value that I can use for basic statistical formulas.

Thanks in advance!

 

[MARK858]

returns a value of 8.5. Great, that's what I need

If the 8.5 is correct then just divide it by 24 and format as [hh]:mm or [h]:mm

 

[jaypop18]

Brilliant! That worked. Still a little tedious because now I have to add a second column to convert the data into [hh]:mm with this formula =((LEFT(P17,3)*8.5)/24)+(MID(P17,5,5)), fill that down the range and then SUM it at the end. I guess I need to test my hand at a macro to do this for me whenever I open this spreadsheet.

Thanks for the help!