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]

Hi, can i ask re the row with "14 March 2022 - 11:44" to "15 March 2022 00:11" - I make the difference/duration is "12:27" rather than the "13:15" shown in the screen shot - or I have I worked that out wrong?

If you could upload some more examples (the database, or a subset of it) that might also help.

Thanks

 

[Cookey5599]

Hi Ed, I seem to have lost the ability to upload the actual database.
Yes your are right it is 12:27 so there must be something wrong in the formula.

 

[edmitchell]

Hi, thanks for confirming. Will work up some sample data, various formulae's (to see which suits/works) and let you know (unless of course some one beats me to it!).

If some VBA seems to be easier to get to the answer, would that still "work for you" (though will look for a formula first)?

 

[edmitchell]

Okay, my next question, if you have these details for a task:

In Date = 22/03/22 Time=08:00
Out Date = 28/03/22 Time=09:00

This would work out as a duration of 61.0 hours, so how should this appear in the spreadsheet (e.g. in the 'time diff' column), just as 61:00? And if there were minutes as well, would it then be 61:15 minutes (or as a decimal, e.g 61.25)?

Thanks

 

[Cookey5599]

Morning Ed,

Firstly yes VBA would be fine, I am ok with some VBA code but have no idea where to start with this.

Second it would display the time diff in hours (cell formatting currently custom hh:mm)

Thanks

 

[edmitchell]

Hi,

thanks for confirming.

I'll work this up in VBA, and let you know, (may be a few days as I'm have a lot of work on), but will be in touch


Ed

 

[Cookey5599]

Morning Ed

I really appreciate your efforts on this, it has totally bamboozled me

Cheers

 

[edmitchell]

No worries, please see the following VBA.

Not the most 'stylist' of code, as I've had about 20 goes through it! And is 'attuned' to how the formulae I wrote originally worked, so will be interesting to align this with your 'real world' data rather than my test/sample start/end dates & times!

I suggest you add the VBA function to your Excel file (or a test copy), and then add the formula that calls the function into a new column ('V'?), and then compare the VBA results with that from your original Formula, and the filter the results to show any rows that have different results, and then (if you are happy to) - re post those start / end dates / times that look 'incorrect' and I'll work through to see what needs to be changed (assuming that it isn't of course a perfect match!)

in order to call the VBA, in the first cell in Column V (row 10?), you would have "=Time_Taken(B10,C10,H10,I10)" (without the quotes ) -
the parameters being start date, start time, end date, end time (and then obviously repeat this through the relevant rows)

for example:-

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

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 beyond 1 day
Raw_Duration = End_Date_and_time * 1 - Start_Date_and_time * 1

Full_Days = 0
If (Raw_Duration > 1 Or (End_Date > Start_Date And End_Time > 7 / 24)) Then
    If (End_Date - Start_Date >= 2) Then
        'Work days after 1st part date, to penultimate full day
        Full_Days = Application.WorksheetFunction.NetworkDays(Start_Date, End_Date - 1) - 1    '01/04/22
    Else
        If (End_Time > Start_Time) Then
            Full_Days = Application.WorksheetFunction.NetworkDays(Start_Date, End_Date) - 1
        Else
            Full_Days = -1
        End If
    End If
End If


'how many of the Full days are Friday's?
If (Full_Days = 0 And Raw_Duration < 1) Then
    Noof_Fridays = 0
Else

'Exclude the last Friday, as if the last day is a friday, the actual time will close out on the Friday!
    Noof_Fridays = End_Date - Start_Date - 1 - (Application.WorksheetFunction.NetworkDays_Intl(Start_Date, End_Date - 1, 16) - 1)
End If

'Work out the hours to the end of the 1st day shift, hrs in last shift, and Hours in same shift
If (Full_Days = 0 And Raw_Duration < 1) Then
    Hrs_to_end_of_1st_Day = 0
    hrs_in_last_shift = 0
    Hrs_in_this_shift = Raw_Duration * 24                                             'Time in this shift
        Just_Hours_in_Shift = Int(Hrs_in_this_shift)
        Just_Minutes_in_this_shift = Int((Hrs_in_this_shift - Just_Hours_in_Shift) * 60)
    Time_Taken = Just_Hours_in_Shift & ":" & Format(Just_Minutes_in_this_shift, "00")
    Exit Function
Else
    Hrs_to_end_of_1st_Day = 1.04166666666667 - TimeValue(Start_Time) * 1  'Start_time to the following 01:00
    
    hrs_in_last_shift = End_Time * 1 - 7 / 24                           'time to End Time from 07:00
         
    'Now work out total, allowing for Hrs to end of 1st day, Hrs in last shift, plus full days, less any time iro of Friday(s)
    Total_Time = 24 * (Hrs_to_end_of_1st_Day + hrs_in_last_shift - Noof_Fridays * 0.5) + IIf(Full_Days > 0, Full_Days * 18, 0)
        Total_Just_Hours = Int(Total_Time)
        Total_just_Minutes = Int((Total_Time - Total_Just_Hours) * 60)
        Time_Taken = Total_Just_Hours & ":" & Format(Total_just_Minutes, "00")

End If

End Function

Hope that makes sense

 

[Cookey5599]

Morning Ed,

Firstly thanks for the code, looks like a ton of work has gone in to it.

Ok so I have loaded in to to the test database and seems to be an issue for entries where its gone in to a new day (V11-18)
Works correctly for entries that have been completed on the same day.

Thanks

Chris