Calculate end date and time excluding non-work hours

[Asbestos_Jen]

I have a spreadsheet which currently calculates work due times using user-input start times and turn-around-times (index,match,match from a table). I would like to be able to change the formula to accomodate work hours and holidays. For example, work coded as "urgent" is due 3.5 business hours after receipt, and rush is received+1business day, and standard is received+2business days. I've found how to incorporate full business days into the formula using networkdays, but can't figure out portions of business days.

 

[Asbestos_Jen]

Here's the mini-sheet. You can see that the due date excluding non-work hours matches the manual due date when the TAT is more than a work day, but not for urgent files received late in the work day.

LoginTemplate_smaller.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Project #	Sample Type	Sample Count	TAT	RecdTime	Latest Off Time dd-mmm-yy hh:mm	Due Time	ExcludeNonworkHours	TAT Add days	Should be due (manual)		Sample Type	TAT Code	FBCT TAT time (days)	BULK TAT time (days)	FBCT TAT time (hours)	BULK TAT time (hours)
2	23-0001	FBCT	1	U	05-Oct-23 15:25	05-Oct-23 14:10	05-Oct-23 18:25	05-Oct-23 15:25	0.13	06-Oct-23 09:25		BULK	n/a	n/a	n/a	n/a	n/a
3	23-0002	BULK	3	S	05-Oct-23 15:44	n/a	07-Oct-23 15:44	10-Oct-23 15:44	2.00	10-Oct-23 15:44		FBCT	U	0.083	0.146	2	3.5
4	23-0003						Enter due time	#N/A	#N/A			PT	R	0.833	1	20	24
5	23-0004						Enter due time	#N/A	#N/A				S	2	2	48	48
6	23-0005						Enter due time	#N/A	#N/A				A	0.083	0.146	2	3.5
7	23-0006						Enter due time	#N/A	#N/A
8	23-0007						Enter due time	#N/A	#N/A				HolidayList			StartTime	08:00
9	23-0008						Enter due time	#N/A	#N/A				1-Jan-23	New Year's Day		EndTime	17:00
10	23-0009						Enter due time	#N/A	#N/A				20-Feb-23	Family Day
11	23-0010						Enter due time	#N/A	#N/A				7-Apr-23	Good Friday
12	23-0011						Enter due time	#N/A	#N/A				22-May-23	Victoria Day
13	23-0012						Enter due time	#N/A	#N/A				1-Jul-23	Canada Day
14	23-0013						Enter due time	#N/A	#N/A				4-Sep-23	Labour Day
15	23-0014						Enter due time	#N/A	#N/A				9-Oct-23	Thanksgiving
16	23-0015						Enter due time	#N/A	#N/A				11-Nov-24	Remembrance Day
17	23-0016						Enter due time	#N/A	#N/A				25-Dec-23	Christmas
18	23-0017						Enter due time	#N/A	#N/A				1-Jan-24	New Year's Day
Project Listings

