Formula for Estimated Completion Time for Typical workday

[Jfeijoo]

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 Date	Plt Qty	Pcs	Line Rate / Hr	Time (Hrs)	Time (Days)	Estimated Completion Date
1/10	55	440	4.0000	2.0000	0.0833	1/10/23 8:00 AM​
1/10	55	220	4.0000	1.0000	0.0417	1/10/23 9:00 AM​

 

[Jfeijoo]

Yes it does

 

[awoohaw]

Yes it does

2016 allows Let statements? Please confirm?

 

[Fluff]

No it doesn't only 365 & 2021

 

[awoohaw]

No it doesn't only 365 & 2021

That is what I thought, I'm not sure what the prior message to me means then.
@Jfeijoo , what was your "yes it does" in reference to?

 

[Fluff]

That was the Op's reply to this question of yours

Does 2016 have WORKDAYS.INTL?

 

[Jfeijoo]

Im sorry I was mistaken I am running on 365

 

[awoohaw]

@Jfeijoo , with 365 you can use the LET function. So, see if this solution works? (to convert it to just using cell references in the formula is very complicated).

Some Mr Excel Questions.xlsx
	A	B	C	D	E	F	G	H	I	J
15	Start Date and Time of Task.	Plt Qty	Plts	Pcs	Line Rate / Hr	Time (Hrs)	Time (Days)	Estimated Completion Date
16	10-Jan-2023 06:00:00 AM	55	8	440	4	2	0.083333	01/10/2023 08:00:00 AM
17	10-Jan-2023 08:00:00 AM	55	4	220	4	1	0.041667	01/10/2023 09:00:00 AM
18	10-Jan-2023 09:00:00 AM	55	4	220	4	1	0.041667	01/10/2023 10:00:00 AM
19	10-Jan-2023 10:00:00 AM	55	6	330	4	1.5	0.0625	01/10/2023 11:30:00 AM
20	10-Jan-2023 11:30:00 AM	55	15	825	4	3.75	0.15625	01/11/2023 07:15:00 AM
21	11-Jan-2023 07:15:00 AM	110	50	6600	2	30	1.25	01/17/2023 01:15:00 PM
22	17-Jan-2023 01:15:00 PM	55	30	1650	4	7.5	0.3125	01/18/2023 12:45:00 PM
23	18-Jan-2023 12:45:00 PM	55	30	1650	4	7.5	0.3125	01/19/2023 12:15:00 PM
24	19-Jan-2023 12:15:00 PM	55	6	330	4	1.5	0.0625	01/19/2023 01:45:00 PM
25
Sheet7

Cell Formulas
Range	Formula
F16:F24	F16	=D16/(E16*B16)
G16:G24	G16	=F16/24
H16:H24	H16	=LET( StartDayTime, A16, StartTime, A16-INT(A16), JobTime,G16, MaxEndTime,14/24, OneWorkDay, 8/24, HoursWorkedBefore2PM, IF(StartTime + JobTime > MaxEndTime,MaxEndTime - StartTime, JobTime), TotalHoursCarriedOver,IF(StartTime + JobTime <= MaxEndTime, 0, StartTime + JobTime - MaxEndTime), DaysCarriedOver, INT(TotalHoursCarriedOver/(8/24)+1), HoursCarriedOver, IFERROR(TotalHoursCarriedOver - ((DaysCarriedOver-1) * (8/24)),0), EstCompleteDate, IF(StartTime + JobTime <= MaxEndTime, StartDayTime + JobTime, WORKDAY.INTL(StartDayTime,DaysCarriedOver,1,HOLIDAYLIST)+HoursCarriedOver + 6/24), EstCompleteDate)
A16	A16	=44936+6/24
A17:A24	A17	=H16

Named Ranges
Name	Refers To	Cells
HOLIDAYLIST	=Sheet7!$J$3:$J$4	H16:H24

 

[Jfeijoo]

This works! Is there anyway to simplify?

 

[awoohaw]

This works! Is there anyway to simplify?

Great, I am happy it fulfills your needs. Simplify? There may be some tweaks, but the Let function and using a table make the calculation easiest for me. I'll try to post the Let function with some better formatting here:

=LET(
StartDayTime, A16,
StartTime, A16-INT(A16),
JobTime,G16,
MaxEndTime,14/24,
OneWorkDay, 8/24,
HoursWorkedBefore2PM, IF(StartTime + JobTime > MaxEndTime,MaxEndTime - StartTime, JobTime), TotalHoursCarriedOver,IF(StartTime + JobTime <= MaxEndTime, 0, StartTime + JobTime - MaxEndTime),
DaysCarriedOver, INT(TotalHoursCarriedOver/(8/24)+1), HoursCarriedOver, IFERROR(TotalHoursCarriedOver -
((DaysCarriedOver-1) * (8/24)),0),
EstCompleteDate, IF(StartTime + JobTime <= MaxEndTime, StartDayTime + JobTime, WORKDAY.INTL( StartDayTime,
DaysCarriedOver, 1, HOLIDAYLIST) + HoursCarriedOver + 6/24),
EstCompleteDate)

(edit after post: each line seems to rejustify to far left margin, so I could not make it super clear. But the syntax of LET is variable comma calculation of variable. The repeating of the variable "EstCompletionDate" at the end tells the funcition to print that variable.)