Calculating Difference between two time entries where Fridays have less working hours than Mon-Thurs

[Cookey5599]

Ok firstly sorry about the title and if this has been asked before, but I am really struggling to define the problem.

So we have a spreadsheet that measure the product 1st off in and out time, we need to calculate the average amount of time that these tasks take for a company KPI.
The problem is that our working week is Monday-Thursday 7am to 1am (18 hour shift) and Friday is 7am to 1pm (6 hour shift).

I have the formula that calculates the difference (T10) and takes out weekends, the average is then displayed in (R10, some cells hidden), however as you can see from the screenshot jobs that tip over from Friday to Monday (Row55) are +12 hours because it still counts Friday with the same working hours.

The question is can I somehow have Friday with different working hours?

I can upload the database if that helps.

Many Thanks

 

[edmitchell]

Chris

hi.

After I made the original post, with its VBA, etc - I had a 'light bulb moment' and thought of a different (and I think) easier way to do the various calculations, which has resulted in the VBA below. Can I suggest that you overwrite the VBA from my original post, with this version (still uses the same Function "time_taken" so the cells themselves do not need to change (the VBA also has a new function "Time_of_shift_End" which is only called by the Time_Taken function (so does not have an entry in any cell)).

I've also added to my file, the data you showed in the screen shot, and the results are also shown (and according to my maths are correct ), except for the ID = 11 / Row 14 in my file, where I'm not sure about the Out/End time which you have as "01:19" - I thought the shift's end at "01:00", (the time being beyond the shift end, is then impacting the calculation which is why the results of '06:34' isn't as you would 'expect' - please can you confirm?

Function Time_Taken(Start_Date As Date, Start_Time As Date, End_Date As Date, End_Time As Date) As String

'Check to see if we are in the same day, and then do simple duration calc
End_Date_and_time = DateValue(End_Date) + TimeValue(End_Time)
Start_Date_and_time = DateValue(Start_Date) + TimeValue(Start_Time)

'Check to see if we are within the same day
If (Start_Date = End_Date) Then
    Total_Time = End_Date_and_time * 1 - Start_Date_and_time * 1 'simple duration
  

'Now check to see if we are within day #1 shift e.g. Start Date = End Date less 1 day, and End time is earlier than 01:00
ElseIf (Start_Date = End_Date - 1 And End_Time * 1 <= 1 / 24) Then
    Total_Time = End_Date_and_time * 1 - Start_Date_and_time * 1 'simple duration



Else    'Now need complex calculation e.g. shift#1 time     + number of days x 18 (ex Fridays x 6)   + Last shift time calculation

'Work out, day of week for Start_Date, and then assign time value based for end of shift based on whether its Monday to Thursday or a Friday
End_of_Shift = Time_of_shift_End(Start_Date)

'(1) work out hours to end of 1st shift
If (Start_Time * 1 < 1 / 24) Then ' so start time is after midnight, so remove 1 day !
    Hrs_to_end_of_1st_Day = End_of_Shift - 1 - TimeValue(Start_Time) * 1
    Start_Date = Start_Date - 1         'and move start date back the day
Else
    Hrs_to_end_of_1st_Day = End_of_Shift - TimeValue(Start_Time) * 1  'Start_time to the following 01:00 (or 13:00 on a Friday)
End If

'(2) hours from 07:00 to end of shift (e.g. 25:00 or 13:00)
Hrs_in_last_shift = End_Time * 1 - 7 / 24                           'time to End Time from 07:00

'(3) Number of full days...and number of hours in each full day
'Now work out number(s) of Monday to Thursday, * 18 hours  plus Fridays * 6 hours, from Start_Date +1 to End_Date -1
Total_Time = 0
For iday = Start_Date + 1 To End_Date - 1
    If (Weekday(iday) = 6) Then
        Total_Time = Total_Time + 6 / 24
    ElseIf (Weekday(iday) >= 2 And Weekday(iday) <= 5) Then
        Total_Time = Total_Time + 18 / 24
    End If
Next iday
'Add in hrs in Shift#1, and Hrs for last Shift
Total_Time = Total_Time + Hrs_to_end_of_1st_Day + Hrs_in_last_shift

End If

Total_Time = Total_Time * 24        'Convert back from decimal of day (e.g. 24 hours)
Total_Just_Hours = Int(Total_Time)
Total_just_Minutes = Round((Total_Time - Total_Just_Hours) * 60, 1)
Time_Taken = Total_Just_Hours & ":" & Format(Total_just_Minutes, "00")

End Function
Function Time_of_shift_End(Dte As Date)

I = Weekday(Dte)
If (I = 1 Or I = 7) Then        'Saturday/Sunday - shouldn't happen!
    Time_of_shift_End = 0
ElseIf (I = 6) Then             'Friday, 13:00
    Time_of_shift_End = 13 / 24
Else                            'Other Weekday, 25:00
    Time_of_shift_End = 25 / 24
End If

End Function

 

[Cookey5599]

Hi Ed

Ok so there must be something else going on in my database, when I put the same raw figures in to a new spreadsheet as per your table above it works but when I put the code in to the database it still gives me this random large number for dates that have gone in to another day.

I have attached a couple of screenshots showing formulas that are in cells B1, C1, H1 (date trial1) & I1 (date trial2), these auto populate when information is added in to cells D1 and J1, I cant see how they would interfere with the calculation steps?

The reason these cells auto populate is to save the setters time when entering data.

Cheers Chris

 

[edmitchell]

Chris

Hi - thanks for the screen shots. I think (based on a few tests) that the 'problem' lies with the time values in Columns C & I (in that actually in the cell is that date and the time, but the formatting of the cells only shows the time). So based on this I've changed the VBA slightly - to strip out any dates in the 2 time cells, and therefore please find the updated VBA below.

If this doesn't deliver the fix, perhaps (assuming you are okay to), you could send me a selection/sample for the Excel file via the XL2BB Excel add-in mentioned above/via the "Upload Mini Sheet" below (the add-in would need to be downloaded and installed onto your PC via the instructions detailed), but hopefully this will not be necessary.

Function Time_Taken(Start_Date As Date, Start_Time As Date, End_Date As Date, End_Time As Date) As String

'Make sure that the time variables just hold HH:MM:SS (e.g. strip off any date(s))
Start_Time = TimeValue(Start_Time)
End_Time = TimeValue(End_Time)

'Check to see if we are in the same day, and then do simple duration calc
End_Date_and_time = DateValue(End_Date) + TimeValue(End_Time)
Start_Date_and_time = DateValue(Start_Date) + TimeValue(Start_Time)

'Check to see if we are within the same day
If (Start_Date = End_Date) Then
    Total_Time = End_Date_and_time * 1 - Start_Date_and_time * 1 'simple duration
  

'Now check to see if we are within day #1 shift e.g. Start Date = End Date less 1 day, and End time is earlier than 01:00
ElseIf (Start_Date = End_Date - 1 And End_Time * 1 <= 1 / 24) Then
    Total_Time = End_Date_and_time * 1 - Start_Date_and_time * 1 'simple duration



Else    'Now need complex calculation e.g. shift#1 time     + number of days x 18 (ex Fridays x 6)   + Last shift time calculation

'Work out, day of week for Start_Date, and then assign time value based for end of shift based on whether its Monday to Thursday or a Friday
End_of_Shift = Time_of_shift_End(Start_Date)

'(1) work out hours to end of 1st shift
If (Start_Time * 1 < 1 / 24) Then ' so start time is after midnight, so remove 1 day !
    Hrs_to_end_of_1st_Day = End_of_Shift - 1 - TimeValue(Start_Time) * 1
    Start_Date = Start_Date - 1         'and move start date back the day
Else
    Hrs_to_end_of_1st_Day = End_of_Shift - TimeValue(Start_Time) * 1  'Start_time to the following 01:00 (or 13:00 on a Friday)
End If

'(2) hours from 07:00 to end of shift (e.g. 25:00 or 13:00)
Hrs_in_last_shift = End_Time * 1 - 7 / 24                           'time to End Time from 07:00

'(3) Number of full days...and number of hours in each full day
'Now work out number(s) of Monday to Thursday, * 18 hours  plus Fridays * 6 hours, from Start_Date +1 to End_Date -1
Total_Time = 0
For iday = Start_Date + 1 To End_Date - 1
    If (Weekday(iday) = 6) Then
        Total_Time = Total_Time + 6 / 24
    ElseIf (Weekday(iday) >= 2 And Weekday(iday) <= 5) Then
        Total_Time = Total_Time + 18 / 24
    End If
Next iday
'Add in hrs in Shift#1, and Hrs for last Shift
Total_Time = Total_Time + Hrs_to_end_of_1st_Day + Hrs_in_last_shift

End If

Total_Time = Total_Time * 24        'Convert back from decimal of day (e.g. 24 hours)
Total_Just_Hours = Int(Total_Time)
Total_just_Minutes = Round((Total_Time - Total_Just_Hours) * 60, 1)
Time_Taken = Total_Just_Hours & ":" & Format(Total_just_Minutes, "00")

End Function
Function Time_of_shift_End(Dte As Date)

I = Weekday(Dte)
If (I = 1 Or I = 7) Then        'Saturday/Sunday - shouldn't happen!
    Time_of_shift_End = 0
ElseIf (I = 6) Then             'Friday, 13:00
    Time_of_shift_End = 13 / 24
Else                            'Other Weekday, 25:00
    Time_of_shift_End = 25 / 24
End If

End Function

 

[Cookey5599]

Hi Ed

I think you have cracked it, It works on the test database but I cant get access to the live database for day or so as soon as I have installed the code and tested it I will let you know.

Thanks again

Chris

 

[Cookey5599]

Hi Ed

So we have using it for a while and noticed an issue where the time starts after midnight and ends the same day (see attachment line 225, highlighted in yellow)

We have changed the time taken cell reference from B,C,K,L to H,I,K,L as this is the actual time from when the operator starts the inspection.

Row 229 works fine (green) as I assume its because this tips over in to the next day.

Thanks

Chris

 

[Sulprobil]

I suggest to have a look at this thread:

Shifts and bonuses

Hi, I have a problem I have been trying to find a solution to for a while now. We have people that work shifts. Bonuses can be received for each shift depending on time and day. ---**disclaimer** this is not the actual bonus system, since we don't even use dollars in my country but it's just...

www.mrexcel.com

Maybe you are not just facing less work hours on Fridays and none at all on weekends but also the occasional Bank Holiday.

 

[edmitchell]

Chris

hi - I've amended the function Time_Taken to 'fix' the calculation in row 225, please see below.

I also wondered about the result for row 229 (e.g. 12:60), which should show as 13:00 (as it does in my file), so can I ask what version of Excel you have (as the rounding isn't working as it should)?


Ed

Function Time_Taken(Start_Date As Date, Start_Time As Date, End_Date As Date, End_Time As Date) As String

'Make sure that the time variables just hold HH:MM:SS (e.g. strip off any date(s))
Start_Time = TimeValue(Start_Time)
End_Time = TimeValue(End_Time)

'Check to see if we are in the same day, and then do simple duration calc
End_Date_and_time = DateValue(End_Date) + TimeValue(End_Time)
Start_Date_and_time = DateValue(Start_Date) + TimeValue(Start_Time)

'Check to see if we are within the same day
If (Start_Date = End_Date) Then
    Total_Time = End_Date_and_time * 1 - Start_Date_and_time * 1 'simple duration
    'However check to see if start time is < 01:00, so at end of shift, and then subtract the 6 hrs, that are between shifts.
    If (TimeValue(Start_Time) < 1 / 24) Then
        Total_Time = Total_Time - 0.25          '.25 = 6 hours
    End If
    
'Now check to see if we are within day #1 shift e.g. Start Date = End Date less 1 day, and End time is earlier than 01:00
ElseIf (Start_Date = End_Date - 1 And End_Time * 1 <= 1 / 24) Then
    Total_Time = End_Date_and_time * 1 - Start_Date_and_time * 1 'simple duration



Else    'Now need complex calculation e.g. shift#1 time     + number of days x 18 (ex Fridays x 6)   + Last shift time calculation

'Work out, day of week for Start_Date, and then assign time value based for end of shift based on whether its Monday to Thursday or a Friday
End_of_Shift = Time_of_shift_End(Start_Date)

'(1) work out hours to end of 1st shift
If (Start_Time * 1 < 1 / 24) Then ' so start time is after midnight, so remove 1 day !
    Hrs_to_end_of_1st_Day = End_of_Shift - 1 - TimeValue(Start_Time) * 1
    Start_Date = Start_Date - 1         'and move start date back the day
Else
    Hrs_to_end_of_1st_Day = End_of_Shift - TimeValue(Start_Time) * 1  'Start_time to the following 01:00 (or 13:00 on a Friday)
End If

'(2) hours from 07:00 to end of shift (e.g. 25:00 or 13:00)
Hrs_in_last_shift = End_Time * 1 - 7 / 24                           'time to End Time from 07:00

'(3) Number of full days...and number of hours in each full day
'Now work out number(s) of Monday to Thursday, * 18 hours  plus Fridays * 6 hours, from Start_Date +1 to End_Date -1
Total_Time = 0
For iday = Start_Date + 1 To End_Date - 1
    If (Weekday(iday) = 6) Then
        Total_Time = Total_Time + 6 / 24
    ElseIf (Weekday(iday) >= 2 And Weekday(iday) <= 5) Then
        Total_Time = Total_Time + 18 / 24
    End If
Next iday
'Add in hrs in Shift#1, and Hrs for last Shift
Total_Time = Total_Time + Hrs_to_end_of_1st_Day + Hrs_in_last_shift

End If

Total_Time = Total_Time * 24        'Convert back from decimal of day (e.g. 24 hours)
Total_Just_Hours = Int(Total_Time)
Total_just_Minutes = Round((Total_Time - Total_Just_Hours) * 60, 1)
Time_Taken = Total_Just_Hours & ":" & Format(Total_just_Minutes, "00")

End Function

 

[Cookey5599]

Hi Ed

When I install the updated code I get a compile error (as attached) and the program hangs

Yes I did think the 12:60 was odd, I am running 365 version 2203

Thanks Chris

 

[edmitchell]

Chris

sorry - there are 2 functions in the VBA, (Time_Taken (the main one), and Time_of_Shift_end), last night I only re-posted the Time_Taken one (as the smaller one was unchanged), so 'all' you would need to do is to bring the small one back from an earlier post/Excel copy. But in order to help, I've re-posted it below

Function Time_of_shift_End(Dte As Date)

I = Weekday(Dte)
If (I = 1 Or I = 7) Then        'Saturday/Sunday - shouldn't happen!
    Time_of_shift_End = 0
ElseIf (I = 6) Then             'Friday, 13:00
    Time_of_shift_End = 13 / 24
Else                            'Other Weekday, 25:00
    Time_of_shift_End = 25 / 24
End If

End Function

re the rounding, I am also running 365/2203, so therefore I would suspect the 'error' in the calculated duration, is being driven by the entries in cells I229 and /or L229 - could you expand (change the format?) to see what "hh:mm:ss" are in the cells (I wonder if its the seconds, that are impacting the calculation, and we can't see them due to the format in the cell)?


Ed

 

[Cookey5599]

Hi Ed

Yes the code is working again now, missed the bottom bit off.

Ref the rounding I have expanded those two columns to show HH:MM:SS (attached)

Thanks again