Hello all. I have a report I am trying to do and the parsed data that I receive has times formatted in wk d h m s currently and I need to convert it to total hours:minutes:seconds in one column and total minutes:seconds in another column as shown below. I would prefer dropping the seconds all together if possible as well.
<tbody>
</tbody>
812:55:15
and
48720:15
Is there a function that will do this for me automatically? I have been trying to use text to columns to just split them all but then they are lined up goofy and I cannot figure out how to remove the lettering. Another thing that would work is if there is a way for excel to just go in and remove the wk d h m s lettering on the data and organize the output from right to left as shown below;
<tbody>
</tbody>
If I can strip the letters and text to column and have it output in right to left orientation I can just use =(((A2*7)+B2)*24)+C2 for the hours calculation and =((((A2*7)+B2)*24)+C2)*60 for the minutes respectively (I think).
Either solution would be awesome. I have about 40,000 + entries I have to run this on so doing it by hand is not going to happen
Thanks!
| 4wk 5d 20h 55m 15s |
<tbody>
</tbody>
812:55:15
and
48720:15
Is there a function that will do this for me automatically? I have been trying to use text to columns to just split them all but then they are lined up goofy and I cannot figure out how to remove the lettering. Another thing that would work is if there is a way for excel to just go in and remove the wk d h m s lettering on the data and organize the output from right to left as shown below;
| weeks | days | hours | minutes | seconds |
| 4 | 5 | 20 | 55 | 15 |
| 4 | 11 | 38 | 53 | |
| 7 | 25 | |||
| 2 | 14 | 3 | 23 | 28 |
| 17 | 15 | 00 |
<tbody>
</tbody>
If I can strip the letters and text to column and have it output in right to left orientation I can just use =(((A2*7)+B2)*24)+C2 for the hours calculation and =((((A2*7)+B2)*24)+C2)*60 for the minutes respectively (I think).
Either solution would be awesome. I have about 40,000 + entries I have to run this on so doing it by hand is not going to happen
Thanks!
