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.

 

[mse330]

Welcome to the forums !

If I understand correctly, you have 6 different prices that you are currently charging as per the below

Weekdays - Morning/Night
Saturdays - Morning/Night
Sundays - Morning/Night

Is it possible to have something in between ? e.g. shift from 10 PM until 10 AM ? Would you calculate this on a prorate method ?

 

[Wik321]

Thank you for ur time.
I have 4 rates. WeekDays (day 06:30 to 18:30 and 18:30 to 06:30 night) and one for Sayurday (24 hrs) and one for sunday. But when shift cross 12 midnight on say Friday, then it goes into Saturday and hence a different rate.
Hope this clarifies.

 

[mse330]

Thank you for ur time.
I have 4 rates. WeekDays (day 06:30 to 18:30 and 18:30 to 06:30 night) and one for Sayurday (24 hrs) and one for sunday. But when shift cross 12 midnight on say Friday, then it goes into Saturday and hence a different rate.
Hope this clarifies.

What is the current setup you have? A table with the data & time of the service? If you post a sample of your data perhaps we can suggest a formula to eliminate the manual work you're doing

 

[Wik321]

Hi Mse330,

Apologies for being late. this is a sample of just two shifts for the data i receive from operations team:

Date Book Start Book End Hours 22-07-17 7:00 15:40 8.67 19-07-17 18:30 6:30 12.00 <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

I break this down as (the first shift is Ok but the second has to be broken down)

Date	Day	Book Start	Book End	Charge Rate	Hours
22-07-17	Saturday	7:00	15:40	Saturday	8.67
19-07-17	Wednesday	18:30	0:00	Night	5.50
20-07-17	Thursday	0:00	6:30	Night	6.50

<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Although the total 12 hours in the second shift all are night rate hours but sometime after mid night the day changes to Saturday or Sunday for which there are different rates.

Regards,

 

[mse330]

Wik321, sorry for the late response but I am out of town on vacation & have minimal time with a PC ... The below code may not be the best way or code but perhaps this could help you with what you are currently doing

Based on the above information, I suggest to do the following

1. Save a new Macro Enabled workbook in your desktop & place the below code in a new Module
2. Paste the data as received from operations starting in cell A1
3. Run the Macro

Let me know if that works out for you

Sub CheckHours()

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

With ActiveSheet

    Range("B1").EntireColumn.Insert
    Cells(1, 2) = "Day"
    Cells(1, 5) = "Charge Rate"
    Cells(1, 6) = "Hours"

For x = 2 To lRow
    If Cells(x, 4) < Cells(x, 3) Then
        Rows(x + 1).EntireRow.Insert
        Cells(x + 1, 1) = Cells(x, 1) + 1
        Cells(x + 1, 3) = #12:00:00 AM#
        Cells(x + 1, 4) = Cells(x, 4)
        Cells(x, 4) = #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, 3)
    EndDate = Cells(x, 4)
    
    Cells(x, 2) = Format(Cells(x, 1), "DDDD")
    Cells(x, 5) = 0
    Cells(x, 6) = (EndDate - StartDate) - (StartDate > EndDate) * 24
    
    If Cells(x, 2) = "Saturday" Then
        Cells(x, 5) = "Saturday"
    ElseIf Cells(x, 2) = "Sunday" Then
        Cells(x, 5) = "Saturday"
    ElseIf Cells(x, 3) >= #6:30:00 PM# Or Cells(x, 4) <= #6:30:00 AM# Then
        Cells(x, 5) = "Night"
    Else
        Cells(x, 5) = "Day"
    End If
Next x

End With

End Sub

 

[Wik321]

Thank you so much mse330. My apologies for this distraction on your holidays. the magic things seems to be working except when its mid night time (00:00). please have a look at the data below. some of the hours calculations are not right (i multiplied the hours calculated by macro by 24)

