On Wed, 16 May 2007 18:20:16 -0700, "Bruce" wrote:

Given a string similar to the one listed below (this is actually testing the
driving direction - printer friendly version pasted into one cell column BC)

Total Est. Time: 1 hour, 35 minutes Total Est. Distance: 94.75 miles

I want to get the time needed to travel in a format that can be added or
subtracted from a time in the format of "7:00PM" (Column D)

I have been using the following (column BE)
=IF(ISERROR(VALUE(MID(BC33,SEARCH("Total Est. Time:",BC33)+16,SEARCH("Total
Est. Distance:",BC33)-(SEARCH("Total Est.
Time:",BC33)+24)))),"",VALUE(MID(BC33,SEARCH("Tot al Est.
Time:",BC33)+16,SEARCH("Total Est. Distance:",BC33)-(SEARCH("Total Est.
Time:",BC33)+24))))


and then =D32 -TIME(0,BE32,0) (this is in Column T )

This seems fine, until it is over 59 minutes. The issue is in the BE column
test. In this case, it returns an empty value.

Thanks!


Excel stores times and dates in days and fractions of a day. It would be
easiest if you used the same method.

You want to ensure that your 7:00PM is an "Excel Time", so it should be entered
as 7:00 PM (note the space before the P). You can also enter it as 7 p or
other variations; but you can't enter it as 7:00PM.

You could format it to DISPLAY without the <space but you still have to enter
it in one of the ways I showed.

In the above text string, you need to generate a value equal to

1/24 + 35/1440

How you extract the "1" and the "35" depends on the variations in your text
string. You can certainly use a combination of FIND, MID, etc.

One simple way is to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr

Then, assuming that the hours are followed by the word "hour"; and minutes by
the word "minu", you can use the following:

Hours: =REGEX.MID(A1,"\d+(?=\s*hour)")
Minutes:=REGEX.MID(A1,"\d+(?=\s*minu)")

To convert it into a value that you can add/subtract from an Excel Time:

=REGEX.MID(A1,"\d+(?=\s*hour)")/24+
REGEX.MID(A1,"\d+(?=\s*min)")/1440

HTH
--ron