## 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

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

1. 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.
2. Open Windows Update by clicking the Start button Picture of the Start button, clicking All Programs, and then clicking Windows Update.
3. In the left pane, click Check for updates, and then wait while Windows looks for the latest updates for your computer.
4. If any updates are found, click View available updates.
5. 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. 2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All. 3. The Fixing part is done, the speed of your computer will be much higher than before and the errors have been removed. 