Date and Time Formulas

S

Stelio

New Member
Joined
Oct 8, 2015
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1PriorityResponse StatusRectification StatusWork Order Issued Date TimeResponse Due DateCompletion Due Date Time
2P4CompletedIn Progress2/3/2023 2:10:47 PM2/20/2023 2:10:47 PM2/23/2023 2:10:47 PM
3P4CompletedIn Progress2/3/2023 2:11:20 PM2/20/2023 2:11:20 PM2/23/2023 2:11:20 PM
4P3OverdueIn Progress2/7/2023 5:41:40 PM2/13/2023 9:41:40 AM2/16/2023 9:41:40 AM
5P3CompletedCompleted2/11/2023 11:40:32 AM2/15/2023 11:40:32 AM2/20/2023 11:40:32 AM
6P3CompletedCompleted2/12/2023 10:03:38 AM2/15/2023 10:03:38 AM2/20/2023 10:03:38 AM
7P3CompletedCompleted2/12/2023 11:20:18 AM2/15/2023 11:20:18 AM2/20/2023 11:20:18 AM
8P4CompletedCompleted2/18/2023 12:13:01 AM3/3/2023 12:13:01 AM3/8/2023 12:13:01 AM
9P3OverdueIn Progress2/21/2023 10:43:55 AM2/24/2023 10:43:55 AM3/1/2023 10:43:55 AM
10P4OverdueIn Progress2/23/2023 9:47:31 AM3/9/2023 9:47:31 AM3/14/2023 9:47:31 AM
11P4OverdueIn Progress2/23/2023 11:00:49 AM3/9/2023 11:00:49 AM3/14/2023 11:00:49 AMCurrent Formulas in column E and F
12P4OverdueIn Progress2/23/2023 11:02:27 AM3/9/2023 11:02:27 AM3/14/2023 11:02:27 AM
13P4OverdueIn Progress2/23/2023 3:12:49 PM3/9/2023 3:12:49 PM3/14/2023 3:12:49 PM
14P4CompletedIn Progress2/24/2023 2:08:56 PM3/10/2023 2:08:56 PM3/15/2023 2:08:56 PM
15P4OverdueIn Progress2/24/2023 3:31:34 PM3/10/2023 3:31:34 PM3/15/2023 3:31:34 PM
16P4OverdueIn Progress2/24/2023 3:46:37 PM3/10/2023 3:46:37 PM3/15/2023 3:46:37 PM
17P3CompletedCompleted2/25/2023 5:31:51 AM3/1/2023 5:31:51 AM3/6/2023 5:31:51 AM
18P3CompletedCompleted2/25/2023 7:34:53 AM3/1/2023 7:34:53 AM3/6/2023 7:34:53 AM
19P3CompletedCompleted2/26/2023 5:34:34 AM3/1/2023 5:34:34 AM3/6/2023 5:34:34 AM
20P3CompletedCompleted2/26/2023 6:51:51 AM3/1/2023 6:51:51 AM3/6/2023 6:51:51 AM
21P4In ProgressIn Progress2/27/2023 4:06:00 PM3/14/2023 8:06:00 AM3/17/2023 8:06:00 AM
22P4In ProgressIn Progress2/27/2023 4:30:41 PM3/14/2023 8:30:41 AM3/17/2023 8:30:41 AM
23P4In ProgressIn Progress2/27/2023 4:48:00 PM3/14/2023 8:48:00 AM3/17/2023 8:48:00 AM
24P4In ProgressIn Progress2/28/2023 2:40:33 PM3/14/2023 2:40:33 PM3/17/2023 2:40:33 PM
25P4In ProgressIn Progress2/28/2023 6:30:47 PM3/15/2023 10:30:47 AM3/20/2023 10:30:47 AM
26P4In ProgressIn Progress2/28/2023 6:37:14 PM3/15/2023 10:37:14 AM3/20/2023 10:37:14 AM
27P3CompletedIn Progress2/28/2023 7:58:59 PM3/6/2023 11:58:59 AM3/9/2023 11:58:59 AM
28
29For P3 (Column A)
30Column 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.
31Column 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
33For P4 (Column A)
34Column 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.
35Column 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
37Desired Results of above table as below.
38
39PriorityResponse StatusRectification StatusWork Order Issued Date TimeResponse Due DateCompletion Due Date Time
40P4CompletedIn Progress2/3/2023 2:10:47 PM2/17/2023 2:10:47 PM2/22/2023 2:10:47 PM
41P4CompletedIn Progress2/3/2023 2:11:20 PM2/17/2023 2:11:20 PM2/22/2023 2:11:20 PM
42P3OverdueIn Progress2/7/2023 5:41:40 PM2/10/2023 4:00:00 PM2/15/2023 4:00:00 PM
43P3CompletedCompleted2/11/2023 11:40:32 AM2/15/2023 4:00:00 PM2/20/2023 4:00:00 PM
44P3CompletedCompleted2/12/2023 10:03:38 AM2/15/2023 4:00:00 PM2/20/2023 4:00:00 PM
45P3CompletedCompleted2/12/2023 11:20:18 AM2/15/2023 4:00:00 PM2/20/2023 4:00:00 PM
46P4CompletedCompleted2/18/2023 12:13:01 AM3/3/2023 4:00:00 PM3/8/2023 4:00:00 PM
47P3OverdueIn Progress2/21/2023 10:43:55 AM2/24/2023 10:43:55 AM3/1/2023 10:43:55 AM
48P4OverdueIn Progress2/23/2023 9:47:31 AM3/9/2023 9:47:31 AM3/14/2023 9:47:31 AM
49P4OverdueIn Progress2/23/2023 11:00:49 AM3/9/2023 11:00:49 AM3/14/2023 11:00:49 AM
50P4OverdueIn Progress2/23/2023 11:02:27 AM3/9/2023 11:02:27 AM3/14/2023 11:02:27 AM
51P4OverdueIn Progress2/23/2023 3:12:49 PM3/9/2023 3:12:49 PM3/14/2023 3:12:49 PM
52P4CompletedIn Progress2/24/2023 2:08:56 PM3/10/2023 2:08:56 PM3/15/2023 2:08:56 PM
53P4OverdueIn Progress2/24/2023 3:31:34 PM3/10/2023 3:31:34 PM3/15/2023 3:31:34 PM
54P4OverdueIn Progress2/24/2023 3:46:37 PM3/10/2023 3:46:37 PM3/15/2023 3:46:37 PM
55P3CompletedCompleted2/25/2023 5:31:51 AM3/1/2023 4:00:00 PM3/6/2023 4:00:00 PM
56P3CompletedCompleted2/25/2023 7:34:53 AM3/1/2023 4:00:00 PM3/6/2023 4:00:00 PM
57P3CompletedCompleted2/26/2023 5:34:34 AM3/1/2023 4:00:00 PM3/6/2023 4:00:00 PM
58P3CompletedCompleted2/26/2023 6:51:51 AM3/1/2023 4:00:00 PM3/6/2023 4:00:00 PM
59P4In ProgressIn Progress2/27/2023 4:06:00 PM3/13/2023 4:00:00 PM3/16/2023 4:00:00 PM
60P4In ProgressIn Progress2/27/2023 4:30:41 PM3/13/2023 4:00:00 PM3/16/2023 4:00:00 PM
61P4In ProgressIn Progress2/27/2023 4:48:00 PM3/13/2023 4:00:00 PM3/16/2023 4:00:00 PM
62P4In ProgressIn Progress2/28/2023 2:40:33 PM3/14/2023 2:40:33 PM3/17/2023 2:40:33 PM
63P4In ProgressIn Progress2/28/2023 6:30:47 PM3/14/2023 4:00:00 PM3/17/2023 4:00:00 PM
64P4In ProgressIn Progress2/28/2023 6:37:14 PM3/14/2023 4:00:00 PM3/17/2023 4:00:00 PM
65P3CompletedIn Progress2/28/2023 7:58:59 PM3/3/2023 4:00:00 PM3/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
RangeFormula
E2:E27E2=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:F27F2=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
CellConditionCell FormatStop If True
D39:D65Cell ValueduplicatestextNO


