Converting duration or time into a number

**awcwa** · 03-27-2013, 04:39 PM

Hi, I have a dataset with a column that contains the duration of an event, that lasts up to 24 hours. The format is as follows: xxh xxm where h stands for hours and m stands for minutes. So if an event lasts 20 minutes it'll say 20 m. If an event lasts 2 hours 24 minutes it'll say 2 h 24 m. If it lasts 2 hours it'll just say 2 h. Is there a function I can write, so "20 m would be read as "20" and 2 h 24 m would be read as "144"? I just want the value in minutes. I can't sum anything or use these values in functions until I convert them into numbers. Thanks for your help!

-A

**FDibbins** · 03-27-2013, 04:54 PM

Hi

Not sure if this will work for you...in a new column, use this copied down, and format as number...

=HOUR(A1)*60+MINUTE(A1)

**awcwa** · 03-27-2013, 05:00 PM

I tried it but it didn't work. Maybe I need to format the column with the durations? "12 m" is simply read as "12 m" and not as a number so Excel can't recognize what to format as minutes and hours

**FDibbins** · 03-27-2013, 05:02 PM

I can't sum anything or use these values in functions until I convert them into numbers. Thanks for your help!

Your option will give you text

Is the time you are using an entered time, or the result if a calculation? If you F2 (edit) F9 (calc) what shows in the forumla bar?

**awcwa** · 03-27-2013, 05:05 PM

I don't understand your question. The time or duration, whatever you wish to call it, is entered in. So I just have a column of 12 m, 2 h, 3 h, etc etc. with no calculations. I want to make calculations using these numbers, but can't because of the letter "m" and "h". I guess one option would be to find and replace all ms with a blank, and then find all h's and replace with a blank, then multiply it by 60.

**judgeh59** · 03-27-2013, 05:17 PM

=IFERROR(VALUE(LEFT(A1,FIND("h",A1,1)-1)),0)*60+IFERROR(VALUE(MID(A1,IFERROR(FIND("h",A1,1),0)+1,3)),0)

its clunky but I tested against
2 h 24 m
2 h
20 m
and result were as expected...but you must be consistant on your input....HTH

**FDibbins** · 03-27-2013, 05:19 PM

OK I think I understand better now, I thought you meant that the data (duration) was entered as time (2:24)

Try this instead (assuming your data is in A1)...
=LEFT(A1,2)*60+(MID(A1,SEARCH("m",A1,1)-3,2)*1)

It would, of course, make things much easier if the data was entered correctly inthe 1st place AS time, using the time format 2:24

Edit: @ judgeh just fyi, you wont need the VALUE 1st part of your formula, you are already *60. and for the 2nd part, just *1, then you dont need the VALUE there either

Nice solution though

**judgeh59** · 03-27-2013, 05:30 PM

thanks....I guess I don't trust Excel to Cast it for me....but you are definitely correct....

**daddylonglegs** · 03-27-2013, 05:32 PM

This solution should also work with the format suggested

=SUM(MID(0&A1&"000",FIND({"h","m"},A1&"xxxhm")-2,2)*{60,1})

Converting duration or time into a number

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Re: Converting duration or time into a number

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions