Breaking Up A Range Of Time

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,853
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am struggling to find an efficient means of taking an employee's shift and breaking it down into different time components.

Consider this shift: 2:00PM Saturday - 2:00AM Sunday.
The rules for overtime are:
Hours between 7:00AM and 3:00PM Saturdays are at time an one half (x1.5).
Hours between 3:00PM Saturday and 0:00AM Monday are at double time (x2)

I am trying to work out code that will helf me find the appropriate values for x1.5 (OT1) and x2 (OT2) allocations respectively. I run into all kinds of crazy results when I work with time especially when it comes with crossing into the next dat

VBA Code:
'so far .....
sostm = .cells(tgt_r,9) 'start of shift time
eostm = .Cells(tgt_r, 10) 'end of shift time
sosdt = DateValue(.Cells(2, 5) & " " & .Cells(2, 6) & ", " & .Cells(2, 4)) 'start of shift date
sos = sosdt + sostm 'start date-time 
eos = '?????????? 'end date-time
elghrs = .Cells(tgt_r, 11) 'hours of shift
othrs = elghrs
ot1 = '??????? 'hours between 7am and 3pm Saturday
ot2 = '??????? 'hours between 3pm Saturday and 0:00A Monday (all day Sunday)
MsgBox "Start of Shift: " & Format(sos, "ddd dd-mm hh:mm A/P" & Chr(13) & _
       "End of Shift: " & Format(eos, "ddd dd-mm hh:mm A/P" & Chr(13) & _
       "Eligible for: " & othrs & " hrs. overtime.")

Example:
sostm = 2:00:00 PM
eostm = 2:00:00 AM
sosdt = 43974
sos = 43974.583333
eos = ?
elghrs = 12
othrs = 12

The expected results:
ot1 = 1 (2:00P - 3:00P Saturday)
ot2 = 11 (3:00P Sat - 2:00A Sunday)

Thank you.


0.583333​
0.083333​
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
147
Hi sbTimeDiff. Thank you for sharing that! Again, a very thorough solution, but perhaps a bit too complicated for me to deal with. Despite that though, it has provied some additional education I didn't have 30 minutes ago, so it's appreciated.

Looking for possibles solutions to my issues encountered in post #4, I've raised that unique problem with a cross posted question here.

Kirk's sample calculation is totally ok, I think.
In my humble opinion the UDF sbTimeDiff is simplifying things by encapsulating the desired functionality:

Breaking_up_a_range_of_timelsm.xlsm
ABCDEFGHIJKLMNO
1WeekdayStartEndSaturday1StartEndSaturday2StartEndSaturday3StartEndSundayStartEnd
2Monday0:0024:00MondayMondayMondayMonday
3Tuesday0:0024:00TuesdayTuesdayTuesdayTuesday
4Wednesday0:0024:00WednesdayWednesdayWednesdayWednesday
5Thursday0:0024:00ThursdayThursdayThursdayThursday
6Friday0:0024:00FridayFridayFridayFriday
7SaturdaySaturday0:007:00Saturday7:0015:00Saturday15:0024:00Saturday
8SundaySundaySundaySundaySunday0:0024:00
9HolidaysHolidaysHolidaysHolidaysHolidays
10
11Rate factor111,522
12
13ScenarioInOutHrs between LimitsHrs between LimitsHrs between LimitsHrs between LimitsHrs between LimitsRegular Pay Rate ($/h)Pay with overtime ($)
14121.09.2019 06:0022.09.2019 01:000:001:008:009:001:0020660,00
15207.09.2019 17:5408.09.2019 03:320:000:000:006:063:3220385,33
16322.05.2020 22:0023.05.2020 14:002:007:007:000:000:0020390,00
17423.05.2020 21:3024.05.2020 16:150:000:000:002:3016:1520750,00
18524.05.2020 18:0025.05.2020 08:308:300:000:000:006:0020410,00
19624.05.2020 18:0026.05.2020 08:3032:300:000:000:006:0020890,00
Sheet1
Cell Formulas
RangeFormula
E14:E19E14=sbTimeDiff($B14,$C14,Weekday)
F14:F19F14=sbTimeDiff($B14,$C14,Saturday1)
G14:G19G14=sbTimeDiff($B14,$C14,Saturday2)
H14:H19H14=sbTimeDiff($B14,$C14,Saturday3)
I14:I19I14=sbTimeDiff($B14,$C14,Sunday)
K14:K19K14=J14*SUMPRODUCT(E14:I14,$E$11:$I$11)*24
Named Ranges
NameRefers ToCells
Saturday1=Sheet1!$E$2:$F$9F14:F19
Saturday2=Sheet1!$H$2:$I$9G14:G19
Saturday3=Sheet1!$K$2:$L$9H14:H19
Sunday=Sheet1!$N$2:$O$9I14:I19
Weekday=Sheet1!$B$2:$C$9E14:E19


Maybe you find out when you need to take holidays into account ;)
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

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
Top