Calculating end time considering non-business hours

Hemanth Kumar S

New Member
Joined
Aug 29, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Dear All,

Glad to be part of Mr.Excel.com,

I want to determine the task's end time excluding non-working hours and downtime (Tea, Lunch breaks), Please provide me advice on how to find the finish time for a time target sheet that basically belongs to my team and where the start date and time as well as the expected time in minutes are known.

If the start time is 5.40 PM on August 30, 2022, and the targeted time is 100 minutes, the end time should be 10 AM on August 31, 2022, the following day. (Taking into account the working hours of 9 to 6.30).

Similar to this, the predicted completion time is 300 minutes, and the end time should be the following day, August 31, 2022, at 2.05 PM (Considering the tea break is 11 AM to 11.15 AM & Lunch break 1.30 PM to 2 PM, Tea break 5 - 5.15 PM).

Dinesh Target Time Sheet.xlsx
ABCDEFGHI
1Target Time SheetWorking Hours - 8 hours (9 AM to 6.30 PM), 11 AM - 11.15 AM Tea Break, 1.30 PM - 2 PM Lunch Break, 5 PM - 5.15 PM Tea Break
2Sl. NoStart Date and time (Task Allotted Time)TaskTask Cycle Time Planned Downtime (Tea & Lunch Break)End Date and time (Task Allotted Time)Actual Task CompletionTime DifferenceResult
3129/08/2022 09:00Task A20029/08/2022 09:2029/08/2022 11:5502:35:00Crossed
4229/08/2022 09:45Task B40029/08/2022 10:2529/08/2022 11:1400:49:00Crossed
5329/08/2022 10:35Task C501529/08/2022 11:4030/08/2022 16:3004:50:00Crossed
6429/08/2022 11:50Task D6004529/08/2022 22:3531/08/2022 16:3017:55:00Crossed
7529/08/2022 22:45Task E401029/08/2022 23:3501/09/2022 16:3016:55:00Crossed
8629/08/2022 23:45Task F150030/08/2022 02:1502/09/2022 11:3009:15:00Crossed
9730/08/2022 02:25Task G540030/08/2022 11:2503/09/2022 16:3005:05:00Crossed
10830/08/2022 11:35Task H400030/08/2022 18:1504/09/2022 16:3022:15:00Crossed
Value Added
Cell Formulas
RangeFormula
H3:H10H3=TEXT(G3-F3, "HH:MM:SS")
I3:I10I3=IF(HOUR(H3)>0,"Crossed",IF(MINUTE(H3)>5,"Crossed",IF(AND(MINUTE(H3)=5,SECOND(H3)>0),"Crossed","Within Time")))
E6E6=30+15
B4B4=F3+TIME(0, 10+15, 0)
B5:B10B5=F4+TIME(0, 10, 0)
F3:F10F3=B3+TIME(0, D3+E3, 0)
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,233
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You sayd nothing about working days, but I guess that weekend (and maybe other holidays) should be taken into consideration.
Thus I suggest a possible solution based on a User defined Function that corresponds to the following code:
Code:
Function Completion(ByVal myStart As Date, myTime As Long, ByRef myTTable As Range, Optional hDays As String = "6 7") As Date
Dim Elaps As Long, I As Long, ttCnt As Long, J As Long
Dim StHour As Double, wArr()
'
ttCnt = myTTable.Columns.Count
ReDim wArr(1 To 2, 1 To ttCnt * 5)
For I = 1 To 5
    For J = 1 To ttCnt
        wArr(1, J + (I - 1) * ttCnt) = Round(myTTable.Cells(1, J) + (I - 1), 6)
        wArr(2, J + (I - 1) * ttCnt) = myTTable.Cells(2, J)
    Next J
Next I
StHour = myStart - Int(myStart)
For I = 0 To 1440 * 4
    If InStr(1, hDays, Weekday(myStart + I / 1440, 2), vbTextCompare) = 0 Then
        Elaps = Elaps + Application.WorksheetFunction.HLookup(Round(StHour + I / 1440, 6), wArr, 2)
        If Elaps > myTime Then
'            Debug.Print I, CDate(myStart + I / 1440)
            Exit For
        End If
    End If
Next I
Completion = myStart + I / 1440
End Function

