Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Time Counter

  1. #1
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0

    Exclamation Time Counter

    Hi, I have a time-sheet where employees book their time worked on various Jobs. Each Job (Reg5) also have certain activities (Reg6) connected to it. There is also an Hours Worked Textbox (Reg7). So it means a person will fill in the time sheet for each job he has done a day. For example Tom has worked on three jobs on Monday so he enters the time sheet and captures his time.... the First Job was 1 hour, the second 3 hrs and the last 4 hrs to make up for the 8 hours a day. Currently I have a counter that shows the time booked and the time left but it is not working perfectly. What I want is the following each time Tom fills in the time sheet the counters add up the hours if he gets past 8 hrs it must give a warning. Also on a Friday it is only 6 hrs total work time. The counter must be able to recognize each employee and counts their time book individually. The data is captured on Sheet 2 (Data) - Date, Weekday, LastName, FirstName, Job, Activity ID, Hours Worked and Payroll category. I do have a demo program but it is too large to upload - contact me for more detail.
    Code:
    Private Sub Reg7_change()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    On Error Resume Next
    Sheets.Add
    Range("A1").Formula = "=SUMIFS(Data!I:I,Data!C:C," & Reg1.Value & ",Data!E:E," & Reg3.Value & ",Data!F:F," & Reg4.Value & ")+ " & Reg7.Value & ""
    Label29 = Range("A1").Value
    Label32 = 8 - Label29
    
    ActiveSheet.Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    Last edited by Mnet; 12-07-2017 at 02:38 AM. Reason: SOLVED

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    Hi Mnet,

    Welcome to board !

    Can you please attach the workbook ?

    Cut down the data if it's huge in size.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    Thank you - It is still too big even when cutting down almost all the data - Please see my private message. Thanks in advance...

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    keep 10 rows of data, zip the file and upload.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0

    Red face Time Counter

    Dear Admin, hereby the data as discussed - Thank you!
    Attached Files Attached Files

  6. #6
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    Use this code. You don't need to create a sheet each time for calculation, instead use application.worksheetfunction.

    Code:
        Dim TotHours    As Double
        Dim rngData     As Range
        
        With Worksheets("Data")
            Set rngData = .Range("c2:i" & .Range("c" & .rows.Count).End(xlUp).Row)
        End With
        TotHours = Application.WorksheetFunction.SumIfs(rngData.Columns(7), rngData.Columns(1), CDate(Reg1.Value), rngData.Columns(3), Reg3.Value, rngData.Columns(4), Reg4.Value)
        
        TotHours = TotHours + CDbl(Reg7.Value)
        
        Label29 = TotHours
        If TotHours > 8 Then
            MsgBox "Your warning message", vbExclamation
            Exit Sub
        End If
        
        Label32 = 8 - TotHours
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  7. #7
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    Wonderful!!! Thank you it looks great!! Thank you for your time and effort it is much appreciated - On a Friday it is 6 hours would it be possible to distinguish between the 8 and 6 hours when filling in the time worked??
    Last edited by Mnet; 12-06-2017 at 09:37 AM.

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,099
    Rep Power
    10
    You are welcome!

    Code:
        Dim TotHours    As Double
        Dim rngData     As Range
        Dim CutOff      As Double
        
        CutOff = 8
        If Application.WorksheetFunction.Weekday(DTPicker1, 2) = 5 Then CutOff = 6 'for Friday
        
        With Worksheets("Data")
            Set rngData = .Range("c2:i" & .Range("c" & .rows.Count).End(xlUp).Row)
        End With
        TotHours = Application.WorksheetFunction.SumIfs(rngData.Columns(7), rngData.Columns(1), CDate(Reg1.Value), rngData.Columns(3), Reg3.Value, rngData.Columns(4), Reg4.Value)
        
        TotHours = TotHours + CDbl(Reg7.Value)
        
        Label29 = TotHours
        If TotHours > CutOff Then
            MsgBox "Your warning message", vbExclamation
            Exit Sub
        End If
        
        Label32 = CutOff - TotHours
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Senior Member
    Join Date
    Jun 2012
    Posts
    327
    Rep Power
    6
    Weekday is a regular VBA-function

    instead of
    Code:
    If Application.WorksheetFunction.Weekday(DTPicker1, 2) = 5 Then
    use
    Code:
    If Weekday(DTPicker1, 2) = 5 Then

  10. #10
    Junior Member
    Join Date
    Dec 2017
    Posts
    6
    Rep Power
    0
    Excellent!! Thanks again for helping me - you are very kind!

Similar Threads

  1. Extract Time in 24H format from Date and time.
    By zorro in forum Excel Help
    Replies: 3
    Last Post: 09-01-2016, 07:50 PM
  2. Extract Time from a Date&time cell
    By Feebles in forum Excel Help
    Replies: 4
    Last Post: 11-09-2015, 06:54 AM
  3. Calculate Time Difference Between Time In HH:MM
    By Stalker in forum Excel Help
    Replies: 8
    Last Post: 03-28-2013, 03:57 PM
  4. Displayin Date/Time in "original" time zone
    By Rasm in forum Excel Help
    Replies: 4
    Last Post: 04-21-2012, 02:32 AM
  5. Spreading a time range (shift time, etc) in columns.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 1
    Last Post: 08-23-2011, 12:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •