Calculating end time considering non-business hours

[Hemanth Kumar S]

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
	A	B	C	D	E	F	G	H	I
1	Target Time Sheet					Working 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
2	Sl. No	Start Date and time (Task Allotted Time)	Task	Task Cycle Time	Planned Downtime (Tea & Lunch Break)	End Date and time (Task Allotted Time)	Actual Task Completion	Time Difference	Result
3	1	29/08/2022 09:00	Task A	20	0	29/08/2022 09:20	29/08/2022 11:55	02:35:00	Crossed
4	2	29/08/2022 09:45	Task B	40	0	29/08/2022 10:25	29/08/2022 11:14	00:49:00	Crossed
5	3	29/08/2022 10:35	Task C	50	15	29/08/2022 11:40	30/08/2022 16:30	04:50:00	Crossed
6	4	29/08/2022 11:50	Task D	600	45	29/08/2022 22:35	31/08/2022 16:30	17:55:00	Crossed
7	5	29/08/2022 22:45	Task E	40	10	29/08/2022 23:35	01/09/2022 16:30	16:55:00	Crossed
8	6	29/08/2022 23:45	Task F	150	0	30/08/2022 02:15	02/09/2022 11:30	09:15:00	Crossed
9	7	30/08/2022 02:25	Task G	540	0	30/08/2022 11:25	03/09/2022 16:30	05:05:00	Crossed
10	8	30/08/2022 11:35	Task H	400	0	30/08/2022 18:15	04/09/2022 16:30	22:15:00	Crossed
Value Added

Cell Formulas
Range	Formula
H3:H10	H3	=TEXT(G3-F3, "HH:MM:SS")
I3:I10	I3	=IF(HOUR(H3)>0,"Crossed",IF(MINUTE(H3)>5,"Crossed",IF(AND(MINUTE(H3)=5,SECOND(H3)>0),"Crossed","Within Time")))
E6	E6	=30+15
B4	B4	=F3+TIME(0, 10+15, 0)
B5:B10	B5	=F4+TIME(0, 10, 0)
F3:F10	F3	=B3+TIME(0, D3+E3, 0)

 

[Anthony47]

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:

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:

=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:

=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...

 

[Hemanth Kumar S]

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:

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:

=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:

=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:

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:

=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:

=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]

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":

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:

=RealTaken(B2,G2,$M$1:$U$2)

With this value you can calculate your efficiency factor

 

[Deepak_12]

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:

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:

=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:

=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...

Hello Anthony, I was having the same problem as Hemanth until I found your solution. It worked like a charm, but I can't get my head around vba code.
Could you kindly explain it to me? I would love to know how it works in depth

Thanks a lot,
Kind regards

 

[Anthony47]

I wasted some time to find my test file and the associated data structure...
The Function Completion assumes that a "working time table" is declared in the worksheet; that corresponds to the area in Orange in the image I attached "last august"
Then the code starts from the "Start date and time", adds 1 minute, test if this new "Date and time" is a working day and adds 0 or 1 depending on the second row of the "Working time table".
This "add 1 minute" cicle continue untill the expected "task cycle time" is reached (for a maximum of 1440*4 minutes, ie 4 days)

In doing this we need to keep in mind that Excel represents dates as a decimal number from 0.0 (ie 0h0m0s) to 0.999999 (23h59m59.9999sec)

Here it is the code with some added comments:

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)
'Create a "Working time table" 5 days long:
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)                         'This extract the Hour:Min from the Start time
'This simply increment 1 minute each cycle untill the declared Minutes be reached
For I = 0 To 1440 * 4
    'Check that the "current" weekday is a working day:
    If InStr(1, hDays, Weekday(myStart + I / 1440, 2), vbTextCompare) = 0 Then      '0=No match in the string
        'Elaps get incremented only if the "date + incremented time" is working time:
        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
'Return the Completion date and time:
Completion = myStart + I / 1440           'I are the passed minutes; I/1440 is its equivalent in excel timing
End Function

HTH

 

[OoM_JaN]

Great code!
I would like to change Duration in minutus into a time.
StaringTime id date + hour
Duration is in time so 120 woulde be 2:00
Where in the code should I multiply with 24 (2:00 = 0,08 *24)?

 

[Anthony47]

Where in the code should I multiply with 24 (2:00 = 0,08 *24)?

Rather than modifying the code, modify your call to the UDF

Example:
I suggest using the following formula (where D2 contains the "duration" in decimal minutes)

=Completion(B2,D2,$M$1:$U$2)

If D2 contains a time (example 2:02 to mean 2h:2min) you will use

=Completion(B2, D2*1440, $M$1:$U$2)

Would that be ok?

 

[OoM_JaN]

Rather than modifying the code, modify your call to the UDF

Example:
I suggest using the following formula (where D2 contains the "duration" in decimal minutes)

=Completion(B2,D2,$M$1:$U$2)

If D2 contains a time (example 2:02 to mean 2h:2min) you will use

=Completion(B2, D2*1440, $M$1:$U$2)

Would that be ok?

Sure, that would correct the input but my OCD what's to have my formula as clean as possible.

 

[OoM_JaN]

Sure, that would correct the input but my OCD what's to have my formula as clean as possible.

I've changed myTime As Long into

myTime As Date

and added

myTime = myTime * 1440

in the code.
I'm sure there is a better solution but it works