• Status: Solved
• Priority: Medium
• Security: Public
• Views: 453

# Count Total Hours

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
Cartillo
• 4
• 4
• 3
• +2
4 Solutions

Commented:
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

Commented:
oops - grabbed the wrong one - this should do it
=IF(ISBLANK(A10),"",IF(ISBLANK(A9),"",IF(ISERROR(A10-A9),"",A10-A9)))
0

Author Commented:
Hi normajm400,

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

Author Commented:
Hi normajm400,

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

EngineerCommented:
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 Commented:
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

Analyst ProgrammerCommented:
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
nexttime = DateAdd("h", 24, nexttime)
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

EngineerCommented:
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

Commented:
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

EngineerCommented:
>> Not for points...Why not?
0

Author Commented:
Hi,

Thanks a lot for the help.
0

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

Commented:
>> 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

EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 4
• 4
• 3
• +2
Tackle projects and never again get stuck behind a technical roadblock.