Need help by Timesheet

I have a timesheet created, with the examples that were in the forum.
I've gotten a lot of help, but knowledge is invaluable. (sigh)
I do not get out in the totals of hours.

1st attachment is how I filled my hours.
it's a bit clumsy, but for me it was eventually cluttered.
zonder puntjes.xlsm

2nd attachment is with the help of fellow forum users become more beautiful.
But ....
there are some points that can be better.
Map3.xlsm

In column O : normal hours 100 % there is 8 hours .
but if I have to work 4 hours, 4 hours standing. (Mon - Fri)
If I work more than 8 hours than the extra hours should be allocated.
My Employer is flexible with working hours, if total working hours is 8.
If there is little work, you finish work, clean up and go home. This is less than 8 hours of work.

In a quarterly census will only work on a Saturday .
Again, it is the more people count of the faster we go home .

Column T and U is the total hours worked (= sum of column N)
Box U4 : I have worked fewer hours , even if I have overtime one day .
I have 38 hours worked including overtime pay everything as 38 hours .
I have 40 hours worked including overtime pay everything as 40 hours .
I have 48 hours worked including overtime pay over 40 hours and 8 hours for holiday savings . ( Leave)

All times are entered as military format manually. 800 hours is 8:00.

Please work step by step. that i understand the formule.
keep the layout the same, if possible.

many thx/ love,
Mofasa

Moderator's note: See also this thread posted in Dutch --6StringJazzer

Split the cells so that hh is in one cell, then the mm is in an adjacent cell

Im sorry, I would seriously question that approach. Time, in excel, is actually a decimal of 1(day), and if it is entered as time, then the calcs become far simpler - otherwise you have to add minutes (which are numbers, not time) up to 60, carry the balance over, convert 60 to 1, add that to the hourse (which, again, are numbers, not time) an d when they get to 24, convert 24 to 1 etc etc

Once you understand how "time" works in excel, then you see that time is in fact a multiple of 10 (sort of)

Hoping I have not gone over previous advice, but its the joy of working with numbers that are not neat multiples of 10

I will take a look ane see what we can come up with

I am trying to work my way through your logic, row-by-row.

change the formula in column O to this...
=IF(V3=7,"",8/24) ("8:00" is text, not a number)

Change the formula in Q to this...
=IF(OR(N3="",O3>N3),"",N3-O3)

Change the formulas in R (all of them), to this...
=SUM($N$3:N3)

Once you have this done, let me know where the "errors" are

What are the rules governing OT?

1.25 is paid from how many hours to how many hours?
1.5 is paid from how many hours to how many hours?
Is there any extra payment for sunday?
etc

I understand you hesitation and sorry for the lack of information. It's because the time card is pretty complicated.

