How to Fix a Kernel32.DLL Error? : Troubleshoot:Calculate length of job excluding multiple breaks

Calculate length of job excluding multiple breaks

Hi All
I need a formula to calculate the length of a job based on the following:
start date and time
end date and time
it needs to exclude 4 scheduled breaks in a day and have the option of including weekends (if they are planned to be worked).
Bank holidays/plant closed days also need to be excluded.
Is this possible...?

Anwsers to the Problem Calculate length of job excluding multiple breaks

Download SmartPCFixer to Fix It (Free)

Hi,
Apologies for the delay, I posted one response then quickly realised I was suffering from muddled thought and deleted it.
Try this one.
Put the code in a general module and on the worksheet call with:-
Note A1 and B1 can be any cells but they must be a properly formatted date and time.

This includes weekend working format as general
=elapsed(A1,B1,TRUE)
or
This excludes weekend working format as general
=elapsed(A1,B1,FALSE)
The elapsed time is returned as a decimal hours and minutes but if you look at the last couple of lines of code there's a commented out line that will return the time as HH:MM.
If you use this line then note the comment in the code about the formula cell
format.

Function elapsed(start As Range, finish As Range, WeEnd As Boolean) As Double
Dim FullDays As Long, Breaks As Long
Dim firstdaybreak As Double
Dim LastDayBreak As Double
Dim AllBreaks As Double
Dim FullTime As Double
Dim StartDayHours As Double
Dim EndDayHours As Double
Dim Break1 As Double
Dim Break2 As Double
Dim Break3 As Double
Dim Break4 As Double
Dim StartTime As Double
Dim EndTime As Double
'Calculate full days for a job
If WeEnd Then
    FullDays = WorksheetFunction.Max(0, Int(finish) - Int(start) - 1)
Else
    FullDays = WorksheetFunction.NetworkDays(start, finish) - 2
End If
'Calculate breaks for full days
If FullDays > 0 Then Breaks = FullDays * 2
'Breaks
    Break1 = CDbl(TimeValue("08:30")) '15 minutes
    Break2 = CDbl(TimeValue("12:00")) '45 Minutes
    Break3 = CDbl(TimeValue("15:30")) '15 Minutes
    Break4 = CDbl(TimeValue("19:00")) '45 Minutes

'Srart and end times
    StartTime = CDbl(TimeValue(Format(start, "HH:MM")))
    EndTime = CDbl(TimeValue(Format(finish, "HH:MM")))
'Calculate first day breaks
'08:30,12:00,15:30,19:00
    If StartTime <= Break1 Then firstdaybreak = 0.25
    If StartTime <= Break2 Then firstdaybreak = firstdaybreak + 0.75
    If StartTime <= Break3 Then firstdaybreak = firstdaybreak + 0.25
    If StartTime <= Break4 Then firstdaybreak = firstdaybreak + 0.75

'Calculate last day breaks
'08:30,12:00,15:30,19:00
'***
    If EndTime >= Break1 Then LastDayBreak = 0.25
    If EndTime >= Break2 Then LastDayBreak = LastDayBreak + 0.75
    If EndTime >= Break3 Then LastDayBreak = LastDayBreak + 0.25
    If EndTime >= Break4 Then LastDayBreak = LastDayBreak + 0.75

If Format(start, "DDMMYYYY") = Format(finish, "DDMMYYYY") Then
'calculate breaks if job starts and finishes on same day
'08:30,12:00,15:30,19:00
If StartTime <= Break1 And EndTime >= Break1 Then AllBreaks = 0.25
If StartTime <= Break2 And EndTime >= Break2 Then AllBreaks = AllBreaks + 0.75
If StartTime <= Break3 And EndTime >= Break3 Then AllBreaks = AllBreaks + 0.25
If StartTime <= Break4 And EndTime >= Break4 Then AllBreaks = AllBreaks + 0.75

Else
    AllBreaks = firstdaybreak + LastDayBreak + Breaks
End If

'calculate full hours
If Format(start, "DDMMYYYY") <> Format(finish, "DDMMYYYY") Then
StartDayHours = (TimeValue("22:00") - TimeValue(Format(start, "HH:MM"))) * 24
EndDayHours = (TimeValue(Format(finish, "HH:MM")) - TimeValue("06:00")) * 24
FullTime = (FullDays * 16) + StartDayHours + EndDayHours

Else
FullTime = (CDbl(TimeValue(Format(finish, "HH:MM"))) - CDbl(TimeValue(Format(start, "HH:MM")))) * 24

End If
    'This returns the elapsed time as decimal
    elapsed = FullTime - AllBreaks
    'This returns the elapsed time as time
    'If you use this cline format the cell with
    'a custom format of [HH]:MM
   'elapsed = (FullTime - AllBreaks) / 24
End Function

To install drivers and other optional updates

To install optional updates, you need to review and then select them from the list of updates that Windows finds for your computer. Optional updates are not installed automatically.
Open Windows Update by clicking the Start button Picture of the Start button, clicking All Programs, and then clicking Windows Update.
In the left pane, click Check for updates, and then wait while Windows looks for the latest updates for your computer.
If any updates are found, click View available updates.
Select the optional updates that you want, and then click Install. Administrator permission required If you are prompted for an administrator password or confirmation, type the password or provide confirmation.

Recommended Method to Fix the Problem: Calculate length of job excluding multiple breaks:

How to Fix Calculate length of job excluding multiple breaks with SmartPCFixer?

1. Click the button to download Error Fixer . Install it on your system. Open it, and it will perform a scan for your system. The junk files will be shown in the list.