Solved

# Count Total Hours

Posted on 2011-10-26
Medium Priority
448 Views
Hi Experts,

I would like to request Experts help create a macro to automatically update Column D with total duration of each title. The total duration of the title is obtained by identify time deterrent between each title at column A (e.g. title 1 at 00:00hrs, title2 starts at 00:30hrs, the different is  30mins). II have manually update few sample at column D for Experts perusal. Hope Experts could help me create this macro.

Data-Consolidate.xls
0
Question by:Cartillo
• 4
• 4
• 3
• +2

Expert Comment

ID: 37035968
Try this formula in the column:
``````=IF(ISBLANK(A9),"",IF(ISERROR(A9-A8),"",A9-A8))
``````
It checks for a second value to calculate and if there isn't one, leaves the cell empty.
0

Assisted Solution

normajm400 earned 200 total points
ID: 37035974
oops - grabbed the wrong one - this should do it
``````=IF(ISBLANK(A10),"",IF(ISBLANK(A9),"",IF(ISERROR(A10-A9),"",A10-A9)))
``````
0

Author Comment

ID: 37036004
Hi normajm400,

Any chance to convert this formula into macro? Please assist.
0

Author Comment

ID: 37036012
Hi normajm400,

I noticed the formula always skipped the last 2 titles of each date, attached the result.
Data-Consolidate.xls
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 400 total points
ID: 37036093
You are using the correct formula in the wrong row.

Use this formula

=IF(ISBLANK(A9),"",IF(ISBLANK(A8),"",IF(ISERROR(A9-A8),"",A9-A8)))

in row 8

If this works then points go to normajm400
0

Author Comment

ID: 37036125
Hi ssaqibh,

Thanks for the help. The last title of each date is always empty. Have highlighted in yellow. How to fix this?
Data-Consolidate.xls
0

LVL 16

Accepted Solution

Peter Kwan earned 1000 total points
ID: 37036526
Hi,

Please try the following macro. I will assume your last row has the same duration as those in previous Wednesdays  (e.g. row 67, 248), so I manually set it to "03:00").

``````Sub CalDuration()
Dim r, r1, d, hd, md

r = 8
r1 = 8

With Sheet1

Do While r <= .UsedRange.Rows.Count

If .Cells(r, 1) <> "" And IsNumeric(.Cells(r, 1)) And InStr(.Cells(r1, 1), "/") = 0 Then
currenttime = .Cells(r, 1)

r1 = r + 1

Do While Not (.Cells(r1, 1) <> "" And IsNumeric(.Cells(r1, 1)) And InStr(.Cells(r1, 1), "/") = 0)
r1 = r1 + 1
If r1 > .UsedRange.Rows.Count Then
Exit Do
End If
Loop

nexttime = .Cells(r1, 1)

If nexttime < currenttime Then
End If

d = DateDiff("n", currenttime, nexttime)

.Cells(r, 4) = Format(CInt(d / 60), "00") & ":" & Format(CInt(d Mod 60), "00")

End If

If r1 > .UsedRange.Rows.Count Then
.Cells(r, 4) = TimeValue("03:00")
End If

r = r1

Loop

End With

End Sub
``````
0

LVL 43

Expert Comment

ID: 37036630
Cartillo, you would have to indicate how to calculate the duration for the last line.

The following formula will calculate the difference from the first title on the next date for the last title for each date.

=IF(ISBLANK(A8),"",IF(ISERROR(A9-A8),"",IF(ISBLANK(A9),A12-A8+1,A9-A8)))
0

LVL 50

Assisted Solution

barry houdini earned 400 total points
ID: 37036749
Not for points...

...but you could ensure that both cells have numbers (i.e. times) with COUNT and thereby reduce this

=IF(ISBLANK(A9),"",IF(ISBLANK(A8),"",IF(ISERROR(A9-A8),"",A9-A8)))

to this

=IF(COUNT(A8,A9)=2,A9-A8,"")

regards, barry
0

LVL 43

Expert Comment

ID: 37036760
>> Not for points...Why not?
0

Author Closing Comment

ID: 37037780
Hi,

Thanks a lot for the help.
0

Expert Comment

ID: 37038071
Thanks barryhoudini for pointing out the "Count" function.  That will be useful!
0

LVL 50

Expert Comment

ID: 37038121
>> Not for points...Why not?

Hello Saqib,

It looked to me like there were existing suggestions that worked OK, my version was simply a "tweak" to those....

regards, barry
0

LVL 43

Expert Comment

ID: 37038338
Yes, Barry the other solutions work but the beauty of your formula does deserve credit. Whenever I am stuck with a formula my first reflex is "Barry would do this with ease and elegance."

Saqib
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