Calculate length of job excluding multiple breaks
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
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
This excludes weekend working format as general
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
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)
FullDays = WorksheetFunction.NetworkDays(start, finish) - 2
'Calculate breaks for full days
If FullDays > 0 Then Breaks = FullDays * 2
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
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
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
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
AllBreaks = firstdaybreak + LastDayBreak + Breaks
'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
FullTime = (CDbl(TimeValue(Format(finish, "HH:MM"))) - CDbl(TimeValue(Format(start, "HH:MM")))) * 24
'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
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.
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.
Related: How to Download Toshiba Portege R500 HDD/SSD Alert Utility v.126.96.36.199 driver,Way to Update & Download Toshiba Satellite A355-S69403 Motorola Modem Region Select Utility v.188.8.131.52 driver,Where to Download Toshiba Satellite L305-S5926 HW Setup Utility v.2.00.11 driver,[Solved] Download Toshiba Satellite L675-S7115 Laptop Checkup v.184.108.40.206 driver,Method to Download Toshiba Satellite M645-S4080 Media Controller v.220.127.116.11 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17
Read More: [Anwsered] BSODS in ntoskrnl.exe+153fa0,Troubleshoot:Browser hangs while opening Silverlight in Full Screen Error,[Solution] browser window,How Can You Fix - C:\\WINDOWS\\system32\\rundll32.exe - missing??,[Anwsered] Brand new laptop, mouse pointer is erratic and is going crazy,All diagnostic tools give 0x800700C1, troubleshooting wizard can't continue,all files try to open with adobe,all icons on desktop have changed to Adobe icon and won't open,All functions of my outlook are working except it will not let me "REPLY" or start a "NEW" email,all f1-f12 shortcuts not work