Cell formatting issue for time spent

[Michael Perry]

So we have a new ticketing system and have been trying to recreate the reports that we previously had in the old system. One of the metrics we are trying to capture is how long a technician spent on tickets on average. Unfortunately the new ticketing system gives us the following format for total time worked: "5 hr(s) 03 min(s)". I'm not sure how to work with or 'fix' this format so I can calculate and get averages etc... Any idea how to format this output better so I can actually use it? Hopefully I've explained this properly. The combination of numbers and letters in this is making formatting a nightmare. Any ideas?

[Richard Buttrey]

If it is indeed a proper time number then you can format it any way you want. What do you get if you test the cell with
=ISNUMBER(A1)
where A1 contains the time worked. If you get TRUE then it is a number. Anything else then it's text and will need string slicing to reconfigure as a proper time number that you can work with.

Upload the workbook (or at least the relevant part of it) so that we can see the problem in context.

[etaf]

you have 03 but not a zero in front of the 5
i suspect you will need to extract the numbers and then use if a time formula

is it exactly as formatted ? in text?

time(hours(),minutes(),seconds())

we could use find/search to find the H and extract the hours using LEFT() and then also ise the same info to extract the mins

would that work ...

[Michael Perry]

Excel Forum Example.xlsx

If it is indeed a proper time number then you can format it any way you want. What do you get if you test the cell with
=ISNUMBER(A1)
where A1 contains the time worked. If you get TRUE then it is a number. Anything else then it's text and will need string slicing to reconfigure as a proper time number that you can work with.

Upload the workbook (or at least the relevant part of it) so that we can see the problem in context.

Thanks for the quick reply Richard. I tried the formula you suggested and it returned FALSE unfortunately. Enclosed is sample of the workbook I'm talking about.

[popipipo]

Please Login or Register  to view this content.

Try this one

[Pepe Le Mokko]

Another way

Please Login or Register  to view this content.

and format as needed

[Michael Perry]

Your sample sheet gives dates in times in standard format. I fail to see the problem ?

The column in question is the "Total Time Worked" column which doesn't give dates and times in a standard format.

[Pepe Le Mokko]

The column in question is the "Total Time Worked" column which doesn't give dates and times in a standard format.

Yes sorry, I was looking in the wrong place and corrected post #6 accordingly

[Richard Buttrey]

Hi,

Another way

=LEFT(Q2,FIND(" ",Q2))/24+VALUE(MID(Q2,LEN(Q2)-8,2))/1440

[Michael Perry]

Sorry I'm just now getting back to this guys, I got swamped yesterday afternoon. You guys rock, all of the solutions proposed work well enough for me to use the output as real numbers. Thanks so much!

[Michael Perry]

Hey all, I had a follow up question to this. I formatted the cells to "h:mm" and used the suggested formula "=SUBSTITUTE(SUBSTITUTE(Q2,"hr(s)",":"),"min(s)","")+0". This took the input of "4 hr(s) 09 min(s)" and changed the output to 4:09.
I then I used use the average formula in Excel to give the average hours worked. Would this give me the correct data or would the format or formula used need to be different in some way? Thanks guys!