Convert TIME from a stupid way of representing into something meaningful

[excelsion]

Hello,

I have a report where the time taken by a certain activity is reported in the manner below ( not sure what the reporting analyst thought when designing it)....How to I make it workable and calculate the time in hours????

5Hours 11Mins 17Seconds

1Day 3Hours 2Mins 27Seconds

2Days 14Hours 15Mins 38Seconds

2Days 1Hour 51Mins 49Seconds

14Hours 39Mins 2Seconds

10Days 4Hours 38Mins 4Seconds

20Hours 51Mins 8Seconds

11Hours 17Mins 23Seconds

<colgroup><col></colgroup><tbody>
</tbody>

 

[VBA Geek]

you can use a custom function, then format the cells as [h]:mm:ss

Function ConvertStringToTime(ByVal S As String) As Date
              ConvertStringToTime = Evaluate(Replace(Replace(Replace(Replace(Replace(Replace(S, "s", ""), "Day", "*1"), "Hour", "/24"), "Min", "/1440"), "Second", "/86400"), " ", "+"))
End Function

Excel 2010
	P	Q
20	5Hours 11Mins 17Seconds	5:11:17
21	1Day 3Hours 2Mins 27Seconds	27:02:27
22	2Days 14Hours 15Mins 38Seconds	62:15:38
23	2Days 1Hour 51Mins 49Seconds	49:51:49
24	14Hours 39Mins 2Seconds	14:39:02
25	10Days 4Hours 38Mins 4Seconds	244:38:04
26	20Hours 51Mins 8Seconds	20:51:08
27	11Hours 17Mins 23Seconds	11:17:23
Sheet2

Cell Formulas
Range	Formula
Q20	=ConvertStringToTime(P20)
Q21	=ConvertStringToTime(P21)
Q22	=ConvertStringToTime(P22)
Q23	=ConvertStringToTime(P23)
Q24	=ConvertStringToTime(P24)
Q25	=ConvertStringToTime(P25)
Q26	=ConvertStringToTime(P26)
Q27	=ConvertStringToTime(P27)

 

[excelsion]

This is brilliant...the only challenge is ..its skipping the days and only converting from hours and beyond...

2Days 14Hours 15Mins 38Seconds

14:15:38

<tbody>
</tbody>

I copied the exact code in a new module...and ran it.

you can use a custom function, then format the cells as [h]:mm:ss

Function ConvertStringToTime(ByVal S As String) As Date
              ConvertStringToTime = Evaluate(Replace(Replace(Replace(Replace(Replace(Replace(S, "s", ""), "Day", "*1"), "Hour", "/24"), "Min", "/1440"), "Second", "/86400"), " ", "+"))
End Function

Excel 2010

	P	Q
20	5Hours 11Mins 17Seconds	5:11:17
21	1Day 3Hours 2Mins 27Seconds	27:02:27
22	2Days 14Hours 15Mins 38Seconds	62:15:38
23	2Days 1Hour 51Mins 49Seconds	49:51:49
24	14Hours 39Mins 2Seconds	14:39:02
25	10Days 4Hours 38Mins 4Seconds	244:38:04
26	20Hours 51Mins 8Seconds	20:51:08
27	11Hours 17Mins 23Seconds	11:17:23

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula Q20 =ConvertStringToTime(P20) Q21 =ConvertStringToTime(P21) Q22 =ConvertStringToTime(P22) Q23 =ConvertStringToTime(P23) Q24 =ConvertStringToTime(P24) Q25 =ConvertStringToTime(P25) Q26 =ConvertStringToTime(P26) Q27 =ConvertStringToTime(P27) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[VBA Geek]

on my side it is converting days as well,

if in your cell you have: 1Day 3Hours 2Mins 27Seconds <colgroup><col width="216"></colgroup><tbody> </tbody> then it wil be : 27:02:27 <colgroup><col width="73"></colgroup><tbody> </tbody>

<colgroup><col width="216"></colgroup><tbody>
</tbody>

 

[steve the fish]

Formatting issue i would suggest. Try formatting to [h]:mm:ss