Cell Formulas
Range	Formula
G2:G18	G2	=IFERROR(IF($B2="Bulk",$E2+INDEX('Project Listings'!$M$1:$O$6,MATCH($D2,'Project Listings'!$M$1:$M$6,0),MATCH('Project Listings'!$O$1,'Project Listings'!$M$1:$O$1,0)),IF(AND(Table1[[#This Row],[Sample Type]]="FBCT",Table1[[#This Row],[TAT]]="U"),Table1[[#This Row],[RecdTime]]+3/24,IF(AND($B2="FBCT",$D2="N/A"),$F2+20/24,$F2+INDEX('Project Listings'!$M$2:$O$6,MATCH('Project Listings'!$D2,'Project Listings'!$M$2:$M$6,0),MATCH('Project Listings'!$N$1,'Project Listings'!$M$1:$O$1))))),"Enter due time")
H2:H18	H2	=WORKDAY(E2,I2,HolidayList)+(E2-INT(E2))
I2:I18	I2	=IF($B2="Bulk",INDEX('Project Listings'!$M$1:$O$6,MATCH($D2,'Project Listings'!$M$1:$M$6,0),MATCH('Project Listings'!$O$1,'Project Listings'!$M$1:$O$1,0)),IF(AND($B2="FBCT",Table1[[#This Row],[TAT]]="U"),3/24,IF(AND($B2="FBCT",$D2="N/A"),20/24,INDEX('Project Listings'!$M$2:$O$6,MATCH('Project Listings'!$D2,'Project Listings'!$M$2:$M$6,0),MATCH('Project Listings'!$N$1,'Project Listings'!$M$1:$O$1)))))
N6:O6,N3:O4	N3	=P3/24

Named Ranges
Name	Refers To	Cells
bulk_TAT	='Project Listings'!$O$1:$O$6	G2:G3580, I2:I3580
fbct_tat	='Project Listings'!$N$1:$N$6	G2:G3580, I2:I3580
HolidayList	='Project Listings'!$M$9:$M$18	H2:H18
TAT	='Project Listings'!$M$1:$M$6	G2:G3580, I2:I3580

 

[Asbestos_Jen]

I've been working at this, and here's what I've got working. Columns V:Z are helpers to get me to the due dates & times I want (Column L). It's not very elegant, but I'm limited to features in Excel 2007 as each computer in my company runs a different version of Office (grrr).

If anyone can think of a way to simplify the formulae besides just combining them into a single cell, please let me know. I will likely combine the formulae before I release the sheet for all users at the beginning of 2024.

2024_ProjectListings_Test_Reduced.xlsx
	A	B	C	D			G	H			K	L										V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Project#	Sample Type	Sample Count	TAT			Samples Received	Analysis Completed			LatestTimeOff	NEW!!! FileDue										Hours Left in WD	AddDays	WD_Recd	Date Recd	Time Recd		TATs and SampleTypes							HolidayList
2	23-0001	BULK	2	U			03-Jan-23 08:46	3-Jan-23			N/A	03-Jan-23 12:16										0.34	0.15	Tue	3-Jan-23	08:46:00		TAT	BULK	FBCT	PT	Application		Year	Date	Wkday	HolidayName
3	23-0002	BULK	6	U			03-Jan-23 09:25	3-Jan-23			N/A	03-Jan-23 12:55										0.32	0.15	Tue	3-Jan-23	09:25:00		U	3.5	2.5	n/a	count business hours only		2023	1-Jan-23	Sun	New Year's Day
4	23-0003	BULK	8	U			03-Jan-23 09:42	3-Jan-23			N/A	03-Jan-23 13:12										0.30	0.15	Tue	3-Jan-23	09:42:00		R	24	n/a	n/a	count business days		2023	2-Jan-23	Mon	New Year's Day Lieu
5	23-0004	BULK	3	U			03-Jan-23 10:37	3-Jan-23			N/A	03-Jan-23 14:07										0.27	0.15	Tue	3-Jan-23	10:37:00		S	48	n/a	n/a	count business days		2023	20-Feb-23	Mon	Family Day
6	23-0005	FBCT	1	U			03-Jan-23 11:34	3-Jan-23			03-Jan-23 11:15	03-Jan-23 14:04										0.23	0.10	Tue	3-Jan-23	11:34:00		A	3.5	2.5	n/a	Not work hour dependent		2023	7-Apr-23	Fri	Good Friday
7	23-0006	FBCT	2	N/A			03-Jan-23 12:48	3-Jan-23			03-Jan-23 10:50	04-Jan-23 06:50										0.18	0.83	Tue	3-Jan-23	12:48:00		PT	n/a	n/a	672	Count 28 calendar days		2023	22-May-23	Mon	Victoria Day
8	23-0007	BULK	2	U			03-Jan-23 13:46	3-Jan-23			N/A	04-Jan-23 08:16										0.13	0.15	Tue	3-Jan-23	13:46:00		N/A	n/a	20	n/a	Count 20 hours from latest time off		2023	1-Jul-23	Sat	Canada Day
9	23-0008	FBCT	1	U			03-Jan-23 14:08	3-Jan-23			N/A	03-Jan-23 16:38										0.12	0.10	Tue	3-Jan-23	14:08:00								2023	3-Jul-23	Mon	Canada Day Lieu
10	23-0009	FBCT	4	N/A			04-Jan-23 08:30	4-Jan-23			03-Jan-23 14:38	04-Jan-23 10:38										0.35	0.83	Wed	4-Jan-23	08:30:00			Business	Hours				2023	7-Aug-23	Mon	Civic Holiday
11	23-0010	FBCT	3	N/A			04-Jan-23 08:31	4-Jan-23			03-Jan-23 15:08	04-Jan-23 11:08										0.35	0.83	Wed	4-Jan-23	08:31:00			Open	08:00				2023	4-Sep-23	Mon	Labour Day
12	23-0011	FBCT	1	N/A			04-Jan-23 09:08	4-Jan-23			03-Jan-23 15:00	04-Jan-23 11:00										0.33	0.83	Wed	4-Jan-23	09:08:00			Close	17:00				2023	9-Oct-23	Mon	Thanksgiving
13	23-0012	BULK	63	S			04-Jan-23 10:12	6-Jan-23			N/A	06-Jan-23 10:12										0.28	2.00	Wed	4-Jan-23	10:12:00								2023	11-Nov-23	Sat	Remembrance Day
14	23-0013	FBCT	3	N/A			04-Jan-23 10:13	4-Jan-23			03-Jan-23 15:05	04-Jan-23 11:05										0.28	0.83	Wed	4-Jan-23	10:13:00								2023	13-Nov-23	Mon	Remembrance Day Lieu
15	23-0014	FBCT	1	U			04-Jan-23 12:08	4-Jan-23			N/A	04-Jan-23 14:38										0.20	0.10	Wed	4-Jan-23	12:08:58								2023	25-Dec-23	Mon	Christmas
16	23-0015	FBCT	4	N/A			04-Jan-23 13:03	4-Jan-23			04-Jan-23 11:32	05-Jan-23 07:32										0.16	0.83	Wed	4-Jan-23	13:03:00						DATA VALIDATION RULES		2023	26-Dec-23	Tue	Boxing Day
17	23-0016	FBCT	2	N/A			04-Jan-23 13:05	4-Jan-23			04-Jan-23 12:32	05-Jan-23 08:32										0.16	0.83	Wed	4-Jan-23	13:05:00						Dates		2024	1-Jan-24	Mon	New Year's Day
18	23-0017	BULK	20	R			04-Jan-23 13:45	5-Jan-23			N/A	05-Jan-23 13:45										0.14	1.00	Wed	4-Jan-23	13:45:00					earliest	01-Jan-23 08:00		2024	19-Feb-24	Mon	Family Day
19	23-0018	FBCT	1	U			04-Jan-23 14:14	4-Jan-23			N/A	04-Jan-23 16:44										0.12	0.10	Wed	4-Jan-23	14:14:00					latest	23-Nov-23 15:10		2024	29-Mar-24	Fri	Good Friday
20	23-0019	FBCT	3	N/A			04-Jan-23 15:18	4-Jan-23			04-Jan-23 14:55	05-Jan-23 10:55										0.07	0.83	Wed	4-Jan-23	15:18:00								2024	20-May-24	Mon	Victoria Day
21	23-0020	BULK	4	U			04-Jan-23 15:43	5-Jan-23			N/A	05-Jan-23 10:13										0.05	0.15	Wed	4-Jan-23	15:43:00								2024	1-Jul-24	Mon	Canada Day
22	23-0021	FBCT	4	N/A			04-Jan-23 16:11	4-Jan-23			04-Jan-23 14:19	05-Jan-23 10:19										0.03	0.83	Wed	4-Jan-23	16:11:00								2024	5-Aug-24	Mon	Civic Holiday
23	23-0022	FBCT	3	N/A			04-Jan-23 16:13	4-Jan-23			04-Jan-23 15:14	05-Jan-23 11:14										0.03	0.83	Wed	4-Jan-23	16:13:00								2024	2-Sep-24	Mon	Labour Day
24	23-0023	BULK	3	U			05-Jan-23 08:00	5-Jan-23			N/A	05-Jan-23 11:30										0.37	0.15	Thu	5-Jan-23	08:00:00								2024	14-Oct-24	Mon	Thanksgiving
25	23-0024	BULK	3	U			05-Jan-23 08:40	5-Jan-23			N/A	05-Jan-23 12:10										0.35	0.15	Thu	5-Jan-23	08:40:00								2024	11-Nov-24	Mon	Remembrance Day
26	23-0025	BULK	2	S			05-Jan-23 10:00	9-Jan-23			N/A	09-Jan-23 10:00										0.29	2.00	Thu	5-Jan-23	10:00:00								2024	25-Dec-24	Wed	Christmas
27	23-0026	FBCT	3	N/A			05-Jan-23 10:42	5-Jan-23			04-Jan-23 12:07	05-Jan-23 08:07										0.26	0.83	Thu	5-Jan-23	10:42:00								2024	26-Dec-24	Thu	Boxing Day
28	23-0027	FBCT	1	U			05-Jan-23 12:31	5-Jan-23			N/A	05-Jan-23 15:01										0.19	0.10	Thu	5-Jan-23	12:31:00								2025	1-Jan-25	Wed	New Year's Day
29	23-0028	FBCT	1	U			05-Jan-23 12:32	5-Jan-23			N/A	05-Jan-23 15:02										0.19	0.10	Thu	5-Jan-23	12:32:00
30	23-0029	FBCT	4	N/A			05-Jan-23 12:54	5-Jan-23			05-Jan-23 11:11	06-Jan-23 07:11										0.17	0.83	Thu	5-Jan-23	12:54:00
31	23-0030	FBCT	1	U			05-Jan-23 13:19	5-Jan-23			N/A	05-Jan-23 15:49										0.15	0.10	Thu	5-Jan-23	13:19:00
32	23-0031	BULK	18	S			05-Jan-23 14:07	6-Jan-23			N/A	09-Jan-23 14:07										0.12	2.00	Thu	5-Jan-23	14:07:00
33	23-0032	FBCT	3	N/A			05-Jan-23 15:52	5-Jan-23			05-Jan-23 15:20	06-Jan-23 11:20										0.05	0.83	Thu	5-Jan-23	15:52:00
34	23-0033	FBCT	4	N/A			05-Jan-23 15:58	5-Jan-23			05-Jan-23 15:13	06-Jan-23 11:13										0.04	0.83	Thu	5-Jan-23	15:58:00
Project

Cell Formulas
Range	Formula
V2:V34	V2	=Close-$Z2
W2:W34	W2	=INDEX(TblTATs[#All],MATCH(D2,TblTATs[[#All],[TAT]],0),MATCH(B2,TblTATs[#Headers],0))/24
X2:X34	X2	=TEXT(WEEKDAY($G2,1),"Ddd")
Y2:Y34	Y2	=INT($G2)
Z2:Z34	Z2	=$G2-INT($G2)
AE7	AE7	=28*24
AF19	AF19	=NOW()
AH3:AH28	AH3	=YEAR(AI3)
AJ3:AJ28	AJ3	=TEXT(WEEKDAY(AI3,1),"Ddd")
L2:L34	L2	=IFERROR(IF(OR(D2="A",D2="PT",AND(D2="U",V2>W2)),G2+W2, IF(AND(D2="U",V2<W2),WORKDAY(G2,CEILING(W2,1),HolidayList)+Open+($W2-$V2), IF(AND(B2="FBCT",D2="n/a"),K2+W2, WORKDAY(G2,W2,HolidayList)+Z2))), "Enter due time")

Named Ranges
Name	Refers To	Cells
Close	=Project!$AD$12	V2:V34
HolidayList	=TblHolidays[Date]	L2:L34, AJ3, AH3
Open	=Project!$AD$11	L2:L34
SType	=TblTATs[[#Headers],[BULK]:[PT]]	W2:W34
TAT	=Project!$AB$3:$AB$8	W2:W34

Cells with Data Validation
Cell	Allow	Criteria
G2:H34	Date	between earliest and NOW()
B2:B34	List	=SType
C2:C34	Whole number	>=0
D2:D34	List	=TAT