Time format in excel

[abugharibh]

I have extracted some data from a healthcare system at work. It includes time differences that appear like this : 1h 04m or 25h 35m
I need to know how many entries are >24 hours and how many < 24 but the problem icouldnt deal with that kind of format.
Please how can i change that to a regular time format: 1:04 and 25:35

 

[theBardd]

Do a replace, replace h and space with :, m with nothing.

 

[joeu2004]

I presume the colon is not part of the data; that is, "1h 04m", not ": 1h 04m".

Use an formula of the form:

=--TRIM(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m",""))

format as Custom [h]:mm to convert to numeric time.

If that formula is in B1:B10, use the following to count how many are less than 24 hours:

=COUNTIF(B1:B10, "<1")

Excel stores time as a fraction of a day. So 1 = 24 hours.

 

[abugharibh]

Thanks a lot guys