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!
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!