Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i keep a journal or log with excel? nancerella tapioca pudding fox Excel Discussion (Misc queries) 1 August 21st 06 12:59 PM
how do i convert a mailing list in excel to outlook chvbob Excel Discussion (Misc queries) 4 October 14th 05 05:43 PM
convert duration (hr:mn:sec) in to equivalent sec pg Excel Discussion (Misc queries) 2 April 26th 05 07:54 AM
convert decimal number to time : convert 1,59 (minutes, dec) to m agenda9533 Excel Discussion (Misc queries) 8 January 20th 05 11:24 PM
How do I convert a time eg. 00:01:39 to a duration eg. 1.65 min o. Louis Excel Worksheet Functions 1 November 22nd 04 02:36 PM


All times are GMT +1. The time now is 09:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"