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

thanks for the screen shot, amended code (both functions) follows:

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 (e.g. strip off any date(s), and the seconds)
Start_Time = Format(TimeValue(Start_Time), "HH:MM")
End_Time = Format(TimeValue(End_Time), "HH:MM")

'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

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]

Thanks Ed

Really appreciate your help on this.

 

[Cookey5599]

Morning Ed

A couple of odd things happening on the spreadsheet, Pic1: shows a few entries that were started/completed (due to operator issues) at the same time, 2 entries for 11th may start/stop at 00:58 return a value of -6, however further down a few entries start/stop at 16th may 08:16 return 0??

Also and I may need to start a separate thread but when I calculate the overall average time using the formula in pic 2 it returns a #value error, I can see this is because the cells below 408 have yet to be populated but amending formula with ISERROR or trying Aggregate doesn't fix it.

Thanks Chris

 

[edmitchell]

Chris

good morning, I can ‘see’ where the -6 is coming from, and I will fix the function when I’m next ‘on-line’.

re the error in picture 2, are you able to upload via the addin ‘XL2BB’, some/all of the file (so that I could take a look at the dates/times, results)? Or clearly happy if you wish to re-post.


Ed

 

[Cookey5599]

Hi Ed

Try this

Electronic Product Process Verification rev excel.xlsm
							H	I	J	K	L	M
2
1ST OFF LOG

 

[edmitchell]

Chris

Thanks for the XL2BB file, but unfortunately I don't think this quiet worked (as when I paste the copied cells, they all appear show as blank), however please find below the updated code (both functions), which address the "-6" return, and should now return "0:00" if any of the start date/time or end date/time cells are blank (which should I hope 'help' with the average calculation, as this should avoid any #Value returns from the function ).

Regards


Ed

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

'if any of the cells are blank then return Zero time
If (Start_Date = "00:00:00" Or Start_Time = "00:00:00" Or End_Date = "00:00:00" Or End_Time = "00:00:00") Then
    Time_Taken = "0:00"
    Exit Function
End If

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

'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
        If (TimeValue(End_Time) < 1 / 24) Then   'however if the end time is also in this shift do not adjust
        Else
            Total_Time = Total_Time - 0.25          '.25 = 6 hours
        End If
    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

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

Thanks once again, one final question is that when I try and sum column U (to do some other analysis, find sum and divide by count) it just returns 0.
Have attempted to attached part of the database again

Electronic Product Process Verification rev2 draft code.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
232	19 April 2022	15:36	68	19 April 2022	21:11	68								5:35	00:00	00:00
233	19 April 2022	22:23	68	19 April 2022	23:42	68								1:19
234	20 April 2022	00:07	63	20 April 2022	11:22	63								5:15
1ST OFF LOG

Cell Formulas
Range	Formula
H232:H234,Q232:Q234,K232:K234	H232	=IF(J232<>"",IF(H232="",TODAY(),H232),"")
I232:I234	I232	=IF(J232<>"",IF(I232="", NOW(),I232),"")
L232:L234	L232	=IF(M232<>"", IF(L232="", NOW(),L232),"")
N232:N234	N232	=IF(P232<>"", IF(N232="",TODAY(),N232),"")
O232:O234,R232:R234	O232	=IF(P232<>"",IF(O232="",NOW(),O232),"")
V232	V232	=SUM(U232:U233)
W232	W232	=SUM(U232:U233)
U232:U234	U232	=time_taken(H232,I232,K232,L232)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J76:J302,J304:J1033	Cell Value	contains "65"	text	NO
J76:J302,J304:J1033	Cell Value	contains "1"	text	NO
J76:J302,J304:J1033	Cell Value	contains "63"	text	NO
J76:J302,J304:J1033	Cell Value	contains "24"	text	NO
J76:J302,J304:J1033	Cell Value	contains "3"	text	NO
J76:J302,J304:J1033	Cell Value	contains "T2"	text	NO
J76:J302,J304:J1033	Cell Value	contains "68"	text	NO
I10:I1033	Expression	=ISBLANK(N10)=FALSE	text	NO
O10:O1033	Expression	=ISBLANK(M10)=FALSE	text	NO
S10:S1033	Expression	=ISBLANK(M10)=FALSE	text	NO
R10:R1033	Expression	=ISBLANK(M10)=FALSE	text	NO
Q10:Q1033	Expression	=ISBLANK(M10)=FALSE	text	NO
P10:P1033	Expression	=ISBLANK(M10)=FALSE	text	NO
S10:S1033	Expression	=ISBLANK(M10)=TRUE	text	NO
P10:P1033	Expression	=ISBLANK(M10)=TRUE	text	NO
N10:N1033	Expression	=ISBLANK(M10)=FALSE	text	NO
L10:L1033	Cell	does not contain a blank value	text	NO
K10:K1033	Cell	does not contain a blank value	text	NO
S10:S1033	Cell	does not contain a blank value	text	NO
P10:P1033	Cell Value	>=1	text	NO
M10:M1033	Cell Value	contains "65"	text	NO
M10:M1033	Cell Value	contains "1"	text	NO
M10:M1033	Cell Value	contains "63"	text	NO
M10:M1033	Cell Value	contains "24"	text	NO
M10:M1033	Cell Value	contains "3"	text	NO
M10:M1033	Cell Value	contains "T2"	text	NO
M10:M1033	Cell Value	contains "68"	text	NO
G10:H1033	Expression	=ISBLANK(M10)=FALSE	text	NO

