# Calculating end time considering non-business hours

#### Hemanth Kumar S

##### New Member
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
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
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
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
123.1 KB · Views: 5
Last edited:

#### Hemanth Kumar S

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

Replies
8
Views
414
Replies
6
Views
822
Replies
10
Views
407
Replies
0
Views
169
Replies
0
Views
232

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.

### Which adblocker are you using?

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

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