Formula for Estimated Completion Time for Typical workday

Jfeijoo

New Member
Joined
Jan 23, 2023
Messages
8
Office Version
  1. 365
Platform
  1. Windows
What formula would work in order to show estimated completion time for a typical workday from 6AM-2PM Monday through Friday no Weekends and the only data able to use is the following below but the next completion start time is the previous (line above) completion time. Also if it goes over the 8 hour day then it spills over to the next day starting at 6AM plus the additional time. Need a formula able to click and drag down.

Start DatePlt QtyPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion Date
1/10554404.00002.00000.0833
1/10/23 8:00 AM​
1/10552204.00001.00000.0417
1/10/23 9:00 AM​
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I'm unsure of some of your givens/calculations, as well as data formats. But, here is my attempt at it, this does not take weekends or holidays into consideration, I'll try to post that in a few minutes:
Cell Formulas
RangeFormula
E4:E9E4=C4/(D4*B4)
F4:F9F4=E4/24
G4:G9G4=IF((A4+F4-INT(A4))>(14/24),INT(A4)+1+6/24,A4)+E4/24
A5:A9A5=G4
 
Upvote 0
I'm unsure of some of your givens/calculations, as well as data formats. But, here is my attempt at it, this does not take weekends or holidays into consideration, I'll try to post that in a few minutes:
Cell Formulas
RangeFormula
E4:E9E4=C4/(D4*B4)
F4:F9F4=E4/24
G4:G9G4=IF((A4+F4-INT(A4))>(14/24),INT(A4)+1+6/24,A4)+E4/24
A5:A9A5=G4
This is not correct, i do not carry the remainder over. please ignore.
 
Upvote 0
OK, corrected for the correct time remainder to carry over, it also includes consideration for weekends and some fake holidays (in the 'holiday' named range):
Cell Formulas
RangeFormula
E4:E12E4=C4/(D4*B4)
F4:F12F4=E4/24
G4:G12G4=IF( (A4+F4-INT(A4))>(14/24), WORKDAY.INTL(INT(A4),1,1,HOLIDAYLIST)+6/24+ (A4+F4-INT(A4)-14/24), A4+E4/24)
A5:A12A5=G4
Named Ranges
NameRefers ToCells
HOLIDAYLIST=Sheet7!$I$3:$I$4G4:G12
 
Upvote 0
I tried that formula and it did not seem to work when the time exceeded 8 hrs as it increased passed 2pm.


Start DatePlt QtyPltsPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion Date
1/105584404.00002.00000.0833
1/10/23 8:00 AM​
1/105542204.00001.00000.0417
1/10/23 9:00 AM​
1/105542204.00001.00000.0417
1/10/23 10:00 AM​
1/105563304.00001.50000.0625
1/10/23 11:30 AM​
1/1055158254.00003.75000.1563
1/11/23 9:45 AM​
1/11132506,6003.142915.90910.6629
1/12/23 9:54 PM​
1/1155301,6504.00007.50000.3125
1/12/23 1:30 PM​
1/1255301,6504.00007.50000.3125
1/13/23 1:30 PM​
1/135563304.00001.50000.0625
1/14/23 7:30 AM​