PUBLIC HOLIDAYS SHEET:

MASTER EM SLA Report w incorrect data.xlsx
AB
1Day after New Year’s Day1/01/2023
23/01/2023
32/01/2023
4Waitangi Day6/02/2023
5Good Friday7/04/2023
6Easter Monday10/04/2023
7Anzac Day25/04/2023
8King’s Birthday5/06/2023
9Matariki14/07/2023
10Labour Day23/10/2023
11Christmas Day25/12/2023
12Boxing Day26/12/2023
13
14
Public Holidays
 
Sort by date Sort by votes
Thanks. I'll get this done sometime after dinner. (It is 4:00 pm here.).
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sorry I didn't get it done last night. Here it is. I am not entirely sure I have the p1 and p2 adding correctly.
I removed conditional formatting to compare E & F expecteds to G & H tests because of that.

mr excel questions 15.xlsm
ABCDEFGHIJK
48PriorityResponse StatusRectification StatusWork Order Issued Date TimeResponse Due DateCompletion Due Date TimeNew Response Due DateNew Completion Due Date
49P2CompletedIn ProgressFriday 2023-02-03 14:10:47Friday 2023-02-17 14:10:47Wednesday 2023-02-22 14:10:47FALSEFALSEDay after New Year’s Day2023-01-01 00:00
50P4CompletedIn ProgressFriday 2023-02-03 14:11:20Friday 2023-02-17 14:11:20Wednesday 2023-02-22 14:11:20Monday 2023-02-20 14:11:20Thursday 2023-02-23 14:11:202023-01-03 00:00
51P3OverdueIn ProgressTuesday 2023-02-07 17:41:40Friday 2023-02-10 16:00:00Wednesday 2023-02-15 16:00:00Friday 2023-02-10 16:00:00Wednesday 2023-02-15 16:00:002023-01-02 00:00
52P3CompletedCompletedSaturday 2023-02-11 11:40:32Wednesday 2023-02-15 16:00:00Monday 2023-02-20 16:00:00Wednesday 2023-02-15 16:00:00Monday 2023-02-20 16:00:00Waitangi Day2023-02-06 00:00
53P3CompletedCompletedSunday 2023-02-12 10:03:38Wednesday 2023-02-15 16:00:00Monday 2023-02-20 16:00:00Wednesday 2023-02-15 16:00:00Monday 2023-02-20 16:00:00Good Friday2023-04-07 00:00
54P3CompletedCompletedSunday 2023-02-12 11:20:18Wednesday 2023-02-15 16:00:00Monday 2023-02-20 16:00:00Wednesday 2023-02-15 16:00:00Monday 2023-02-20 16:00:00Easter Monday2023-04-10 00:00
55P4CompletedCompletedSaturday 2023-02-18 00:13:01Friday 2023-03-03 16:00:00Wednesday 2023-03-08 16:00:00Friday 2023-03-03 16:00:00Wednesday 2023-03-08 16:00:00Anzac Day2023-04-25 00:00
56P3OverdueIn ProgressTuesday 2023-02-21 10:43:55Friday 2023-02-24 10:43:55Wednesday 2023-03-01 10:43:55Friday 2023-02-24 10:43:55Wednesday 2023-03-01 10:43:552023-06-02 00:00
57P4OverdueIn ProgressThursday 2023-02-23 09:47:31Thursday 2023-03-09 09:47:31Tuesday 2023-03-14 09:47:31Thursday 2023-03-09 09:47:31Tuesday 2023-03-14 09:47:31King’s Birthday2023-06-05 00:00
58P4OverdueIn ProgressThursday 2023-02-23 11:00:49Thursday 2023-03-09 11:00:49Tuesday 2023-03-14 11:00:49Thursday 2023-03-09 11:00:49Tuesday 2023-03-14 11:00:49Matariki2023-07-14 00:00
59P4OverdueIn ProgressThursday 2023-02-23 11:02:27Thursday 2023-03-09 11:02:27Tuesday 2023-03-14 11:02:27Thursday 2023-03-09 11:02:27Tuesday 2023-03-14 11:02:27Labour Day2023-10-23 00:00
60P4OverdueIn ProgressThursday 2023-02-23 15:12:49Thursday 2023-03-09 15:12:49Tuesday 2023-03-14 15:12:49Thursday 2023-03-09 15:12:49Tuesday 2023-03-14 15:12:49Christmas Day2023-12-25 00:00
61P4CompletedIn ProgressFriday 2023-02-24 14:08:56Friday 2023-03-10 14:08:56Wednesday 2023-03-15 14:08:56Friday 2023-03-10 14:08:56Wednesday 2023-03-15 14:08:56Boxing Day2023-12-26 00:00
62P4OverdueIn ProgressFriday 2023-02-24 15:31:34Friday 2023-03-10 15:31:34Wednesday 2023-03-15 15:31:34Friday 2023-03-10 15:31:34Wednesday 2023-03-15 15:31:34
63P4OverdueIn ProgressFriday 2023-02-24 15:46:37Friday 2023-03-10 15:46:37Wednesday 2023-03-15 15:46:37Friday 2023-03-10 15:46:37Wednesday 2023-03-15 15:46:37
64P3CompletedCompletedSaturday 2023-02-25 09:31:51Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00
65P3CompletedCompletedSaturday 2023-02-25 07:34:53Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00
66P3CompletedCompletedSunday 2023-02-26 05:34:34Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00
67P3CompletedCompletedSunday 2023-02-26 06:51:51Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00Wednesday 2023-03-01 16:00:00Monday 2023-03-06 16:00:00
68P4In ProgressIn ProgressMonday 2023-02-27 16:06:00Monday 2023-03-13 16:00:00Thursday 2023-03-16 16:00:00Monday 2023-03-13 16:00:00Thursday 2023-03-16 16:00:00
69P4In ProgressIn ProgressMonday 2023-02-27 16:30:41Monday 2023-03-13 16:00:00Thursday 2023-03-16 16:00:00Monday 2023-03-13 16:00:00Thursday 2023-03-16 16:00:00
70P4In ProgressIn ProgressMonday 2023-02-27 16:48:00Monday 2023-03-13 16:00:00Thursday 2023-03-16 16:00:00Monday 2023-03-13 16:00:00Thursday 2023-03-16 16:00:00
71P4In ProgressIn ProgressTuesday 2023-02-28 14:40:33Tuesday 2023-03-14 14:40:33Friday 2023-03-17 14:40:33Tuesday 2023-03-14 14:40:33Friday 2023-03-17 14:40:33
72P4In ProgressIn ProgressTuesday 2023-02-28 18:30:47Tuesday 2023-03-14 16:00:00Friday 2023-03-17 16:00:00Tuesday 2023-03-14 16:00:00Friday 2023-03-17 16:00:00
73P4In ProgressIn ProgressTuesday 2023-02-28 18:37:14Tuesday 2023-03-14 16:00:00Friday 2023-03-17 16:00:00Tuesday 2023-03-14 16:00:00Friday 2023-03-17 16:00:00
74P3CompletedIn ProgressTuesday 2023-02-28 19:58:59Friday 2023-03-03 16:00:00Wednesday 2023-03-08 16:00:00Friday 2023-03-03 16:00:00Wednesday 2023-03-08 16:00:00
Export
Cell Formulas
RangeFormula
G49:G74G49=IF(OR($A49="P1",$A49="P2"),IF((MOD($D47,1)+IF($A49="P1",2,4)/24)>(TIME(16,0,0)),WORKDAY.INTL($D47,1,1,PublicHolidays)+ (TIME(8,0,0))+(IF($A49="P1",2,4)/24-((TIME(16,0,0)) -MOD($D47,1)))),IF(OR($A49="P4",$A49="P3"),WORKDAY.INTL($D49,IF($A49="P4",10,3),1,PublicHolidays) + IF(OR(WEEKDAY($D49)=1,WEEKDAY($D49)=7),16/24, IF(AND(MOD($D49,1)<=TIME(16,0,0),MOD($D49,1)>=TIME(8,0,0)),MOD($D49,1),TIME(16,0,0))),""))
H49:H74H49=IF(OR($A49="P1",$A49="P2"),IF((MOD($D47,1)+IF($A49="P1",4,8)/24)>(TIME(16,0,0)),WORKDAY.INTL($D47,1,1,PublicHolidays)+ (TIME(8,0,0))+(IF($A49="P1",4,8)/24-((TIME(16,0,0)) -MOD($D47,1)))),WORKDAY.INTL($D49,IF($A49="P4",13,6),1,PublicHolidays) + IF(OR(WEEKDAY($D49)=1,WEEKDAY($D49)=7),16/24, IF(AND(MOD($D49,1)<=16/24,MOD($D49,1)>=8/24),MOD($D49,1),16/24)))
Named Ranges
NameRefers ToCells
PublicHolidays=Export!$K$49:$K$61G49:H74
 
