charge rates according to shift spans

[Wik321]

Hi all,
I need to invoice my client for security services. The rate is not fixed but based on shift timings which is: 06:30 to 18:30 day shift 18:30 to 06:30 night shift. Also rate is different for saturdays and Sunday. I am currently doing it manually.
Any help will be much appreciated.

 

[Wik321]

Hi mse330,

You are the man. this has worked. Just a small correction. in the code where you have defined the "charge rate" please mark sunday as equal to "Sunday" and not Saturday. Also in the data, i usually have client name in column A and then date, can you please adjust the code for that.

Many thanks for your help and time. Really appreciate it.

Regards,

 

[mse330]

Wik321, I have made the necessary changes as in the below code. Try it out & let me know if it works

Sub CheckHours()

Dim lRow As Long, x As Long
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Range("C1").EntireColumn.Insert
Cells(1, 3) = "Day"
Cells(1, 6) = "Charge Rate"
Cells(1, 7) = "Hours"

For x = 2 To lRow
    If Cells(x, 5) < Cells(x, 4) Then
        Rows(x + 1).EntireRow.Insert
        Cells(x + 1, 1) = Cells(x, 1)
        Cells(x + 1, 2) = Cells(x, 2) + 1
        Cells(x + 1, 4) = #12:00:00 AM#
        Cells(x + 1, 5) = Cells(x, 5)
        Cells(x, 5) = #12:00:00 AM#
    End If
Next x

lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Dim StartDate As Date, EndDate As Date

For x = 2 To lRow
    StartDate = Cells(x, 4)
    EndDate = Cells(x, 5)
    
    Cells(x, 3) = Format(Cells(x, 2), "DDDD")
    Cells(x, 7) = (EndDate - StartDate) - (StartDate > EndDate) * 24
    
    If Cells(x, 3) = "Saturday" Then
        Cells(x, 6) = "Saturday"
    ElseIf Cells(x, 3) = "Sunday" Then
        Cells(x, 6) = "Sunday"
    ElseIf Cells(x, 4) >= #6:30:00 PM# Or Cells(x, 5) <= #6:30:00 AM# Then
        Cells(x, 6) = "Night"
    Else
        Cells(x, 6) = "Day"
    End If
    
    If Cells(x, 7).Value > 1 Then
        Cells(x, 7).Value = TimeSerial(Hour(Cells(x, 7).Value), Minute(Cells(x, 7).Value), Second(Cells(x, 7).Value))
    End If
Next x

End Sub