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

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

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

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

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

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

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

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

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

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

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

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

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