charge rates according to shift spans

W

Wik321

New Member
Joined
Aug 4, 2017
Messages
6
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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 ?
 
Upvote 0
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.
 
Upvote 0
Wik321 said:
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.
Click to expand...

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
 
Upvote 0
Hi Mse330,

Apologies for being late. this is a sample of just two shifts for the data i receive from operations team:
[TABLE="width: 309"]
<tbody>[TR]
[TD="class: xl67, width: 76"][TABLE="width: 309"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Book Start[/TD]
[TD]Book End[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]22-07-17[/TD]
[TD]7:00[/TD]
[TD]15:40[/TD]
[TD]8.67[/TD]
[/TR]
[TR]
[TD]19-07-17[/TD]
[TD]18:30[/TD]
[TD]6:30[/TD]
[TD]12.00
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl68, width: 87"][/TD]
[TD="class: xl68, width: 81"][/TD]
[TD="class: xl65, width: 65"][/TD]
[/TR]
</tbody>[/TABLE]

I break this down as (the first shift is Ok but the second has to be broken down)
[TABLE="width: 473"]
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Day[/TD]
[TD]Book Start[/TD]
[TD]Book End[/TD]
[TD]Charge Rate [/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]22-07-17[/TD]
[TD]Saturday[/TD]
[TD]7:00[/TD]
[TD]15:40[/TD]
[TD]Saturday[/TD]
[TD]8.67[/TD]
[/TR]
[TR]
[TD]19-07-17[/TD]
[TD]Wednesday[/TD]
[TD]18:30[/TD]
[TD]0:00[/TD]
[TD]Night[/TD]
[TD]5.50[/TD]
[/TR]
[TR]
[TD]20-07-17[/TD]
[TD]Thursday[/TD]
[TD]0:00[/TD]
[TD]6:30[/TD]
[TD]Night[/TD]
[TD]6.50[/TD]
[/TR]
</tbody>[/TABLE]

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,
 
Upvote 0
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

Code: 
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
 
Upvote 0
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)

[TABLE="width: 430"]
<colgroup><col span="2"><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Day[/TD]
[TD]Book Start [/TD]
[TD]Book End[/TD]
[TD]Charge Rate[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD="align: right"]22-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]16:00[/TD]
[TD] Day [/TD]
[TD="align: right"]8.5[/TD]
[/TR]
[TR]
[TD="align: right"]25-05-17[/TD]
[TD]Thursday[/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]17:30[/TD]
[TD] Day [/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]26-05-17[/TD]
[TD]Friday[/TD]
[TD="align: right"]7:30[/TD]
[TD="align: right"]17:30[/TD]
[TD] Day [/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]27-05-17[/TD]
[TD]Saturday[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]14:00[/TD]
[TD] Saturday [/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]22-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]19:30[/TD]
[TD="align: right"]20:30[/TD]
[TD] Night [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]22-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]5:30[/TD]
[TD="align: right"]6:30[/TD]
[TD] Night [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]23-05-17[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]5:30[/TD]
[TD="align: right"]6:30[/TD]
[TD] Night [/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]25-05-17[/TD]
[TD]Thursday[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]16:00[/TD]
[TD] Day [/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]26-05-17[/TD]
[TD]Friday[/TD]
[TD="align: right"]6:30[/TD]
[TD="align: right"]19:00[/TD]
[TD] Day [/TD]
[TD="align: right"]12.5[/TD]
[/TR]
[TR]
[TD="align: right"]22-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]0:00[/TD]
[TD] Night [/TD]
[TD="align: right"]553[/TD]
[/TR]
[TR]
[TD="align: right"]23-05-17[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]7:00[/TD]
[TD] Day [/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]26-05-17[/TD]
[TD]Friday[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]0:00[/TD]
[TD] Night [/TD]
[TD="align: right"]557[/TD]
[/TR]
[TR]
[TD="align: right"]27-05-17[/TD]
[TD]Saturday[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]6:30[/TD]
[TD] Saturday [/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD="align: right"]27-05-17[/TD]
[TD]Saturday[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]0:00[/TD]
[TD] Saturday [/TD]
[TD="align: right"]561[/TD]
[/TR]
[TR]
[TD="align: right"]28-05-17[/TD]
[TD]Sunday[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]3:00[/TD]
[TD] Saturday [/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]28-05-17[/TD]
[TD]Sunday[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]0:00[/TD]
[TD] Saturday [/TD]
[TD="align: right"]557.5[/TD]
[/TR]
[TR]
[TD="align: right"]29-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]6:30[/TD]
[TD] Night [/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD="align: right"]22-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]0:00[/TD]
[TD] Night [/TD]
[TD="align: right"]557.5[/TD]
[/TR]
[TR]
[TD="align: right"]23-05-17[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]6:30[/TD]
[TD] Night [/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD="align: right"]23-05-17[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]0:00[/TD]
[TD] Night [/TD]
[TD="align: right"]561[/TD]
[/TR]
[TR]
[TD="align: right"]25-05-17[/TD]
[TD]Thursday[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]0:00[/TD]
[TD] Night [/TD]
[TD="align: right"]561[/TD]
[/TR]
[TR]
[TD="align: right"]27-05-17[/TD]
[TD]Saturday[/TD]
[TD="align: right"]6:30[/TD]
[TD="align: right"]18:30[/TD]
[TD] Saturday [/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]22-05-17[/TD]
[TD]Monday[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]15:00[/TD]
[TD] Day [/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]23-05-17[/TD]
[TD]Tuesday[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]6:30[/TD]
[TD] Night [/TD]
[TD="align: right"]564[/TD]
[/TR]
[TR]
[TD="align: right"]26-05-17[/TD]
[TD]Friday[/TD]
[TD="align: right"]3:00[/TD]
[TD="align: right"]15:00[/TD]
[TD] Day [/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]28-05-17[/TD]
[TD]Sunday[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]3:00[/TD]
[TD] Saturday [/TD]
[TD="align: right"]564[/TD]
[/TR]
</tbody>[/TABLE]

Thanks again for your time and have a great time.

Regards,
 
Upvote 0
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)
Click to expand...
 
Upvote 0
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
 
Upvote 0
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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Excel functions to determine hours worked on day or night shift
Replies
7
Views
524
Kaper
Kaper
Ramballah
  • Question Question
Need a formula that counts the shifts and gives me a total
Replies
2
Views
739
bobaol
B
C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
420
ChetanPuri
C
alm395
  • Question Question
Coverage By Hour
Replies
6
Views
831
Fluff
Fluff
S
  • Question Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
738
Sunshine8790
S

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top