Date	Day	Book Start	Book End	Charge Rate	Hours
22-05-17	Monday	7:30	16:00	Day	8.5
25-05-17	Thursday	7:30	17:30	Day	10
26-05-17	Friday	7:30	17:30	Day	10
27-05-17	Saturday	8:00	14:00	Saturday	6
22-05-17	Monday	19:30	20:30	Night	1
22-05-17	Monday	5:30	6:30	Night	1
23-05-17	Tuesday	5:30	6:30	Night	1
25-05-17	Thursday	6:00	16:00	Day	10
26-05-17	Friday	6:30	19:00	Day	12.5
22-05-17	Monday	23:00	0:00	Night	553
23-05-17	Tuesday	0:00	7:00	Day	7
26-05-17	Friday	19:00	0:00	Night	557
27-05-17	Saturday	0:00	6:30	Saturday	6.5
27-05-17	Saturday	15:00	0:00	Saturday	561
28-05-17	Sunday	0:00	3:00	Saturday	3
28-05-17	Sunday	18:30	0:00	Saturday	557.5
29-05-17	Monday	0:00	6:30	Night	6.5
22-05-17	Monday	18:30	0:00	Night	557.5
23-05-17	Tuesday	0:00	6:30	Night	6.5
23-05-17	Tuesday	15:00	0:00	Night	561
25-05-17	Thursday	15:00	0:00	Night	561
27-05-17	Saturday	6:30	18:30	Saturday	12
22-05-17	Monday	6:00	15:00	Day	9
23-05-17	Tuesday	18:30	6:30	Night	564
26-05-17	Friday	3:00	15:00	Day	12
28-05-17	Sunday	15:00	3:00	Saturday	564

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

Thanks again for your time and have a great time.

Regards,

 

[mse330]

Wik321, can you please highlight what exactly the issue in 1 example. Also, what do you mean by

(i multiplied the hours calculated by macro by 24)

 

[Wik321]

Hi mse330,

When i ran your code, the last column in the data above (hours) was showing decimal. e.g the first row was 0.35416667 in the last column. I assumed that your code calculated hours and multiplied it by 24 (number of hours in a day). This gives correct result 8.5 hrs (from 7:30 - 16:00). However when i copied it down, the result does not seem correct as it shows figures like 553 etc. The calculation of hours needs to be checked.

I hope i am making some sense.

Warm Regards

 

[mse330]

Hey Wik321

I honestly couldn't figure out why it is making it this way but I added another loop at the end of the code to overcome this issue ... Check the below updated code & let me know if that solves the issue for you

Sub CheckHours()

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

With ActiveSheet

    Range("B1").EntireColumn.Insert
    Cells(1, 2) = "Day"
    Cells(1, 5) = "Charge Rate"
    Cells(1, 6) = "Hours"

For x = 2 To lRow
    If Cells(x, 4) < Cells(x, 3) Then
        Rows(x + 1).EntireRow.Insert
        Cells(x + 1, 1) = Cells(x, 1) + 1
        Cells(x + 1, 3) = #12:00:00 AM#
        Cells(x + 1, 4) = Cells(x, 4)
        Cells(x, 4) = #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, 3)
    EndDate = Cells(x, 4)
    
    Cells(x, 2) = Format(Cells(x, 1), "DDDD")
    'Cells(x, 5) = 0
    Cells(x, 6) = (EndDate - StartDate) - (StartDate > EndDate) * 24
    
    If Cells(x, 2) = "Saturday" Then
        Cells(x, 5) = "Saturday"
    ElseIf Cells(x, 2) = "Sunday" Then
        Cells(x, 5) = "Saturday"
    ElseIf Cells(x, 3) >= #6:30:00 PM# Or Cells(x, 4) <= #6:30:00 AM# Then
        Cells(x, 5) = "Night"
    Else
        Cells(x, 5) = "Day"
    End If
Next x

For x = 2 To lRow
    If Cells(x, 6).Value > 1 Then
        Cells(x, 6).Value = TimeSerial(Hour(Cells(x, 6).Value), Minute(Cells(x, 6).Value), 0)
    End If
Next x

End With

End Sub