Possible Text Conversion

[Guest]

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

 

[Guest]

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.

 

[Guest]

Assuming data is in D37, try
=SUMPRODUCT((LEFT(D37,FIND("days",D37)-1)*24)+(MID(D37,FIND("days",D37)+4,FIND("hours",D37)-FIND("days",D37)-4))+(MID(D37,FIND("hours",D37)+5,FIND("minutes",D37)-FIND("hours",D37)-5)/60))

should give you the number of hours, expressed as a decimal. Change ranges
as needed.

 

[Ron Rosenfeld]

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.000694444444})

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.000694444444})*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

 

[Tom Ogilvy]

With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1:A5)," days "," Jan
1900 ")," hours ",":")," minutes",""))

gives me
32.525 days

 

[Ron Rosenfeld]

With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1:A5)," days "," Jan
1900 ")," hours ",":")," minutes",""))

gives me
32.525 days

Very nice.
--ron

 

[Guest]

A much more elegant solution.

With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1:A5)," days "," Jan
1900 ")," hours ",":")," minutes",""))

gives me
32.525 days

 

[Guest]

I agree, very nice.

With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1:A5)," days "," Jan
1900 ")," hours ",":")," minutes",""))

gives me
32.525 days

 

[Guest]

A much more elegant solution.

With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1:A5)," days "," Jan
1900 ")," hours ",":")," minutes",""))

gives me
32.525 days

 

[Guest]

Sorry - I kept getting error messages and didn't think the post was being
accepted.

 

[Guest]

Just outstanding. Thanks.

With your data in A1:A5
=SUMPRODUCT(1*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1:A5)," days "," Jan
1900 ")," hours ",":")," minutes",""))

gives me
32.525 days

 

[Guest]

You can never say enough positive things about my solutions <g> Feel free to
post away! Don't give it a second thought.

 

[Guest]

I hoped you wouldn't be too upset <g>. After a few supposedly failed
attempts to post, it got personal.