?
Solved

Count Total Hours

Posted on 2011-10-26
14
Medium Priority
?
448 Views
Last Modified: 2012-05-12
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
Comment
Question by:Cartillo
  • 4
  • 4
  • 3
  • +2
14 Comments
 

Expert Comment

by:normajm400
ID: 37035968
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
 

Assisted Solution

by:normajm400
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)))

Open in new window

0
 

Author Comment

by:Cartillo
ID: 37036004
Hi normajm400,

Any chance to convert this formula into macro? Please assist.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Cartillo
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

by:Saqib Husain, Syed
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

by:Cartillo
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

by:
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
                    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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:barry houdini
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

by:Saqib Husain, Syed
ID: 37036760
>> Not for points...Why not?
0
 

Author Closing Comment

by:Cartillo
ID: 37037780
Hi,

Thanks a lot for the help.
0
 

Expert Comment

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

Expert Comment

by:barry houdini
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

by:Saqib Husain, Syed
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question