Date and Time Formulas

[Stelio]

Hi Fellow Excel'ers,

I'm stumped on how to get the desired result with date and time formula in document provided.

I think I've put in enough info on this document to explain the issue but will try here too.

I am needing to have the formulas correctly calculate the required response and rectification date/time during business hours based on a given date to have suppliers attend jobs on time and measure their KPI's.

The issue I have is the formula is calculating the hours correctly, but it is not giving them the full business days allowance if the time the work order was issued falls outside business times.

Not sure if I've done a good job explaining but if anyone can help I'm happy to answer any questions. The spreadsheet provided should clarify hopefully.

EXPORT SHEET:

MASTER EM SLA Report w incorrect data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Priority	Response Status	Rectification Status	Work Order Issued Date Time	Response Due Date	Completion Due Date Time
2	P4	Completed	In Progress	2/3/2023 2:10:47 PM	2/20/2023 2:10:47 PM	2/23/2023 2:10:47 PM
3	P4	Completed	In Progress	2/3/2023 2:11:20 PM	2/20/2023 2:11:20 PM	2/23/2023 2:11:20 PM
4	P3	Overdue	In Progress	2/7/2023 5:41:40 PM	2/13/2023 9:41:40 AM	2/16/2023 9:41:40 AM
5	P3	Completed	Completed	2/11/2023 11:40:32 AM	2/15/2023 11:40:32 AM	2/20/2023 11:40:32 AM
6	P3	Completed	Completed	2/12/2023 10:03:38 AM	2/15/2023 10:03:38 AM	2/20/2023 10:03:38 AM
7	P3	Completed	Completed	2/12/2023 11:20:18 AM	2/15/2023 11:20:18 AM	2/20/2023 11:20:18 AM
8	P4	Completed	Completed	2/18/2023 12:13:01 AM	3/3/2023 12:13:01 AM	3/8/2023 12:13:01 AM
9	P3	Overdue	In Progress	2/21/2023 10:43:55 AM	2/24/2023 10:43:55 AM	3/1/2023 10:43:55 AM
10	P4	Overdue	In Progress	2/23/2023 9:47:31 AM	3/9/2023 9:47:31 AM	3/14/2023 9:47:31 AM
11	P4	Overdue	In Progress	2/23/2023 11:00:49 AM	3/9/2023 11:00:49 AM	3/14/2023 11:00:49 AM		Current Formulas in column E and F
12	P4	Overdue	In Progress	2/23/2023 11:02:27 AM	3/9/2023 11:02:27 AM	3/14/2023 11:02:27 AM
13	P4	Overdue	In Progress	2/23/2023 3:12:49 PM	3/9/2023 3:12:49 PM	3/14/2023 3:12:49 PM
14	P4	Completed	In Progress	2/24/2023 2:08:56 PM	3/10/2023 2:08:56 PM	3/15/2023 2:08:56 PM
15	P4	Overdue	In Progress	2/24/2023 3:31:34 PM	3/10/2023 3:31:34 PM	3/15/2023 3:31:34 PM
16	P4	Overdue	In Progress	2/24/2023 3:46:37 PM	3/10/2023 3:46:37 PM	3/15/2023 3:46:37 PM
17	P3	Completed	Completed	2/25/2023 5:31:51 AM	3/1/2023 5:31:51 AM	3/6/2023 5:31:51 AM
18	P3	Completed	Completed	2/25/2023 7:34:53 AM	3/1/2023 7:34:53 AM	3/6/2023 7:34:53 AM
19	P3	Completed	Completed	2/26/2023 5:34:34 AM	3/1/2023 5:34:34 AM	3/6/2023 5:34:34 AM
20	P3	Completed	Completed	2/26/2023 6:51:51 AM	3/1/2023 6:51:51 AM	3/6/2023 6:51:51 AM
21	P4	In Progress	In Progress	2/27/2023 4:06:00 PM	3/14/2023 8:06:00 AM	3/17/2023 8:06:00 AM
22	P4	In Progress	In Progress	2/27/2023 4:30:41 PM	3/14/2023 8:30:41 AM	3/17/2023 8:30:41 AM
23	P4	In Progress	In Progress	2/27/2023 4:48:00 PM	3/14/2023 8:48:00 AM	3/17/2023 8:48:00 AM
24	P4	In Progress	In Progress	2/28/2023 2:40:33 PM	3/14/2023 2:40:33 PM	3/17/2023 2:40:33 PM
25	P4	In Progress	In Progress	2/28/2023 6:30:47 PM	3/15/2023 10:30:47 AM	3/20/2023 10:30:47 AM
26	P4	In Progress	In Progress	2/28/2023 6:37:14 PM	3/15/2023 10:37:14 AM	3/20/2023 10:37:14 AM
27	P3	Completed	In Progress	2/28/2023 7:58:59 PM	3/6/2023 11:58:59 AM	3/9/2023 11:58:59 AM
28
29	For P3 (Column A)
30	Column E needs to be 3 full business days after Column D if Column D lands on a public holiday or weekend (business day starts at 8am and ends 4pm) OR 24 business hours from date/time of Column F if Column D is during business hours.
31	Column F needs to be 6 full business days after Column D if Column D lands on a public holiday or weekend (business day starts at 8am and ends 4pm) OR 48 business hours from date/time of Column F if Column D is during business hours.
32
33	For P4 (Column A)
34	Column E needs to be 10 full business days after Column D if Column D lands on a public holiday or weekend (business day starts at 8am and ends 4pm) OR 80 business hours from date/time of Column F if Column D is during business hours.
35	Column F needs to be 13 full business days after Column D if Column D lands on a public holiday or weekend (business day starts at 8am and ends 4pm) OR 104 business hours from date/time of Column F if Column D is during business hours.
36
37	Desired Results of above table as below.
38
39	Priority	Response Status	Rectification Status	Work Order Issued Date Time	Response Due Date	Completion Due Date Time
40	P4	Completed	In Progress	2/3/2023 2:10:47 PM	2/17/2023 2:10:47 PM	2/22/2023 2:10:47 PM
41	P4	Completed	In Progress	2/3/2023 2:11:20 PM	2/17/2023 2:11:20 PM	2/22/2023 2:11:20 PM
42	P3	Overdue	In Progress	2/7/2023 5:41:40 PM	2/10/2023 4:00:00 PM	2/15/2023 4:00:00 PM
43	P3	Completed	Completed	2/11/2023 11:40:32 AM	2/15/2023 4:00:00 PM	2/20/2023 4:00:00 PM
44	P3	Completed	Completed	2/12/2023 10:03:38 AM	2/15/2023 4:00:00 PM	2/20/2023 4:00:00 PM
45	P3	Completed	Completed	2/12/2023 11:20:18 AM	2/15/2023 4:00:00 PM	2/20/2023 4:00:00 PM
46	P4	Completed	Completed	2/18/2023 12:13:01 AM	3/3/2023 4:00:00 PM	3/8/2023 4:00:00 PM
47	P3	Overdue	In Progress	2/21/2023 10:43:55 AM	2/24/2023 10:43:55 AM	3/1/2023 10:43:55 AM
48	P4	Overdue	In Progress	2/23/2023 9:47:31 AM	3/9/2023 9:47:31 AM	3/14/2023 9:47:31 AM
49	P4	Overdue	In Progress	2/23/2023 11:00:49 AM	3/9/2023 11:00:49 AM	3/14/2023 11:00:49 AM
50	P4	Overdue	In Progress	2/23/2023 11:02:27 AM	3/9/2023 11:02:27 AM	3/14/2023 11:02:27 AM
51	P4	Overdue	In Progress	2/23/2023 3:12:49 PM	3/9/2023 3:12:49 PM	3/14/2023 3:12:49 PM
52	P4	Completed	In Progress	2/24/2023 2:08:56 PM	3/10/2023 2:08:56 PM	3/15/2023 2:08:56 PM
53	P4	Overdue	In Progress	2/24/2023 3:31:34 PM	3/10/2023 3:31:34 PM	3/15/2023 3:31:34 PM
54	P4	Overdue	In Progress	2/24/2023 3:46:37 PM	3/10/2023 3:46:37 PM	3/15/2023 3:46:37 PM
55	P3	Completed	Completed	2/25/2023 5:31:51 AM	3/1/2023 4:00:00 PM	3/6/2023 4:00:00 PM
56	P3	Completed	Completed	2/25/2023 7:34:53 AM	3/1/2023 4:00:00 PM	3/6/2023 4:00:00 PM
57	P3	Completed	Completed	2/26/2023 5:34:34 AM	3/1/2023 4:00:00 PM	3/6/2023 4:00:00 PM
58	P3	Completed	Completed	2/26/2023 6:51:51 AM	3/1/2023 4:00:00 PM	3/6/2023 4:00:00 PM
59	P4	In Progress	In Progress	2/27/2023 4:06:00 PM	3/13/2023 4:00:00 PM	3/16/2023 4:00:00 PM
60	P4	In Progress	In Progress	2/27/2023 4:30:41 PM	3/13/2023 4:00:00 PM	3/16/2023 4:00:00 PM
61	P4	In Progress	In Progress	2/27/2023 4:48:00 PM	3/13/2023 4:00:00 PM	3/16/2023 4:00:00 PM
62	P4	In Progress	In Progress	2/28/2023 2:40:33 PM	3/14/2023 2:40:33 PM	3/17/2023 2:40:33 PM
63	P4	In Progress	In Progress	2/28/2023 6:30:47 PM	3/14/2023 4:00:00 PM	3/17/2023 4:00:00 PM
64	P4	In Progress	In Progress	2/28/2023 6:37:14 PM	3/14/2023 4:00:00 PM	3/17/2023 4:00:00 PM
65	P3	Completed	In Progress	2/28/2023 7:58:59 PM	3/3/2023 4:00:00 PM	3/8/2023 4:00:00 PM
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
Export

