| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i keep a journal or log with excel? | Excel Discussion (Misc queries) | |||
| how do i convert a mailing list in excel to outlook | Excel Discussion (Misc queries) | |||
| convert duration (hr:mn:sec) in to equivalent sec | Excel Discussion (Misc queries) | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
| How do I convert a time eg. 00:01:39 to a duration eg. 1.65 min o. | Excel Worksheet Functions | |||
Linear Mode