Upvote 0
Hi awoohaw,

You are a master of date and time calculations!
I feel we're almost there.
I've modified the P1 and P2 formulas so these are now working correctly as they should. These priorities don't take into account any public holidays or weekends etc. So all good on this front.
All the other calculations are working well except for when D time is on a weekday between midnight and 8am. The formula is skipping a full business day and adding an extra day to the expected response and rectification dates/times. However, it works ok if D time is between 4pm and midnight. So I think there's an IF statement that needs to account for this.

Can't thank you enough for ploughing through this web.

Updated.xlsx
ABCDEFGHI
1PriorityResponse StatusRectification StatusWork Order Issued Date TimeResponse Due DateCompletion Due Date TimeResponse Due Result ExpectedRect. Due Result ExpectedComment
2P3CompletedIn Progress3/11/2023 12:10:47 AM3/15/2023 4:00:00 PM3/20/2023 4:00:00 PM3/15/2023 4:00:00 PM3/20/2023 4:00:00 PMCorrect if D is a weekend.
3P4CompletedIn Progress3/11/2023 12:10:47 AM3/24/2023 4:00:00 PM3/29/2023 4:00:00 PM3/24/2023 4:00:00 PM3/29/2023 4:00:00 PMCorrect if D is a weekend.
4P3CompletedIn Progress3/13/2023 12:10:47 AM3/16/2023 4:00:00 PM3/21/2023 4:00:00 PM3/15/2023 4:00:00 PM3/20/2023 4:00:00 PMFor P3's and P4's if D time is between midnight and 8am then the formula should count the next full business day as the first day. Current formula starts counting one day ahead and therefore adds an extra day to E & F. It should be counting the 13th 8am to 4pm as the first full business day.
5P4CompletedIn Progress3/13/2023 7:59:00 AM3/27/2023 4:00:00 PM3/30/2023 4:00:00 PM3/24/2023 4:00:00 PM3/29/2023 4:00:00 PMFor P3's and P4's if D time is between 4pm previous business day and 8am then the formula should count the next full business day as the first day. Current formula starts counting one day ahead and therefore adds an extra day to E & F. It should be counting the 13th 8am to 4pm as the first full business day.
6P3CompletedIn Progress3/13/2023 11:10:47 PM3/16/2023 4:00:00 PM3/21/2023 4:00:00 PM3/16/2023 4:00:00 PM3/21/2023 4:00:00 PMCorrect. D is after hours and counts the 14th as the first full business day. So after hours works well but not "before hours" as is row 4.
7P4CompletedIn Progress3/13/2023 7:59:00 PM3/27/2023 4:00:00 PM3/30/2023 4:00:00 PM3/27/2023 4:00:00 PM3/30/2023 4:00:00 PMCorrect. D is after hours and counts the 14th as the first full business day. So after hours works well but not "before hours" as is row 4.
8P3CompletedIn Progress3/7/2023 1:10:47 PM3/10/2023 1:10:47 PM3/15/2023 1:10:47 PM3/10/2023 1:10:47 PM3/15/2023 1:10:47 PMCorrect if D is during business hours.
9P4CompletedIn Progress3/7/2023 1:10:47 PM3/21/2023 1:10:47 PM3/24/2023 1:10:47 PM3/21/2023 1:10:47 PM3/24/2023 1:10:47 PMCorrect.
10P1CompletedCompleted3/13/2023 6:00:00 AM3/13/2023 8:00:00 AM3/13/2023 10:00:00 AM3/13/2023 8:00:00 AM3/13/2023 10:00:00 AMP1's and P2's now working correctly. Modified formula.
11P2CompletedCompleted2/12/2023 11:20:18 AM2/12/2023 3:20:18 PM2/12/2023 7:20:18 PM2/12/2023 3:20:18 PM2/12/2023 7:20:18 PMP1's and P2's now working correctly. Modified formula.
12P1CompletedCompleted2/18/2023 12:13:01 AM2/18/2023 2:13:01 AM2/18/2023 4:13:01 AM2/18/2023 2:13:01 AM2/18/2023 4:13:01 AMP1's and P2's now working correctly. Modified formula.
13P2OverdueIn Progress2/21/2023 10:43:55 AM2/21/2023 2:43:55 PM2/21/2023 6:43:55 PM2/21/2023 2:43:55 PM2/21/2023 6:43:55 PMP1's and P2's now working correctly. Modified formula.
14
15For P3 (Column A)
16Column 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.
17Column 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.
18
19For P4 (Column A)
20Column 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.
21Column 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.
22
23For P1 (Column A)
24Column E needs to be 2 hours after the WO was issued regardless of whether it is in business hours or outside business hours or public holidays, weekends etc.
25Column E needs to be 4 hours after the WO was issued regardless of whether it is in business hours or outside business hours or public holidays, weekends etc.
26
27For P2 (Column A)
28Column E needs to be 4 hours after the WO was issued regardless of whether it is in business hours or outside business hours or public holidays, weekends etc.
29Column E needs to be 8 hours after the WO was issued regardless of whether it is in business hours or outside business hours or public holidays, weekends etc.
Export
Cell Formulas
RangeFormula
E2:E13E2=IF(A2="P2",D2+(4/24),IF(A2="P1",D2+(2/24),IF(OR($A2="P4",$A2="P3"),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)<=TIME(16,0,0),MOD($D2,1)>=TIME(8,0,0)),MOD($D2,1),TIME(16,0,0))),"")))
F2:F13F2=IF(A2="P2",D2+(8/24),IF(A2="P1",D2+(4/24),WORKDAY.INTL($D2,IF($A2="P4",13,6),1,'Public Holidays'!$B$1:$B$12)+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))))


Updated.xlsx
AB
1Day after New Year’s Day1/01/2023
23/01/2023
32/01/2023
4Waitangi Day6/02/2023
5Good Friday7/04/2023
6Easter Monday10/04/2023
7Anzac Day25/04/2023
8King’s Birthday5/06/2023
9Matariki14/07/2023
10Labour Day23/10/2023
11Christmas Day25/12/2023
12Boxing Day26/12/2023
Public Holidays
 
Upvote 0
Yeah, that was the issue i mentioned, but then promptly forgot. Let me look into it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
sumifs using date and time values
Replies
9
Views
322
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
272
Cubist
Cubist
S
  • Solved
Networkdays formula issue
Replies
3
Views
272
Joe4
Joe4
K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
88
kumara_faith
K
Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
272
Artik
Artik

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

Share this page

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