| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible Text Conversion
How would it be possible to convert the below text into a number that Excel
will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
for the first group of 7Days 19 hours 41 minutes, assuming that is an elapsed
time until the current time: =NOW()-DAY(7)-HOUR(19)-MINUTE(41) Paste the formula into a cell it will give you the date and time that the count started. "Rob" wrote: How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
Assuming data is in D3:D7, try
=SUMPRODUCT((LEFT(D3:D7,FIND("days",D3:D7)-1)*24)+(MID(D3:D7,FIND("days",D3:D7)+4,FIND("hours ",D3:D7)-FIND("days",D3:D7)-4))+(MID(D3:D7,FIND("hours",D3:D7)+5,FIND("minutes ",D3:D7)-FIND("hours",D3:D7)-5)/60)) should give you the number of hours, expressed as a decimal. Change ranges as needed. "Rob" wrote: How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
On Thu, 5 Oct 2006 17:10:02 -0700, Rob wrote:
How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob Kind of. And exactly how to do it depends on what you want to do with the result. Excel stores these values as days and fractions of days. A problem is that if the number of days gets to 32, excel, when displaying the value, will roll it over to 1. So if you have stored, for example, 32.5 representing 32 days 12 hours, and you format it as d:hh:mm, Excel will display 1:12:00 and not 32:12:00 The value can still be used in date arithmetic, however: e.g. =NOW+32.5 will give a valid date. Or, you could format the result as General and get the total of days and fraction of a day. To just convert to a value Excel understands as a date/time, assuming the number of days is less than 32, first the parsing is done most simply by downloading and installing Longre's free morefunc.xll add-in from http://xcell05.free.fr/ This add-in can be easily distributed with the workbook, if that is an issue. Then use the formula: =SUMPRODUCT(--REGEX.MID(A1,"\d+",{1,2,3}),{1,0.0416666667,0.0006 94444444}) To SUM the entries in a single formula, assuming your values are in a single column (A1:A5), you can use the following formula: =SUMPRODUCT(MMULT(--REGEX.MID(A1:A5,"\d+",{1,2,3}),{1;0.0416666667;0.0 00694444444})*1) Format the result as General or Number with the required number of decimals. The result will be in Days and fractions of a day, and you can add or subtract from Excel dates. The second array constant represents 1 0.041666..7 = 1/24 0.00069444... = 1/24/60 So it converts the days, hours, minutes into days and fractions of a day. The formulas assume that no decimal fractions are used, and that every entry has a digit entry for days, hours and minutes. If the format is different, a slight modification might be required. It is certainly possible to parse out the numbers using built-in functions, but it's more cumbersome. --ron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
On Thu, 5 Oct 2006 21:54:22 -0400, "Tom Ogilvy" wrote:
With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TR IM(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days Very nice. --ron |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
A much more elegant solution.
"Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
I agree, very nice.
"Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
A much more elegant solution.
"Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
Sorry - I kept getting error messages and didn't think the post was being
accepted. "JMB" wrote: A much more elegant solution. "Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
Just outstanding. Thanks.
"Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
You can never say enough positive things about my solutions <g Feel free to
post away! Don't give it a second thought. -- Regards, Tom Ogilvy "JMB" wrote: Sorry - I kept getting error messages and didn't think the post was being accepted. "JMB" wrote: A much more elegant solution. "Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Possible Text Conversion
I hoped you wouldn't be too upset <g. After a few supposedly failed
attempts to post, it got personal. "Tom Ogilvy" wrote: You can never say enough positive things about my solutions <g Feel free to post away! Don't give it a second thought. -- Regards, Tom Ogilvy "JMB" wrote: Sorry - I kept getting error messages and didn't think the post was being accepted. "JMB" wrote: A much more elegant solution. "Tom Ogilvy" wrote: With your data in A1:A5 =SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRI M(A1:A5)," days "," Jan 1900 ")," hours ",":")," minutes","")) gives me 32.525 days -- Regards, Tom Ogilvy "Rob" wrote in message ... How would it be possible to convert the below text into a number that Excel will recognize as date/time? Or... How would it be possible to sum the below numbers given that it is recognized as text by Excel? 7 days 19 hours 41 minutes 7 days 19 hours 41 minutes 1 days 9 hours 47 minutes 3 days 11 hours 20 minutes 12 days 0 hours 07 minutes Thank in Advance. TOTALLY! Rob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conversion of Numbers into Text | Excel Discussion (Misc queries) | |||
| Text Date Conversion | Excel Discussion (Misc queries) | |||
| Text to time conversion | Excel Discussion (Misc queries) | |||
| text conversion problem | Excel Programming | |||
| Text Conversion | Excel Programming | |||
Linear Mode