Start DatePlt QtyPltsPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion Date
=$H$1=VLOOKUP(B4,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)8=F4*D4=VLOOKUP(B4,Rates!C:I,7,FALSE)=F4/H4=(F4/H4)/24=IF((A4+J4-INT(A4))>(14/24),INT(A4)+1+6/24,A4)+I4/24
=K4=VLOOKUP(B5,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)4=F5*D5=VLOOKUP(B5,Rates!C:I,7,FALSE)=F5/H5=(F5/H5)/24=IF((A5+J5-INT(A5))>(14/24),INT(A5)+1+6/24,A5)+I5/24
=K5=VLOOKUP(B6,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)4=F6*D6=VLOOKUP(B6,Rates!C:I,7,FALSE)=F6/H6=(F6/H6)/24=IF((A6+J6-INT(A6))>(14/24),INT(A6)+1+6/24,A6)+I6/24
=K6=VLOOKUP(B7,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)6=F7*D7=VLOOKUP(B7,Rates!C:I,7,FALSE)=F7/H7=(F7/H7)/24=IF((A7+J7-INT(A7))>(14/24),INT(A7)+1+6/24,A7)+I7/24
=K7=VLOOKUP(B8,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)15=F8*D8=VLOOKUP(B8,Rates!C:I,7,FALSE)=F8/H8=(F8/H8)/24=IF((A8+J8-INT(A8))>(14/24),INT(A8)+1+6/24,A8)+I8/24
=K8=VLOOKUP(B9,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)50=F9*D9=VLOOKUP(B9,Rates!C:I,7,FALSE)=F9/H9=(F9/H9)/24=IF((A9+J9-INT(A9))>(14/24),INT(A9)+1+6/24,A9)+I9/24
=K8=VLOOKUP(B10,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)30=F10*D10=VLOOKUP(B10,Rates!C:I,7,FALSE)=F10/H10=(F10/H10)/24=IF((A10+J10-INT(A10))>(14/24),INT(A10)+1+6/24,A10)+I10/24
=K10=VLOOKUP(B11,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)30=F11*D11=VLOOKUP(B11,Rates!C:I,7,FALSE)=F11/H11=(F11/H11)/24=IF((A11+J11-INT(A11))>(14/24),INT(A11)+1+6/24,A11)+I11/24
=K11=VLOOKUP(B12,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)6=F12*D12=VLOOKUP(B12,Rates!C:I,7,FALSE)=F12/H12=(F12/H12)/24=IF((A12+J12-INT(A12))>(14/24),INT(A12)+1+6/24,A12)+I12/24
 
Upvote 0
My first attempt did not accomodate correctly for the carry over amount, I had added the entire work time to the new day start time.
My second attempt, post #4 calculates the time past 2 pm and adds that to 6 am on the next work day (non holiday, non weekend).
 
Last edited:
Upvote 0
So when I tried the new formula, I cant get it to work like yours how do I reference the holdaylist is that what is causing the error?

Start DatePlt QtyPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion DateHOLIDAYLIST
1/10554404.00002.00000.0833
1/10/23 8:00 AM​
Saturday, January 14, 2023​
1/10552204.00001.00000.0417
1/10/23 9:00 AM​
Sunday, January 15, 2023​
1/10552204.00001.00000.0417
1/10/23 10:00 AM​
1/10553304.00001.50000.0625
1/10/23 11:30 AM​
1/10558254.00003.75000.1563
#NAME?​
#NAME?1326,6003.142915.90910.6629
#NAME?​
#NAME?551,6504.00007.50000.3125
#NAME?​
#NAME?551,6504.00007.50000.3125
#NAME?​
#NAME?553304.00001.50000.0625
#NAME?​