Cell Formulas
Range	Formula
E2:E27	E2	=IF(A2="P3",IF((MOD(D2,1)+MOD(24,8)/24)>2/3+0.0000000001,WORKDAY(D2,INT(24/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(24,8)/24-1/3,WORKDAY(D2,INT(24/8),'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(24,8)/24),IF(A2="P4",IF((MOD(D2,1)+MOD(80,8)/24)>2/3+0.0000000001,WORKDAY(D2,INT(80/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(80,8)/24-1/3,WORKDAY(D2,INT(80/8),'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(80,8)/24),IF(ISBLANK(A2),"")))
F2:F27	F2	=IF(A2="P3",IF((MOD(D2,1)+MOD(48,8)/24)>2/3+0.0000000001,WORKDAY(D2,INT(48/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(48,8)/24-1/3,WORKDAY(D2,INT(48/8),'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(48,8)/24),IF(A2="P4",IF((MOD(D2,1)+MOD(104,8)/24)>2/3+0.0000000001,WORKDAY(D2,INT(104/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(104,8)/24-1/3,WORKDAY(D2,INT(104/8),'Public Holidays'!$B$1:$B$12)+MOD(D2,1)+MOD(104,8)/24),IF(ISBLANK(A2),"")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D39:D65	Cell Value	duplicates	text	NO

PUBLIC HOLIDAYS SHEET:

MASTER EM SLA Report w incorrect data.xlsx
	A	B
1	Day after New Year’s Day	1/01/2023
2		3/01/2023
3		2/01/2023
4	Waitangi Day	6/02/2023
5	Good Friday	7/04/2023
6	Easter Monday	10/04/2023
7	Anzac Day	25/04/2023
8	King’s Birthday	5/06/2023
9	Matariki	14/07/2023
10	Labour Day	23/10/2023
11	Christmas Day	25/12/2023
12	Boxing Day	26/12/2023
13
14
Public Holidays

 

[Stelio]

@Stelio , I just had a thought What happens when the work order arrives between 800 and 1600 on a weekend day?

Then the formula should start counting the response time from Monday morning 8am.
So for a P3 the response time should be Wednesday 4pm. Same should apply for the timeframes for rectification for P3 and also timeframes for P4.

Hope this makes sense?

 

[awoohaw]

Then the formula should start counting the response time from Monday morning 8am.
So for a P3 the response time should be Wednesday 4pm. Same should apply for the timeframes for rectification for P3 and also timeframes for P4.

Hope this makes sense?

okay, I expected that. but it requires a tweak in the formulas, i think.

 

[Stelio]

okay, I expected that. but it requires a tweak in the formulas, i think.

I’ll test tomorrow and let you k ow. Thanks again for the great and much needed help.

 

[awoohaw]

Okay, I had a lot of unfound errors so please ignore the above. I still have two differences with your expected results though:

mr excel questions 15.xlsm
	A	B	C	D	E	F	G	H	I	J	K
46	Priority	Response Status	Rectification Status	Work Order Issued Date Time	Response Due Date	Completion Due Date Time	New Response Due Date	New Completion Due Date
47	P4	Completed	In Progress	Friday 2023-02-03 14:10:47	Friday 2023-02-17 14:10:47	Wednesday 2023-02-22 14:10:47	Monday 2023-02-20 14:10:47	Thursday 2023-02-23 14:10:47		Day after New Year’s Day	2023-01-01 00:00
48	P4	Completed	In Progress	Friday 2023-02-03 14:11:20	Friday 2023-02-17 14:11:20	Wednesday 2023-02-22 14:11:20	Monday 2023-02-20 14:11:20	Thursday 2023-02-23 14:11:20			2023-01-03 00:00
49	P3	Overdue	In Progress	Tuesday 2023-02-07 17:41:40	Friday 2023-02-10 16:00:00	Wednesday 2023-02-15 16:00:00	Friday 2023-02-10 16:00:00	Wednesday 2023-02-15 16:00:00			2023-01-02 00:00
50	P3	Completed	Completed	Saturday 2023-02-11 11:40:32	Wednesday 2023-02-15 16:00:00	Monday 2023-02-20 16:00:00	Wednesday 2023-02-15 16:00:00	Monday 2023-02-20 16:00:00		Waitangi Day	2023-02-06 00:00
51	P3	Completed	Completed	Sunday 2023-02-12 10:03:38	Wednesday 2023-02-15 16:00:00	Monday 2023-02-20 16:00:00	Wednesday 2023-02-15 16:00:00	Monday 2023-02-20 16:00:00		Good Friday	2023-04-07 00:00
52	P3	Completed	Completed	Sunday 2023-02-12 11:20:18	Wednesday 2023-02-15 16:00:00	Monday 2023-02-20 16:00:00	Wednesday 2023-02-15 16:00:00	Monday 2023-02-20 16:00:00		Easter Monday	2023-04-10 00:00
53	P4	Completed	Completed	Saturday 2023-02-18 00:13:01	Friday 2023-03-03 16:00:00	Wednesday 2023-03-08 16:00:00	Friday 2023-03-03 16:00:00	Wednesday 2023-03-08 16:00:00		Anzac Day	2023-04-25 00:00
54	P3	Overdue	In Progress	Tuesday 2023-02-21 10:43:55	Friday 2023-02-24 10:43:55	Wednesday 2023-03-01 10:43:55	Friday 2023-02-24 10:43:55	Wednesday 2023-03-01 10:43:55		King’s Birthday	2023-06-05 00:00
55	P4	Overdue	In Progress	Thursday 2023-02-23 09:47:31	Thursday 2023-03-09 09:47:31	Tuesday 2023-03-14 09:47:31	Thursday 2023-03-09 09:47:31	Tuesday 2023-03-14 09:47:31		Matariki	2023-07-14 00:00
56	P4	Overdue	In Progress	Thursday 2023-02-23 11:00:49	Thursday 2023-03-09 11:00:49	Tuesday 2023-03-14 11:00:49	Thursday 2023-03-09 11:00:49	Tuesday 2023-03-14 11:00:49		Labour Day	2023-10-23 00:00
57	P4	Overdue	In Progress	Thursday 2023-02-23 11:02:27	Thursday 2023-03-09 11:02:27	Tuesday 2023-03-14 11:02:27	Thursday 2023-03-09 11:02:27	Tuesday 2023-03-14 11:02:27		Christmas Day	2023-12-25 00:00
58	P4	Overdue	In Progress	Thursday 2023-02-23 15:12:49	Thursday 2023-03-09 15:12:49	Tuesday 2023-03-14 15:12:49	Thursday 2023-03-09 15:12:49	Tuesday 2023-03-14 15:12:49		Boxing Day	2023-12-26 00:00
59	P4	Completed	In Progress	Friday 2023-02-24 14:08:56	Friday 2023-03-10 14:08:56	Wednesday 2023-03-15 14:08:56	Friday 2023-03-10 14:08:56	Wednesday 2023-03-15 14:08:56
60	P4	Overdue	In Progress	Friday 2023-02-24 15:31:34	Friday 2023-03-10 15:31:34	Wednesday 2023-03-15 15:31:34	Friday 2023-03-10 15:31:34	Wednesday 2023-03-15 15:31:34
61	P4	Overdue	In Progress	Friday 2023-02-24 15:46:37	Friday 2023-03-10 15:46:37	Wednesday 2023-03-15 15:46:37	Friday 2023-03-10 15:46:37	Wednesday 2023-03-15 15:46:37
62	P3	Completed	Completed	Saturday 2023-02-25 09:31:51	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00
63	P3	Completed	Completed	Saturday 2023-02-25 07:34:53	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00
64	P3	Completed	Completed	Sunday 2023-02-26 05:34:34	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00
65	P3	Completed	Completed	Sunday 2023-02-26 06:51:51	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00	Wednesday 2023-03-01 16:00:00	Monday 2023-03-06 16:00:00
66	P4	In Progress	In Progress	Monday 2023-02-27 16:06:00	Monday 2023-03-13 16:00:00	Thursday 2023-03-16 16:00:00	Monday 2023-03-13 16:00:00	Thursday 2023-03-16 16:00:00
67	P4	In Progress	In Progress	Monday 2023-02-27 16:30:41	Monday 2023-03-13 16:00:00	Thursday 2023-03-16 16:00:00	Monday 2023-03-13 16:00:00	Thursday 2023-03-16 16:00:00
68	P4	In Progress	In Progress	Monday 2023-02-27 16:48:00	Monday 2023-03-13 16:00:00	Thursday 2023-03-16 16:00:00	Monday 2023-03-13 16:00:00	Thursday 2023-03-16 16:00:00
69	P4	In Progress	In Progress	Tuesday 2023-02-28 14:40:33	Tuesday 2023-03-14 14:40:33	Friday 2023-03-17 14:40:33	Tuesday 2023-03-14 14:40:33	Friday 2023-03-17 14:40:33
70	P4	In Progress	In Progress	Tuesday 2023-02-28 18:30:47	Tuesday 2023-03-14 16:00:00	Friday 2023-03-17 16:00:00	Tuesday 2023-03-14 16:00:00	Friday 2023-03-17 16:00:00
71	P4	In Progress	In Progress	Tuesday 2023-02-28 18:37:14	Tuesday 2023-03-14 16:00:00	Friday 2023-03-17 16:00:00	Tuesday 2023-03-14 16:00:00	Friday 2023-03-17 16:00:00
72	P3	Completed	In Progress	Tuesday 2023-02-28 19:58:59	Friday 2023-03-03 16:00:00	Wednesday 2023-03-08 16:00:00	Friday 2023-03-03 16:00:00	Wednesday 2023-03-08 16:00:00
Export

Cell Formulas
Range	Formula
G47:G72	G47	=WORKDAY.INTL($D47,IF($A47="P4",10,3),1,PublicHolidays) + IF(OR(WEEKDAY($D47)=1,WEEKDAY($D47)=7),16/24, IF(AND(MOD($D47,1)<=16/24,MOD($D47,1)>=8/24),MOD($D47,1),16/24))
H47:H72	H47	=WORKDAY.INTL($D47,IF($A47="P4",13,6),1,PublicHolidays) + IF(OR(WEEKDAY($D47)=1,WEEKDAY($D47)=7),16/24, IF(AND(MOD($D47,1)<=16/24,MOD($D47,1)>=8/24),MOD($D47,1),16/24))

Named Ranges
Name	Refers To	Cells
PublicHolidays	=Export!$K$47:$K$58	G47:H72

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H47:H72	Expression	=ROUND(F47,6)<>ROUND(H47,6)	text	NO
F47:F72	Expression	=ROUND(F47,6)<>ROUND(H47,6)	text	NO
E47:E72	Expression	=ROUND(E47,6)<>ROUND(G47,6)	text	NO
G47:G74	Expression	=ROUND(E47,6)<>ROUND(G47,6)	text	NO

 

[Stelio]

Hi awoohaw!

Your formula works great and give the exact expected results. The differences in your two results are due to my error inputting manually the expected results as there is a public holiday on 6.02 which I missed.

Now for the final challenge.
How do I now pair these formulas so they execute for all the priorities. I need to pair formula 1&2 and then pair formula 3&4.
Very excited to get this one working!

Formula 1

=IF(A47="P2",D47+(4/24),IF(A47="P1",D27+(2/24),IF(ISBLANK(A27),"")))

Formula 2

=WORKDAY.INTL($D47,IF($A47="P4",10,3),1,PublicHolidays) + IF(OR(WEEKDAY($D47)=1,WEEKDAY($D47)=7),16/24, IF(AND(MOD($D47,1)<=16/24,MOD($D47,1)>=8/24),MOD($D47,1),16/24))


Formula 3

=IF(A47="P2",D27+(8/24),IF(A47="P1",D47+(4/24),IF(ISBLANK(A27),"")))

Formula 4

=WORKDAY.INTL($D47,IF($A47="P4",13,6),1,PublicHolidays) + IF(OR(WEEKDAY($D47)=1,WEEKDAY($D47)=7),16/24, IF(AND(MOD($D47,1)<=16/24,MOD($D47,1)>=8/24),MOD($D47,1),16/24))

 

[awoohaw]

im confused ... what are formulas 1 and 3? What cells do they go in? in both formulas 1 and 3 you refer to cells in row 47 and row 27? (Should that be only row 47).

Or are you saying you want to combine the value check for cells in COLUMN A into the formulas in COLUMNS E and F?

Also, I learned another method of entering the time calculation for the hour checks. Instead of 16/24 you could use Time(16,0,0) it is more key strokes but it is more easily understood for debugging.

 

[Stelio]

im confused ... what are formulas 1 and 3? What cells do they go in? in both formulas 1 and 3 you refer to cells in row 47 and row 27? (Should that be only row 47).

Or are you saying you want to combine the value check for cells in COLUMN A into the formulas in COLUMNS E and F?

Also, I learned another method of entering the time calculation for the hour checks. Instead of 16/24 you could use Time(16,0,0) it is more key strokes but it is more easily understood for debugging.

Hi awoohaw,

Apologies, I copied the formulas and forgot to change the cell references. See below. The additional formula for each repsonse and rectification is for the P1'a and P'2 which works fine, just needs to be combined to process all the priorities.

=IF(A47="P2",D47+(4/24),IF(A47="P1",D47+(2/24),IF(ISBLANK(A47),"")))

Formula 2

=WORKDAY.INTL($D47,IF($A47="P4",10,3),1,PublicHolidays) + IF(OR(WEEKDAY($D47)=1,WEEKDAY($D47)=7),16/24, IF(AND(MOD($D47,1)<=16/24,MOD($D47,1)>=8/24),MOD($D47,1),16/24))


Formula 3

=IF(A47="P2",D47+(8/24),IF(A47="P1",D47+(4/24),IF(ISBLANK(A47),"")))

Formula 4

=WORKDAY.INTL($D47,IF($A47="P4",13,6),1,PublicHolidays) + IF(OR(WEEKDAY($D47)=1,WEEKDAY($D47)=7),16/24, IF(AND(MOD($D47,1)<=16/24,MOD($D47,1)>=8/24),MOD($D47,1),16/24))

 

[awoohaw]

ok, not a problem, i just wanted to be sure.

But, you didn't answer the more important question.. Do you need P1 and P2 considerations in the calculation in Columns E and F? If not where?

 

[Stelio]

ok, not a problem, i just wanted to be sure.

But, you didn't answer the more important question.. Do you need P1 and P2 considerations in the calculation in Columns E and F? If not where?

Yes please - columns E and F need to calculate P1's and P'2 as well. I split this out as these were working and only had an issue with P3's and P4's.
Hope this makes sense?

 

[Stelio]

Hi awoohaw.

I found an issue where if the Work Order is issued on a weekend, the response times and dates are correct - however, when it's raised out of hours i.e. on a Monday 6am the formulas are adding one extra business day to the response due and rectification due. I tried changing the parameters IF($A2="P4",10,3) and IF($A2="P4",13,6) to a 2 and a 5 but this just reversed the issue where out of hours is fine but weekends is incorrect. Any idea on this one?

MASTER EM SLA Report w incorrect data.xlsx
	A	B	C	D	E	F	G	H	I
1	Priority	Response Status	Rectification Status	Work Order Issued Date Time	Response Due Date	Completion Due Date Time	Response Due Result Expected	Rect. Due Result Expected	Comment
2	P3	Completed	In Progress	3/11/2023 12:10:47 AM	3/15/2023 4:00:00 PM	3/20/2023 4:00:00 PM	Correct	Correct
3	P4	Completed	In Progress	3/11/2023 12:10:47 AM	3/24/2023 4:00:00 PM	3/29/2023 4:00:00 PM	Correct	Correct
4	P3	Completed	In Progress	3/13/2023 12:10:47 AM	3/16/2023 4:00:00 PM	3/21/2023 4:00:00 PM	3/15/2023 4:00:00 PM	3/20/2023 4:00:00 PM	Formula is adding an extra business day to P3's when raised out of hours.
5	P4	Completed	In Progress	3/13/2023 7:59:00 AM	3/27/2023 4:00:00 PM	3/30/2023 4:00:00 PM	3/24/2023 4:00:00 PM	3/29/2023 4:00:00 PM	Formula is adding extra business day to P4's when raised out of hours
6	P3	Completed	Completed	3/13/2023 6:00:00 AM	3/16/2023 6:00:00 AM	3/21/2023 6:00:00 AM
7	P3	Completed	Completed	2/12/2023 11:20:18 AM	2/15/2023 11:20:18 AM	2/20/2023 11:20:18 AM
8	P4	Completed	Completed	2/18/2023 12:13:01 AM	3/3/2023 12:13:01 AM	3/8/2023 12:13:01 AM
9	P3	Overdue	In Progress	2/21/2023 10:43:55 AM	2/24/2023 10:43:55 AM	3/1/2023 10:43:55 AM
10	P4	Overdue	In Progress	2/23/2023 9:47:31 AM	3/9/2023 9:47:31 AM	3/14/2023 9:47:31 AM
11	P4	Overdue	In Progress	2/23/2023 11:00:49 AM	3/9/2023 11:00:49 AM	3/14/2023 11:00:49 AM		Current Formulas in column E and F
12	P4	Overdue	In Progress	2/23/2023 11:02:27 AM	3/9/2023 11:02:27 AM	3/14/2023 11:02:27 AM
13	P4	Overdue	In Progress	2/23/2023 3:12:49 PM	3/9/2023 3:12:49 PM	3/14/2023 3:12:49 PM
14	P4	Completed	In Progress	2/24/2023 2:08:56 PM	3/10/2023 2:08:56 PM	3/15/2023 2:08:56 PM
15	P4	Overdue	In Progress	2/24/2023 3:31:34 PM	3/10/2023 3:31:34 PM	3/15/2023 3:31:34 PM
16	P4	Overdue	In Progress	2/24/2023 3:46:37 PM	3/10/2023 3:46:37 PM	3/15/2023 3:46:37 PM
17	P3	Completed	Completed	2/25/2023 5:31:51 AM	3/1/2023 5:31:51 AM	3/6/2023 5:31:51 AM
18	P3	Completed	Completed	2/25/2023 7:34:53 AM	3/1/2023 7:34:53 AM	3/6/2023 7:34:53 AM
19	P3	Completed	Completed	2/26/2023 5:34:34 AM	3/1/2023 5:34:34 AM	3/6/2023 5:34:34 AM
20	P3	Completed	Completed	2/26/2023 6:51:51 AM	3/1/2023 6:51:51 AM	3/6/2023 6:51:51 AM
21	P4	In Progress	In Progress	2/27/2023 4:06:00 PM	3/14/2023 8:06:00 AM	3/17/2023 8:06:00 AM
22	P4	In Progress	In Progress	2/27/2023 4:30:41 PM	3/14/2023 8:30:41 AM	3/17/2023 8:30:41 AM
23	P4	In Progress	In Progress	2/27/2023 4:48:00 PM	3/14/2023 8:48:00 AM	3/17/2023 8:48:00 AM
24	P4	In Progress	In Progress	2/28/2023 2:40:33 PM	3/14/2023 2:40:33 PM	3/17/2023 2:40:33 PM
25	P4	In Progress	In Progress	2/28/2023 6:30:47 PM	3/15/2023 10:30:47 AM	3/20/2023 10:30:47 AM
26	P4	In Progress	In Progress	2/28/2023 6:37:14 PM	3/15/2023 10:37:14 AM	3/20/2023 10:37:14 AM
27	P3	Completed	In Progress	2/28/2023 7:58:59 PM	3/6/2023 11:58:59 AM	3/9/2023 11:58:59 AM
28
29	For P3 (Column A)
30	Column E needs to be 3 full business days after Column D if Column D lands on a public holiday, weekend or out of hours (business day starts at 8am and ends 4pm) OR 24 business hours from date/time of Column F if Column D is during business hours.
31	Column F needs to be 6 full business days after Column D if Column D lands on a public holiday, weekend or out of hours (business day starts at 8am and ends 4pm) OR 48 business hours from date/time of Column F if Column D is during business hours.
32
33	For P4 (Column A)
34	Column E needs to be 10 full business days after Column D if Column D lands on a public holiday, weekend or out of hours (business day starts at 8am and ends 4pm) OR 80 business hours from date/time of Column F if Column D is during business hours.
35	Column F needs to be 13 full business days after Column D if Column D lands on a public holiday, weekend or out of hours (business day starts at 8am and ends 4pm) OR 104 business hours from date/time of Column F if Column D is during business hours.
Export

Cell Formulas
Range	Formula
E2:E5	E2	=WORKDAY.INTL($D2,IF($A2="P4",10,3),1,'Public Holidays'!B1:B12)+IF(OR(WEEKDAY($D2)=1,WEEKDAY($D2)=7),16/24,IF(AND(MOD($D2,1)<=16/24,MOD($D2,1)>=8/24),MOD($D2,1),16/24))
F2	F2	=WORKDAY.INTL($D2,IF($A2="P4",13,6),1,'Public Holidays'!B1:B12)+IF(OR(WEEKDAY($D2)=1,WEEKDAY($D2)=7),16/24,IF(AND(MOD($D2,1)<=16/24,MOD($D2,1)>=8/24),MOD($D2,1),16/24))
F3:F5	F3	=WORKDAY.INTL($D3,IF($A3="P4",13,6),1,'Public Holidays'!B2:B13)+IF(OR(WEEKDAY($D3)=1,WEEKDAY($D3)=7),16/24, IF(AND(MOD($D3,1)<=16/24,MOD($D3,1)>=8/24),MOD($D3,1),16/24))
E6:E27	E6	=IF(A6="P3",IF((MOD(D6,1)+MOD(24,8)/24)>2/3+0.0000000001,WORKDAY(D6,INT(24/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(24,8)/24-1/3,WORKDAY(D6,INT(24/8),'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(24,8)/24),IF(A6="P4",IF((MOD(D6,1)+MOD(80,8)/24)>2/3+0.0000000001,WORKDAY(D6,INT(80/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(80,8)/24-1/3,WORKDAY(D6,INT(80/8),'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(80,8)/24),IF(ISBLANK(A6),"")))
F6:F27	F6	=IF(A6="P3",IF((MOD(D6,1)+MOD(48,8)/24)>2/3+0.0000000001,WORKDAY(D6,INT(48/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(48,8)/24-1/3,WORKDAY(D6,INT(48/8),'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(48,8)/24),IF(A6="P4",IF((MOD(D6,1)+MOD(104,8)/24)>2/3+0.0000000001,WORKDAY(D6,INT(104/8)+1,'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(104,8)/24-1/3,WORKDAY(D6,INT(104/8),'Public Holidays'!$B$1:$B$12)+MOD(D6,1)+MOD(104,8)/24),IF(ISBLANK(A6),"")))

Public Holidays:

MASTER EM SLA Report w incorrect data.xlsx
	A	B
1	Day after New Year’s Day	1/01/2023
2		3/01/2023
3		2/01/2023
4	Waitangi Day	6/02/2023
5	Good Friday	7/04/2023
6	Easter Monday	10/04/2023
7	Anzac Day	25/04/2023
8	King’s Birthday	5/06/2023
9	Matariki	14/07/2023
10	Labour Day	23/10/2023
11	Christmas Day	25/12/2023
12	Boxing Day	26/12/2023
Public Holidays