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

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
Asked:
Cartillo
  • 4
  • 4
  • 3
  • +2
4 Solutions
 
normajm400Commented:
Try this formula in the column:
=IF(ISBLANK(A9),"",IF(ISERROR(A9-A8),"",A9-A8))

Open in new window

It checks for a second value to calculate and if there isn't one, leaves the cell empty.  
0
 
normajm400Commented:
oops - grabbed the wrong one - this should do it
=IF(ISBLANK(A10),"",IF(ISBLANK(A9),"",IF(ISERROR(A10-A9),"",A10-A9)))

Open in new window

0
 
CartilloAuthor Commented:
Hi normajm400,

Any chance to convert this formula into macro? Please assist.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
CartilloAuthor Commented:
Hi normajm400,

I noticed the formula always skipped the last 2 titles of each date, attached the result.
Data-Consolidate.xls
0
 
Saqib Husain, SyedEngineerCommented:
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
 
CartilloAuthor 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
 
Peter KwanAnalyst 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

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
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
 
barry houdiniCommented:
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
 
Saqib Husain, SyedEngineerCommented:
>> Not for points...Why not?
0
 
CartilloAuthor Commented:
Hi,

Thanks a lot for the help.
0
 
normajm400Commented:
Thanks barryhoudini for pointing out the "Count" function.  That will be useful!
0
 
barry houdiniCommented:
>> 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
 
Saqib Husain, SyedEngineerCommented:
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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