Need to convert Outlook Journal Duration to a number
 

How do I create a formula that converts a Outlook Journal Duration to a
number that represents hours:For Instance - I want the following to all be
hours without the word "Hours" or "Hour" or "minutes" (I want to use the
number of hours in another formula....
Duration
3.5 hours
1 hour
45 minutes
2 hours
All this is in one column -
I got as far as if(isnumber(search("hour",A2)), but now what? How do I get
the 3.5 to use it in a formula...

Thanks

Need to convert Outlook Journal Duration to a number
 

=LEFT(A2,FIND(" ",A2)-1)/IF(ISNUMBER(SEARCH("minute",A2)),60,1)
--
David Biddulph

"JRO Mtn Group" wrote in message
...
How do I create a formula that converts a Outlook Journal Duration to a
number that represents hours:For Instance - I want the following to all be
hours without the word "Hours" or "Hour" or "minutes" (I want to use the
number of hours in another formula....
Duration
3.5 hours
1 hour
45 minutes
2 hours
All this is in one column -
I got as far as if(isnumber(search("hour",A2)), but now what? How do I get
the 3.5 to use it in a formula...

Thanks

Need to convert Outlook Journal Duration to a number
 

David - thanks - I figured it out very early this am - was on a roll...
=IF(ISNUMBER(SEARCH("h",D2)),SUBSTITUTE(D2,"h","") ,IF(ISNUMBER(SEARCH("m",D2)),SUBSTITUTE(D2,"m","")/60,IF(ISNUMBER(SEARCH("d",D2)),SUBSTITUTE(D2,"d"," ")*24,"wrong")))
I appreciate you help

"David Biddulph" wrote:

=LEFT(A2,FIND(" ",A2)-1)/IF(ISNUMBER(SEARCH("minute",A2)),60,1)
--
David Biddulph

"JRO Mtn Group" wrote in message
...
How do I create a formula that converts a Outlook Journal Duration to a
number that represents hours:For Instance - I want the following to all be
hours without the word "Hours" or "Hour" or "minutes" (I want to use the
number of hours in another formula....
Duration
3.5 hours
1 hour
45 minutes
2 hours
All this is in one column -
I got as far as if(isnumber(search("hour",A2)), but now what? How do I get
the 3.5 to use it in a formula...

Thanks

Need to convert Outlook Journal Duration to a number
 

Your formula doesn't cope with inputs in the format which you quoted in your
original message, but I'm glad that you're happy with the answer.
--
David Biddulph

"JRO Mtn Group" wrote in message
...
David - thanks - I figured it out very early this am - was on a roll...
=IF(ISNUMBER(SEARCH("h",D2)),SUBSTITUTE(D2,"h","") ,IF(ISNUMBER(SEARCH("m",D2)),SUBSTITUTE(D2,"m","")/60,IF(ISNUMBER(SEARCH("d",D2)),SUBSTITUTE(D2,"d"," ")*24,"wrong")))
I appreciate you help

"David Biddulph" wrote:

=LEFT(A2,FIND(" ",A2)-1)/IF(ISNUMBER(SEARCH("minute",A2)),60,1)
--
David Biddulph

"JRO Mtn Group" wrote in message
...
How do I create a formula that converts a Outlook Journal Duration to a
number that represents hours:For Instance - I want the following to all
be
hours without the word "Hours" or "Hour" or "minutes" (I want to use
the
number of hours in another formula....
Duration
3.5 hours
1 hour
45 minutes
2 hours
All this is in one column -
I got as far as if(isnumber(search("hour",A2)), but now what? How do I
get
the 3.5 to use it in a formula...

Thanks