Start DatePlt QtyPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion DateHOLIDAYLIST
=$H$1=VLOOKUP(B4,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F4*D4=VLOOKUP(B4,Rates!C:I,7,FALSE)=F4/H4=(F4/H4)/24=IF( (A4+J4-INT(A4))>(14/24), WORKDAY.INTL(INT(A4),1,1,HOLIDAYLIST)+6/24+ (A4+J4-INT(A4)-14/24), A4+I4/24)44940
=K4=VLOOKUP(B5,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F5*D5=VLOOKUP(B5,Rates!C:I,7,FALSE)=F5/H5=(F5/H5)/24=IF( (A5+J5-INT(A5))>(14/24), WORKDAY.INTL(INT(A5),1,1,HOLIDAYLIST)+6/24+ (A5+J5-INT(A5)-14/24), A5+I5/24)44941
=K5=VLOOKUP(B6,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F6*D6=VLOOKUP(B6,Rates!C:I,7,FALSE)=F6/H6=(F6/H6)/24=IF( (A6+J6-INT(A6))>(14/24), WORKDAY.INTL(INT(A6),1,1,HOLIDAYLIST)+6/24+ (A6+J6-INT(A6)-14/24), A6+I6/24)
=K6=VLOOKUP(B7,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F7*D7=VLOOKUP(B7,Rates!C:I,7,FALSE)=F7/H7=(F7/H7)/24=IF( (A7+J7-INT(A7))>(14/24), WORKDAY.INTL(INT(A7),1,1,HOLIDAYLIST)+6/24+ (A7+J7-INT(A7)-14/24), A7+I7/24)
=K7=VLOOKUP(B8,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F8*D8=VLOOKUP(B8,Rates!C:I,7,FALSE)=F8/H8=(F8/H8)/24=IF( (A8+J8-INT(A8))>(14/24), WORKDAY.INTL(INT(A8),1,1,HOLIDAYLIST)+6/24+ (A8+J8-INT(A8)-14/24), A8+I8/24)
=K8=VLOOKUP(B9,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F9*D9=VLOOKUP(B9,Rates!C:I,7,FALSE)=F9/H9=(F9/H9)/24=IF( (A9+J9-INT(A9))>(14/24), WORKDAY.INTL(INT(A9),1,1,HOLIDAYLIST)+6/24+ (A9+J9-INT(A9)-14/24), A9+I9/24)
=K8=VLOOKUP(B10,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F10*D10=VLOOKUP(B10,Rates!C:I,7,FALSE)=F10/H10=(F10/H10)/24=IF( (A10+J10-INT(A10))>(14/24), WORKDAY.INTL(INT(A10),1,1,HOLIDAYLIST)+6/24+ (A10+J10-INT(A10)-14/24), A10+I10/24)
=K10=VLOOKUP(B11,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F11*D11=VLOOKUP(B11,Rates!C:I,7,FALSE)=F11/H11=(F11/H11)/24=IF( (A11+J11-INT(A11))>(14/24), WORKDAY.INTL(INT(A11),1,1,HOLIDAYLIST)+6/24+ (A11+J11-INT(A11)-14/24), A11+I11/24)
=K11=VLOOKUP(B12,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F12*D12=VLOOKUP(B12,Rates!C:I,7,FALSE)=F12/H12=(F12/H12)/24=IF( (A12+J12-INT(A12))>(14/24), WORKDAY.INTL(INT(A12),1,1,HOLIDAYLIST)+6/24+ (A12+J12-INT(A12)-14/24), A12+I12/24)
 
Upvote 0
So when I tried the new formula, I cant get it to work like yours how do I reference the holdaylist is that what is causing the error?

Start DatePlt QtyPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion DateHOLIDAYLIST
1/10554404.00002.00000.0833
1/10/23 8:00 AM​
Saturday, January 14, 2023​
1/10552204.00001.00000.0417
1/10/23 9:00 AM​
Sunday, January 15, 2023​
1/10552204.00001.00000.0417
1/10/23 10:00 AM​
1/10553304.00001.50000.0625
1/10/23 11:30 AM​
1/10558254.00003.75000.1563
#NAME?​
#NAME?1326,6003.142915.90910.6629
#NAME?​
#NAME?551,6504.00007.50000.3125
#NAME?​
#NAME?551,6504.00007.50000.3125
#NAME?​
#NAME?553304.00001.50000.0625
#NAME?​

Start DatePlt QtyPcsLine Rate / HrTime (Hrs)Time (Days)Estimated Completion DateHOLIDAYLIST
=$H$1=VLOOKUP(B4,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F4*D4=VLOOKUP(B4,Rates!C:I,7,FALSE)=F4/H4=(F4/H4)/24=IF( (A4+J4-INT(A4))>(14/24), WORKDAY.INTL(INT(A4),1,1,HOLIDAYLIST)+6/24+ (A4+J4-INT(A4)-14/24), A4+I4/24)44940
=K4=VLOOKUP(B5,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F5*D5=VLOOKUP(B5,Rates!C:I,7,FALSE)=F5/H5=(F5/H5)/24=IF( (A5+J5-INT(A5))>(14/24), WORKDAY.INTL(INT(A5),1,1,HOLIDAYLIST)+6/24+ (A5+J5-INT(A5)-14/24), A5+I5/24)44941
=K5=VLOOKUP(B6,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F6*D6=VLOOKUP(B6,Rates!C:I,7,FALSE)=F6/H6=(F6/H6)/24=IF( (A6+J6-INT(A6))>(14/24), WORKDAY.INTL(INT(A6),1,1,HOLIDAYLIST)+6/24+ (A6+J6-INT(A6)-14/24), A6+I6/24)
=K6=VLOOKUP(B7,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F7*D7=VLOOKUP(B7,Rates!C:I,7,FALSE)=F7/H7=(F7/H7)/24=IF( (A7+J7-INT(A7))>(14/24), WORKDAY.INTL(INT(A7),1,1,HOLIDAYLIST)+6/24+ (A7+J7-INT(A7)-14/24), A7+I7/24)
=K7=VLOOKUP(B8,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F8*D8=VLOOKUP(B8,Rates!C:I,7,FALSE)=F8/H8=(F8/H8)/24=IF( (A8+J8-INT(A8))>(14/24), WORKDAY.INTL(INT(A8),1,1,HOLIDAYLIST)+6/24+ (A8+J8-INT(A8)-14/24), A8+I8/24)
=K8=VLOOKUP(B9,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F9*D9=VLOOKUP(B9,Rates!C:I,7,FALSE)=F9/H9=(F9/H9)/24=IF( (A9+J9-INT(A9))>(14/24), WORKDAY.INTL(INT(A9),1,1,HOLIDAYLIST)+6/24+ (A9+J9-INT(A9)-14/24), A9+I9/24)
=K8=VLOOKUP(B10,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F10*D10=VLOOKUP(B10,Rates!C:I,7,FALSE)=F10/H10=(F10/H10)/24=IF( (A10+J10-INT(A10))>(14/24), WORKDAY.INTL(INT(A10),1,1,HOLIDAYLIST)+6/24+ (A10+J10-INT(A10)-14/24), A10+I10/24)
=K10=VLOOKUP(B11,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F11*D11=VLOOKUP(B11,Rates!C:I,7,FALSE)=F11/H11=(F11/H11)/24=IF( (A11+J11-INT(A11))>(14/24), WORKDAY.INTL(INT(A11),1,1,HOLIDAYLIST)+6/24+ (A11+J11-INT(A11)-14/24), A11+I11/24)
=K11=VLOOKUP(B12,'U:\Distribution and Transportation\Supply Chain\Kitting Schedule\[407 Kitting Schedule Wksheet 3.29.xlsx]Rates'!C:G,5,FALSE)=F12*D12=VLOOKUP(B12,Rates!C:I,7,FALSE)=F12/H12=(F12/H12)/24=IF( (A12+J12-INT(A12))>(14/24), WORKDAY.INTL(INT(A12),1,1,HOLIDAYLIST)+6/24+ (A12+J12-INT(A12)-14/24), A12+I12/24)
It is a named range. You can also just type in the address of the cells that the holiday list is in.
additionally, my solution in post #4 does not take into consideration work periods that span more than one day. So, if a 10 hour task starts at 1 pm it will end at 7 am on the 2nd workday. I'll have to put a calc in the 'days to add' section of the workday.intl formula to take that into consideration.
 
Upvote 0
Yes if you can add that to the formula that would be greatly appreciated, because it will eventually spill into the next workday starting at 6AM.
 
Upvote 0
Okay, Ihave 365, 2016 does not allow let, so I need to convert the named ranges in the solution I have. Does 2016 have WORKDAYS.INTL?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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
Back
Top