Put the code into a standard module of yoyr vba project
Then return to worksheet
In a free area create a table with 2 rows * 9 columns:
-in the first column insert 0:00 (row 1) and 0 (row 2); this means "from time 0:00 it is not working hours"
-in the second columns insert 9:00 and 1; this means "from 9:00 it is working hours"
-in the third column insert 11:00 and 0; you guessed it: this means "from 11:00 it is not working hours"
-continue up to column 9, where you will insert 18:30 and 0 (from 18:30 it is not working hours")

Now you can use this Function using the following Syntax
=Completion(StaringTime, Duration, TimeTable[,NonWorkingDays])

StaringTime id date + hour
Duration is in Minutes
TimeTible is the range with the working hours
NonWorkingDays is a string with the "Number" of the weekdays that are to be ignored; by default it is set to "6 7"

The function will return a date + hour

For example, with reference to the attached image, the formula used in F2 is:
Code:
=Completion(B2,D2,$M$1:$U$2)
$M$1:$U$2 is the address of the working hours (in Orage, in the picture)
You can copy down the formula to get the results for the other lines

You will notice in row 6 and 7 two tasks started late on Friday, with a 40 & 100 minutes cycle time that will be completed on Monday morning; this is because Saturday and Sundays by default are ignored in the calculation.
If you wish to ignore weekends then you may add to the formula a null string, eg:
Code:
=Completion(B2,D2,$M$1:$U$2,"")
Or a string with the weekdays you wish to ignore; for example "1 5" will ignore Monday and Friday

The file has then to be saved as macro enabled

Try...
 

Attachments

  • HKS_Immagine 2022-08-30 170251.jpg
    HKS_Immagine 2022-08-30 170251.jpg
    123.1 KB · Views: 5
Last edited:

Hemanth Kumar S

New Member
Joined
Aug 29, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
You sayd nothing about working days, but I guess that weekend (and maybe other holidays) should be taken into consideration.
Thus I suggest a possible solution based on a User defined Function that corresponds to the following code:
Code:
Function Completion(ByVal myStart As Date, myTime As Long, ByRef myTTable As Range, Optional hDays As String = "6 7") As Date
Dim Elaps As Long, I As Long, ttCnt As Long, J As Long
Dim StHour As Double, wArr()
'
ttCnt = myTTable.Columns.Count
ReDim wArr(1 To 2, 1 To ttCnt * 5)
For I = 1 To 5
    For J = 1 To ttCnt
        wArr(1, J + (I - 1) * ttCnt) = Round(myTTable.Cells(1, J) + (I - 1), 6)
        wArr(2, J + (I - 1) * ttCnt) = myTTable.Cells(2, J)
    Next J
Next I
StHour = myStart - Int(myStart)
For I = 0 To 1440 * 4
    If InStr(1, hDays, Weekday(myStart + I / 1440, 2), vbTextCompare) = 0 Then
        Elaps = Elaps + Application.WorksheetFunction.HLookup(Round(StHour + I / 1440, 6), wArr, 2)
        If Elaps > myTime Then
'            Debug.Print I, CDate(myStart + I / 1440)
            Exit For
        End If
    End If
Next I
Completion = myStart + I / 1440
End Function

Put the code into a standard module of yoyr vba project
Then return to worksheet
In a free area create a table with 2 rows * 9 columns:
-in the first column insert 0:00 (row 1) and 0 (row 2); this means "from time 0:00 it is not working hours"
-in the second columns insert 9:00 and 1; this means "from 9:00 it is working hours"
-in the third column insert 11:00 and 0; you guessed it: this means "from 11:00 it is not working hours"
-continue up to column 9, where you will insert 18:30 and 0 (from 18:30 it is not working hours")

Now you can use this Function using the following Syntax
=Completion(StaringTime, Duration, TimeTable[,NonWorkingDays])

StaringTime id date + hour
Duration is in Minutes
TimeTible is the range with the working hours
NonWorkingDays is a string with the "Number" of the weekdays that are to be ignored; by default it is set to "6 7"

The function will return a date + hour

For example, with reference to the attached image, the formula used in F2 is:
Code:
=Completion(B2,D2,$M$1:$U$2)
$M$1:$U$2 is the address of the working hours (in Orage, in the picture)
You can copy down the formula to get the results for the other lines

You will notice in row 6 and 7 two tasks started late on Friday, with a 40 & 100 minutes cycle time that will be completed on Monday morning; this is because Saturday and Sundays by default are ignored in the calculation.
If you wish to ignore weekends then you may add to the formula a null string, eg:
Code:
=Completion(B2,D2,$M$1:$U$2,"")
Or a string with the weekdays you wish to ignore; for example "1 5" will ignore Monday and Friday

The file has then to be saved as macro enabled

Try...
@Antony47 - Thank You so much, It worked well.

You sayd nothing about working days, but I guess that weekend (and maybe other holidays) should be taken into consideration.
Thus I suggest a possible solution based on a User defined Function that corresponds to the following code:
Code:
Function Completion(ByVal myStart As Date, myTime As Long, ByRef myTTable As Range, Optional hDays As String = "6 7") As Date
Dim Elaps As Long, I As Long, ttCnt As Long, J As Long
Dim StHour As Double, wArr()
'
ttCnt = myTTable.Columns.Count
ReDim wArr(1 To 2, 1 To ttCnt * 5)
For I = 1 To 5
    For J = 1 To ttCnt
        wArr(1, J + (I - 1) * ttCnt) = Round(myTTable.Cells(1, J) + (I - 1), 6)
        wArr(2, J + (I - 1) * ttCnt) = myTTable.Cells(2, J)
    Next J
Next I
StHour = myStart - Int(myStart)
For I = 0 To 1440 * 4
    If InStr(1, hDays, Weekday(myStart + I / 1440, 2), vbTextCompare) = 0 Then
        Elaps = Elaps + Application.WorksheetFunction.HLookup(Round(StHour + I / 1440, 6), wArr, 2)
        If Elaps > myTime Then
'            Debug.Print I, CDate(myStart + I / 1440)
            Exit For
        End If
    End If
Next I
Completion = myStart + I / 1440
End Function

Put the code into a standard module of yoyr vba project
Then return to worksheet
In a free area create a table with 2 rows * 9 columns:
-in the first column insert 0:00 (row 1) and 0 (row 2); this means "from time 0:00 it is not working hours"
-in the second columns insert 9:00 and 1; this means "from 9:00 it is working hours"
-in the third column insert 11:00 and 0; you guessed it: this means "from 11:00 it is not working hours"
-continue up to column 9, where you will insert 18:30 and 0 (from 18:30 it is not working hours")

Now you can use this Function using the following Syntax
=Completion(StaringTime, Duration, TimeTable[,NonWorkingDays])

StaringTime id date + hour
Duration is in Minutes
TimeTible is the range with the working hours
NonWorkingDays is a string with the "Number" of the weekdays that are to be ignored; by default it is set to "6 7"

The function will return a date + hour

For example, with reference to the attached image, the formula used in F2 is:
Code:
=Completion(B2,D2,$M$1:$U$2)
$M$1:$U$2 is the address of the working hours (in Orage, in the picture)
You can copy down the formula to get the results for the other lines

You will notice in row 6 and 7 two tasks started late on Friday, with a 40 & 100 minutes cycle time that will be completed on Monday morning; this is because Saturday and Sundays by default are ignored in the calculation.
If you wish to ignore weekends then you may add to the formula a null string, eg:
Code:
=Completion(B2,D2,$M$1:$U$2,"")
Or a string with the weekdays you wish to ignore; for example "1 5" will ignore Monday and Friday

The file has then to be saved as macro enabled

Try...
Thank you very much, that was very helpful. I appreciate how much time you saved me.

One other thing: I would appreciate your help.

1. Before giving a task to my team, I am known to desired cycle time. Later, I will also know the actual time taken. In order to calculate efficiency, I want to multiply (Time taken (mins) / Target Time (mins)*100). Example: Task A should take 20 minutes to perform and be finished by 09:20 on the same day, according to the image you included. Task A took 160 minutes (11:55) to accomplish - (9 AM - 6:30 PM) for working hours and excluding 11:15 AM for breaks). Here, I want to calculate the actual time taken to complete a tasks in mins excluding the Breaks and non-working hours / days. Request your support.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,233
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
So I seem that the question is: given the real completion time, how long the cycle really lasted, using the same "time table"?
We can use the following "function":
VBA Code:
Function RealTaken(ByVal myStart As Date, myTerm As Date, ByRef myTTable As Range, Optional hDays As String = "6 7") As Long
Dim Elaps As Long, I As Long, ttCnt As Long, J As Long
Dim StHour As Double, wArr(), oDay As Long
'
oDay = 86400            'Seconds
myStart = Round(myStart, 6)
myTerm = Round(myTerm, 6)
ttCnt = myTTable.Columns.Count
ReDim wArr(1 To 2, 1 To ttCnt * 5)
For I = 1 To 5
    For J = 1 To ttCnt
        wArr(1, J + (I - 1) * ttCnt) = Round(myTTable.Cells(1, J) + (I - 1), 6)
        wArr(2, J + (I - 1) * ttCnt) = myTTable.Cells(2, J)
    Next J
Next I
StHour = Round(myStart - Int(myStart), 6)
For I = 0 To oDay * 4 Step 5
    If InStr(1, hDays, Weekday(myStart + I / oDay, 2), vbTextCompare) = 0 Then
        Elaps = Elaps + Application.WorksheetFunction.HLookup(Round(StHour + I / oDay, 6), wArr, 2)
        If Round(myStart + I / oDay, 6) > myTerm Then
            Exit For
        End If
    End If
Next I
RealTaken = Elaps / 12
End Function
Put the code in the same vba module you used for Function Completion, then you will get the "standardized" time taken by the cycle, in minutes, using a formula like
=RealTaken(StartTime,RealCompletionTime,TimeTable[,NonWorkingDays])
Again, NonWorkingDays is optional and its default value is "6 7" (Sat and Sun)

For example:
Excel Formula:
=RealTaken(B2,G2,$M$1:$U$2)

With this value you can calculate your efficiency factor
 

Forum statistics

Threads
1,181,212
Messages
5,928,724
Members
436,625
Latest member
agholson81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top