It allows individuals to enter in time easily in 6 different columns for multiple punch in and punch outs 8 for 8:00 am 830 for 830 am. Depending on what column time is entered in will determine what column the default hour AM or PM. It calculates vacation and sick time assuming a 8 hour day. It calculates correct overtime based on a 40 hour work week (this was quite difficult because my company's pay periods are 1st - 15th and 16 - end of month, you can imagine the headache when a week is split in half by the pay period). It will automatically calculate holiday pay based on code. It keeps track of all of this month to month so an employee needs only one time card file per year. etc..............

Here is the code the "ThisWorkbook" module

Private Sub Workbook_Open()
    Dim r As Range, Holiday As Range, h As Range, DateCel As Range
    Dim wb As Workbook
    Dim sFileName As String, EmpName As String
    Dim ws As Worksheet
    Dim d As Date
    
    Set wb = ThisWorkbook
    Set r = wb.Sheets(1).Range("C1")
    
    Application.EnableEvents = False
    Application.EnableAutoComplete = False
    
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            ws.Protect , UserInterfaceOnly:=True
        End If
    Next ws

    If IsEmpty(r) Then
        wb.Sheets(2).Range("B4") = DateSerial(Year(Now()), 1, 1)
        Call IsHoliday
        
    With Welcome
        .StartUpPosition = 0
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .Show
    End With
        EmpName = Welcome.TextBox1.Text
        If Welcome.TextBox1.Text = "Exit" Then
        Unload Welcome
        GoTo ExitMacro
        Else
        wb.Sheets(2).Range("L28").Value = 1.68  'Set amount of sick hours accrued each pay period
        wb.Sheets(2).Range("I21").Value = Welcome.SickHrs.Text
        wb.Sheets(2).Range("J21").Value = Welcome.VacHrs.Text
        wb.Sheets(1).Range("C1").Value = EmpName  'Insert name that was entered into the Welcom Form
        If Welcome.CheckBox1.Value = True Then
            StartDate = MsgBox(Prompt:="Will you reach 7 year seniority with Century this year?", _
            Buttons:=vbYesNo, Title:="")
                If StartDate = vbNo Then
                
                Else
                    StartDateInput = InputBox("Please enter the date you will reach seniority 'dd/mm/yyyy'")
                    wb.Sheets(1).Range("C27").Value = StartDateInput
                End If
        ElseIf Welcome.CheckBox2.Value = True Then
            wb.Sheets(2).Range("L29").Value = 5
        End If

        sFileName = EmpName & " Time Card " & Year(Now()) & ".xlsm"

        Application.Dialogs(xlDialogSaveAs).Show Arg1:=sFileName, Arg2:=xlOpenXMLWorkbookMacroEnabled
        Unload Welcome
        End If

    End If ' IsEmpty(r)
        Set ws = wb.Sheets(2)
        ws.Activate
        ws.Range("D4").Select
    Set wb = Nothing
    Set r = Nothing
    Set JanDate = Nothing
    Set ws = Nothing
    
ExitMacro:
    Application.EnableEvents = True
End Sub

Module that calculates Holidays

Sub IsHoliday()
    Dim r As Range
    Dim wb As Workbook
    Dim sFileName As String
    Dim pass As String
    Dim ws As Worksheet
    Dim DateCels As Range
    Dim DateCel As Range
    Dim d As Date
    Dim h As Range
    Dim Holiday As Range
    Dim IsHol As Boolean
    
    Dim EmpName As String
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            Set DateCels = ws.Range("B4:B18,B35:B50")
            Set Holiday = ws.Range("M4:M18,M35:M50")
                Holiday.ClearContents
                For Each DateCel In DateCels
                    If DateCel = "" Then
                    
                    Else
                    Set h = DateCel.Offset(, 11)
                    d = DateCel.Value
                    Select Case d
                         '1. New Year's Day
                         Case DateSerial(Year(d), 1, 1)
                            If Weekday(d) = 1 Then  'New Year's Day is on Sunday, New Years Eve is on Saturday
                                h.Offset(1, 0).Value = 8
                            ElseIf Weekday(d) = 7 Then  'New Years Day is on Saturday, New Year's Eve of year before is on Friday
                                
                            ElseIf Weekday(d) = 2 Then  'This means New Year's Day of the year before is on a Sunday
                            
                            Else
                                h.Value = 8
                            End If
                         
                         '2. Memorial Day -- Last Monday in May
                         Case DateSerial(Year(d), 5, Choose(Weekday(DateSerial(Year(d), 5, 1)), 30, 29, 28, 27, 26, 25, 31))
                            h.Value = 8
                         
                         '3. Independence Day
                         Case DateSerial(Year(d), 7, 4)
                            If Weekday(d) = 1 Then
                                h.Offset(1, 0).Value = 8
                            ElseIf Weekday(d) = 7 Then
                                h.Offset(-1, 0).Value = 8
                            Else
                                h.Value = 8
                            End If
                         '4. Labor Day -- First Monday in September
                         Case DateSerial(Year(d), 9, Choose(Weekday(DateSerial(Year(d), 9, 1)), 2, 1, 7, 6, 5, 4, 3))
                            h.Value = 8
                         
                         '5. Thanksgiving Day -- Fourth Thursday in November & Day afte Thanksgiving
                         Case DateSerial(Year(d), 11, Choose(Weekday(DateSerial(Year(d), 11, 1)), 26, 25, 24, 23, 22, 28, 27))
                            h.Value = 8
                            h.Offset(1, 0).Value = 8
                                                        
                            
                         '6. Christmas Eve and Christmas Day
                         Case DateSerial(Year(d), 12, 24)
                            If Weekday(d) = 1 Then  'Christmas Eve is on a Sunday which means Christmas day is on a Monday
                                h.Offset(1, 0).Value = 4
                                h.Offset(2, 0).Value = 8
                            ElseIf Weekday(d) = 7 Then
                                h.Offset(-1, 0).Value = 4  'Christmas Eve is on a Saturday which means Christmas Day is on a Sunday
                                h.Offset(2, 0).Value = 8
                            Else
                                h.Value = 4
                                h.Offset(1, 0).Value = 8
                            End If
                            
                         '7. New Year's Eve
                         Case DateSerial(Year(d), 12, 31)
                            If Weekday(d) = 1 Then   'New Year's Eve is on a Sunday, New Year's Day is on Monday
                                
                            ElseIf Weekday(d) = 6 Then  'New Years Eve is on Firday, New Year's Day is on Saturday
                                h.Offset(-1, 0).Value = 8 'Because New Year's Day's holiday pay gets taken on the Friday
                                h.Offset(-2, 0).Value = 4 'New Year's Eve's half day gets take on the Thursday
                            ElseIf Weekday(d) = 7 Then  'New Year's Eve is on Satruday, New Year's Day is on Sunday
                                h.Offset(-1, 0).Value = 4
                            Else
                                h.Value = 4
                            End If

                     End Select
                End If 'DateCel.Value = ""
                Next DateCel
        End If
    Next ws

End Sub

Hopefully that answers your questions, but it doesn't hurt my feelings if you don't look at it either. Just trying to be helpful.

CP

More Info...


There are 9 other modules and 5 UserForms

Part of the Sheet modules

Public tar As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rg1 As Range, rg2 As Range, rg3 As Range, rg4 As Range, rg5 As Range, rg6 As Range, rg7 As Range
    Dim Cel As Range, i As Range, u As Range, code As Range, Flex As Range, Sick As Range, Vac As Range, Hol As Range
    Dim hh As Integer, mm As Integer
    Dim CV As Double
    Dim CVtxt As String, codeval As String
    Dim d As Date
    
    Set tar = Target
    
    Set rg1 = Range("D4:D18,D35:D50")
    Set rg2 = Range("E4:E18,E35:E52")
    Set rg3 = Range("F4:F18,F35:F50")
    Set rg4 = Range("G4:G18,G35:G50")
    Set rg5 = Range("H4:H18,H35:H50")
    Set rg6 = Range("I4:I18,I35:I50")
    Set rg7 = Range("C4:C18,C35:C50")
    
    Set u = Union(rg1, rg2, rg3, rg4, rg5, rg6, rg7)
    
    If (Application.Intersect(Target, u) Is Nothing) Then GoTo EndMacro
   
    Application.EnableEvents = False
    Application.CutCopyMode = False

    
    'For cells after the code column - format time values
  
rg1:
    'For columns before the code column - format time values
    Set i = Intersect(Target, rg1)
    If i Is Nothing Then GoTo rg2
    On Error GoTo rg2


    For Each Cel In i
      Set code = Cel.Offset(0, -1)
      Set Sick = code.Offset(, 8)
      Set Vac = code.Offset(, 9)
      Set Hol = code.Offset(, 10)
      
      If Not Cel.HasFormula And Len(Cel.Text) > 0 And Hol.Value = 8 Then
        MsgBox "You have Holiday Pay for this day and cannot take sick or vacation hours!"
        Cel.ClearContents
        GoTo rg2
      ElseIf Not Cel.HasFormula And Len(Cel.Text) > 0 Then
        CV = Cel.Value
        If Int(CV) - CV = 0 Then    'No Time entered or time was entered without colon
          If CV < 100 Then CV = CV * 100
          hh = Fix(CV / 100)
          mm = CV - (hh * 100)
          Cel = TimeValue(hh & ":" & mm)
        End If
        'Cel.NumberFormat = "[$-409]h:mm AM/PM;@"   '"h:mm AM/PM"
      End If
      
        With code
            codeval = .Value
            code_s = InStr(1, codeval, "s") Or InStr(1, codeval, "S")
            code_v = InStr(1, codeval, "v") Or InStr(1, codeval, "V")
        End With
        If Cel = "" Then
            If code_s > 0 Then
                Sick = ""
            ElseIf code_v > 0 Then
                Vac = ""
            ElseIf code_v > 0 And code_s > 0 Then
                Vac = ""
                Sick = ""
                GoTo rg2   'Make sure this is correct!!!!!!!!!
                
            End If
        Else

        If codeval = "" Then
                GoTo rg2   'Make sure this is correct!!!!!!!!!
                
        Else
        With code
            If code_s > 0 And code_v > 0 Then
                Sick = ""
                Vac = ""
                Hrs = SpecialHrs(code)
                If Hrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                Else
                    Sick = Hrs * 0.5
                    Vac = Hrs * 0.5
                End If
                GoTo rg2   'Make sure this is correct!!!!!!!!!
                
            End If

            If code_s > 0 Then
                Sick = ""
                SickHrs = SpecialHrs(code)
                    If SickHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Sick = SickHrs
                    End If
            End If
            
            If code_v > 0 Then
                Vac = ""
                VacHrs = SpecialHrs(code)
                    If VacHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Vac = VacHrs
                    End If
            End If
            
        End With
        End If
        End If
    Next Cel
    
rg2:
    'For columns before the code column - format time values
    Set i = Intersect(Target, rg2)
    If i Is Nothing Then GoTo rg3
    On Error GoTo rg3
   
    For Each Cel In i
      Set code = Cel.Offset(0, -2)
      Set Sick = code.Offset(, 8)
      Set Vac = code.Offset(, 9)
      Set Hol = code.Offset(, 10)
      
      If Not Cel.HasFormula And Len(Cel.Text) > 0 And Hol.Value = 8 Then
        MsgBox "You have Holiday Pay for this day and cannot take sick or vacation hours!"
        Cel.ClearContents
        GoTo rg3
      ElseIf Not Cel.HasFormula And Len(Cel.Text) > 0 Then
        CV = Cel.Value
        If Int(CV) - CV = 0 Then    'No Time entered or time was entered without colon
          If CV < 100 Then CV = CV * 100
            hh = Fix(CV / 100)
            mm = CV - (hh * 100)
          If CV >= 100 And CV < 700 Then
          Cel = hh & ":" & mm & " PM"
          Else
          Cel = hh & ":" & mm
          End If
          End If
        End If
        

        With code
            codeval = .Value
            code_s = InStr(1, codeval, "s") Or InStr(1, codeval, "S")
            code_v = InStr(1, codeval, "v") Or InStr(1, codeval, "V")
        End With
        
        If Cel = "" Then
            If code_s > 0 Then
                Sick = ""
            ElseIf code_v > 0 Then
                Vac = ""
            ElseIf code_v > 0 And code_s > 0 Then
                Vac = ""
                Sick = ""

                GoTo rg3   'Make sure this is correct!!!!!!!!!
                
            End If
        Else

        If codeval = "" Then
                GoTo rg3   'Make sure this is correct!!!!!!!!!
                
        Else
        With code
            If code_s > 0 And code_v > 0 Then
                Sick = ""
                Vac = ""
                Hrs = SpecialHrs(code)
                If Hrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                Else
                    Sick = Hrs * 0.5
                    Vac = Hrs * 0.5
                End If
                GoTo rg3   'Make sure this is correct!!!!!!!!!
                
            End If

            If code_s > 0 Then
                Sick = ""
                SickHrs = SpecialHrs(code)
                    If SickHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                 Else
                        Sick = SickHrs
                    End If

            End If
            If code_v > 0 Then
                Vac = ""
                VacHrs = SpecialHrs(code)
                    If VacHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Vac = VacHrs
                    End If
            End If

        End With
        End If
        End If

    Next Cel

    
rg3:
    Set i = Intersect(Target, rg3)
    If i Is Nothing Then GoTo rg4
    On Error GoTo rg4
    
    For Each Cel In i
      Set code = Cel.Offset(0, -3)
      Set Sick = code.Offset(, 8)
      Set Vac = code.Offset(, 9)
      Set Hol = code.Offset(, 10)
      
      If Not Cel.HasFormula And Len(Cel.Text) > 0 And Hol.Value = 8 Then
        MsgBox "You have Holiday Pay for this day and cannot take sick or vacation hours!"
        Cel.ClearContents
        GoTo rg4
      ElseIf Not Cel.HasFormula And Len(Cel.Text) > 0 Then
        CV = Cel.Value
        If Int(CV) - CV = 0 Then    'No Time entered or time was entered without colon
          If CV < 100 Then CV = CV * 100
            hh = Fix(CV / 100)
            mm = CV - (hh * 100)
          If CV >= 800 And CV < 1200 Then
          Cel = hh & ":" & mm & " AM"
          Else
          Cel = hh & ":" & mm & " PM"
          End If
          End If
        End If
        
        With code
            codeval = .Value
            code_s = InStr(1, codeval, "s") Or InStr(1, codeval, "S")
            code_v = InStr(1, codeval, "v") Or InStr(1, codeval, "V")
        End With
        If Cel = "" Then
            If code_s > 0 Then
                Sick = ""
            ElseIf code_v > 0 Then
                Vac = ""
            ElseIf code_v > 0 And code_s > 0 Then
                Vac = ""
                Sick = ""
                GoTo rg4   'Make sure this is correct!!!!!!!!!
                
            End If
        Else

        If codeval = "" Then
                GoTo rg4   'Make sure this is correct!!!!!!!!!
                
        Else
        With code
            If code_s > 0 And code_v > 0 Then
                Sick = ""
                Vac = ""
                Hrs = SpecialHrs(code)
                If Hrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                Else
                    Sick = Hrs * 0.5
                    Vac = Hrs * 0.5
                End If
                GoTo rg4   'Make sure this is correct!!!!!!!!!
                
            End If
            If code_s > 0 Then
                Sick = ""
                SickHrs = SpecialHrs(code)
                    If SickHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Sick = SickHrs
                    End If

            End If
            If code_v > 0 Then
                Vac = ""
                VacHrs = SpecialHrs(code)
                    If VacHrs = "" Then
                 Application.EnableEvents = False
                    code.ClearContents
                 Application.EnableEvents = True
                    Else
                        Vac = VacHrs
                    End If
            End If

        End With
        End If
        End If
        
    Next Cel

I fill the week list updated where possible.
I have all the 00:00 o'clock indicator in the blue columns has taken away .

In Sheet3 I have put an explanation of what I did .
Time display in military time , I can not do . VBA I do not know.
I do not know how VBA works, and how to handle them. (sorry for this )

I do not come out in column T

In the year 2012 I've saved 88:34:00 hours . (T2)
But in the yellow columns I see the saved leave hours anymore.
Although I have worked less , I do not see them.

Under Box T -11 , I cheated.
The formula was max 40 hours and the rest is holiday hours ( hours for hours )

ONLY if really Very busy then the overtime is paying .
But that is only seasonal periods ( Valentine , mothersday , Christmas , etc )


problem:
How can I put in column T for hours .
Military timestamp fill via VBA .
(800 automatically 8:00 )
( 930 automatically 9:30 )
(1800 automatically 18:00 )

the layout please don't changed.

(thanks to Loius, België)

g ,
Mofasa

er zitten toch al veel voorwaardelijke opmaken in je bestand, dus waarom er niet ééntje aan toevoegen
een PHP voor je kolom P en als letterkleur kies je diezelfde als je achtergrond.

PHP Code: 

=OF(P1=0;L1="") 


Here is a simple time-sheet that calculates regular hours, overtime, weekend time, pay.