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?

mdmackillop

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

Next

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.

mdmackillop

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:

mdmackillop

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...

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

06-11-2005, 02:08 PM

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

HaHoBe

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:

Ciao,

Holger

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

mdmackillop

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,

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.