View Full Version : Adding Time written with Text

Anne Troy
06-11-2005, 09:05 AM
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?

06-11-2005, 10:43 AM
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
AddTime = Tim
End Function

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

Anne Troy
06-11-2005, 12:01 PM
No way to do it without VBA?
Also, on what cells will the code you provide work?
Am I asking stupid questions?

Anne Troy
06-11-2005, 12:02 PM
Oh! And thank you!!

Jacob Hilderbrand
06-11-2005, 12:30 PM
Looks like the Range is the Argument for the function so just send the range well calling the function.

Jacob Hilderbrand
06-11-2005, 12:34 PM
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.

06-11-2005, 01:00 PM
No way to do it without VBA?
And there was me thinking that this site was called VBAExpress.:devil:

06-11-2005, 01:52 PM
Divide Jakes formula by 1440 and format it as before for hh:mm

Anne Troy
06-11-2005, 02:00 PM
Egads! LOL. Okay, guys. I'm going to answer this question now...

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

Thanks on their behalf!

Anne Troy
06-11-2005, 02:08 PM
Strange. Can't get it to work. I get 120 minutes for 2.5 hours.

06-11-2005, 02:49 PM
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:


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

06-11-2005, 03:00 PM
Holger is right with the formula and here's a formatted version, with both the formula and UDF solutions,