Adding Time written with Text

[Anne Troy]

I'm sure I've seen it before, but I haven't a clue where to begin and can't find it in a Google search.

Suppose I have values such as:

1 hrs 40 mins
2 hrs 30 mins
13 hrs 10 mins

How can I add them up?

[mdmackillop]

Hi Dreamboat,
The following UDF will return a figure in hours and decimals


Function AddTime(Times As Range)
Dim Tim As Double
Dim Cel, i As Long, j As Long, HR As Long, MT As Double
For Each Cel In Times
i = InStr(1, Cel, "hrs")
j = InStr(1, Cel, "mins")
HR = Left(Cel, i - 2) * 1
MT = Mid(Cel, i + 4, j - (i + 5)) / 60
Tim = Tim + HR + MT
Next
AddTime = Tim
End Function


For hh:mm, change to AddTime = Tim/24 and format the cell to [h]:mm

[Anne Troy]

No way to do it without VBA?
Also, on what cells will the code you provide work?
Am I asking stupid questions?
:)

[Anne Troy]

Oh! And thank you!!

[Jacob Hilderbrand]

Looks like the Range is the Argument for the function so just send the range well calling the function.

[Jacob Hilderbrand]

Non-VBA solution.

Assuming this text is in A:A then:


=LEFT(A1,FIND("hrs",A1,1)-1)*60 + SUBSTITUTE(MID(A1,FIND("hrs",A1,1)+4,LEN(A1)-5)," mins","")

Fill Down.
Sum the Column.
This will be the total minutes.

[mdmackillop]

No way to do it without VBA?
And there was me thinking that this site was called VBAExpress.:devil:

[mdmackillop]

Divide Jakes formula by 1440 and format it as before for hh:mm

[Anne Troy]

Egads! LOL. Okay, guys. I'm going to answer this question now...
http://forums.computertim.com/viewtopic.php?t=1674

I have a friend over there who PMs me for just about all the Office questions.

Thanks on their behalf!

[Anne Troy]

Strange. Can't get it to work. I get 120 minutes for 2.5 hours.

[HaHoBe]

Hi, Dreamboat,

maybe I?m wrong but I think this could do the job:

=LEFT(A1,FIND("hr",A1,1)-1)*60 + MID(A1,LEN(A1)-6,2)
Or go with

=LEFT(A1,FIND("hr",A1,1)-1)*60 + SUBSTITUTE(MID(A1,FIND("hr",A1,1)+3,LEN(A1)-5)," mins","")
but I can?t explain why the formula needs other parameters and doesn?t perform the way I thought to know it was planned to work... :bug: :dunno

OK, even I understood the formula at last - it?s way too early in the morning for me by now. :eek:

Ciao,
Holger

[ edited a couple of times because somebody ought to shutdown the pc right away ;) ]

[mdmackillop]

Holger is right with the formula and here's a formatted version, with both the formula and UDF solutions,