Cells with Data Validation
Cell	Allow	Criteria
Q232:R234	Text length	between 0 and 0

Electronic Product Process Verification rev2 draft code.xlsm
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
232	19 April 2022	15:36	68	19 April 2022	21:11	68								5:35	00:00	00:00
233	19 April 2022	22:23	68	19 April 2022	23:42	68								1:19
234	20 April 2022	00:07	63	20 April 2022	11:22	63								5:15
1ST OFF LOG

Cell Formulas
Range	Formula
H232:H234,Q232:Q234,K232:K234	H232	=IF(J232<>"",IF(H232="",TODAY(),H232),"")
I232:I234	I232	=IF(J232<>"",IF(I232="", NOW(),I232),"")
L232:L234	L232	=IF(M232<>"", IF(L232="", NOW(),L232),"")
N232:N234	N232	=IF(P232<>"", IF(N232="",TODAY(),N232),"")
O232:O234,R232:R234	O232	=IF(P232<>"",IF(O232="",NOW(),O232),"")
V232	V232	=SUM(U232:U233)
W232	W232	=SUM(U232:U233)
U232:U234	U232	=time_taken(H232,I232,K232,L232)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J76:J302,J304:J1033	Cell Value	contains "65"	text	NO
J76:J302,J304:J1033	Cell Value	contains "1"	text	NO
J76:J302,J304:J1033	Cell Value	contains "63"	text	NO
J76:J302,J304:J1033	Cell Value	contains "24"	text	NO
J76:J302,J304:J1033	Cell Value	contains "3"	text	NO
J76:J302,J304:J1033	Cell Value	contains "T2"	text	NO
J76:J302,J304:J1033	Cell Value	contains "68"	text	NO
I10:I1033	Expression	=ISBLANK(N10)=FALSE	text	NO
O10:O1033	Expression	=ISBLANK(M10)=FALSE	text	NO
S10:S1033	Expression	=ISBLANK(M10)=FALSE	text	NO
R10:R1033	Expression	=ISBLANK(M10)=FALSE	text	NO
Q10:Q1033	Expression	=ISBLANK(M10)=FALSE	text	NO
P10:P1033	Expression	=ISBLANK(M10)=FALSE	text	NO
S10:S1033	Expression	=ISBLANK(M10)=TRUE	text	NO
P10:P1033	Expression	=ISBLANK(M10)=TRUE	text	NO
N10:N1033	Expression	=ISBLANK(M10)=FALSE	text	NO
L10:L1033	Cell	does not contain a blank value	text	NO
K10:K1033	Cell	does not contain a blank value	text	NO
S10:S1033	Cell	does not contain a blank value	text	NO
P10:P1033	Cell Value	>=1	text	NO
M10:M1033	Cell Value	contains "65"	text	NO
M10:M1033	Cell Value	contains "1"	text	NO
M10:M1033	Cell Value	contains "63"	text	NO
M10:M1033	Cell Value	contains "24"	text	NO
M10:M1033	Cell Value	contains "3"	text	NO
M10:M1033	Cell Value	contains "T2"	text	NO
M10:M1033	Cell Value	contains "68"	text	NO
G10:H1033	Expression	=ISBLANK(M10)=FALSE	text	NO

Cells with Data Validation
Cell	Allow	Criteria
Q232:R234	Text length	between 0 and 0

 

[edmitchell]

Chris

hi - I think the problem has a root cause in that the return from the function is text (e.g. "9:28", etc), so that it can show long durations such as "36:28" (e.g. 36 hours and 28 minutes), which is obviously not a valid time (as its greater than 24 hours).

In order to create an usable average, my thought would be to convert the hh:mm, into a number (so 13:15 (hh:mm), would be come 13.25), if this works for you you could add a formula into a new cell/column of

=LEFT(AK9,FIND(":",AK9)-1)+RIGHT(AK9,2)/60 (with AK9 being the cell with the result of our VBA function 'time_taken', e.g. U232 to U234 in your example above)

then using the value in this new cell, the average function, etc should then work as intended (but will obviously show the average duration(s) in decimal/numeric format, rather than HH:MM).


Ed

 

[Cookey5599]

Perfect thank you Ed