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
Okay regarding the P1 and P2 updating Columns E & F.

I'll have think about 6 AM. But, to clarify.. If recieved before 8 am it should be treated as if received the day prior (it should make no difference regarding day of week or holiday as that is already taken care of). Is that correct?
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Okay so for column E, P1 is +2 hours, P2 is +4 hours?
For colum F, P1 is +4 hours, p2 is +8 hours?
 
Upvote 0
so, the adds will take you to the next day. Do you want the overlap to the next day to be added to 8 AM?

SO, for a P2, column E calucation, if column D has 15:00, the value for +4 will be the next day at 11:00?
 
Upvote 0
what if a p1 or P2 is received on a weekend/holiday? Before 6:00? After 16:00?

This is really looking like spaghetti code. I may readdress everything when you give me all the requirements.

So... Is this ALL the requirements regarding columns E and F? Are there other values in Column A that can mess this up more?
 
Upvote 0
awoohaw said:
Okay so for column E, P1 is +2 hours, P2 is +4 hours?
For colum F, P1 is +4 hours, p2 is +8 hours?
Click to expand...
Hi. Yes that is correct and that is regardless of when the WO was issued. So straight up add the times to the date/time in column D for E and F for both P1's and P2's.
 
Upvote 0
awoohaw said:
so, the adds will take you to the next day. Do you want the overlap to the next day to be added to 8 AM?

SO, for a P2, column E calucation, if column D has 15:00, the value for +4 will be the next day at 11:00?
Click to expand...
Hi. For P1's and P2's is a straight up addition and can land at any time. P1's and P2's should not consider any restrictions on business times, public holidays and weekends. They can land at any time and any date.
 
Upvote 0
awoohaw said:
what if a p1 or P2 is received on a weekend/holiday? Before 6:00? After 16:00?

This is really looking like spaghetti code. I may readdress everything when you give me all the requirements.

So... Is this ALL the requirements regarding columns E and F? Are there other values in Column A that can mess this up more?
Click to expand...
I hear you re spaghetti code. It's what I see when I look at my feeble attempt.
For P1's and P2's it s straight up addition of time to column D. No consideration should be given to any nuances like business hours, public holidays, weekends etc.
Only P3's and P4's need to consider the business hours, weekends and public holidays.
So, for P3's and P4's, if column D originates on a weekend or public holiday, the full business days must be granted for response and rectification and ends at 4pm for respective dates.
If D originates before 8am on a working day, the first full business day starts on that day for P3's and P4's, provided it's not a weekend or public holiday and ends at 4pm for respective dates.
If D originates after 4pm the first full business day starts the following day, provided it's not a weekend or public holiday and ends at 4pm for respective dates.
If D originates during business hours, the hours are added for the respective response and rectification and will land on the calculated date. The time will be the same as the originating time.

I hope this makes sense but happy to clarify if not.
 
Upvote 0
P1
1678994584135.png



P2
1678994643411.png


P3
1678994682094.png


P4
1678994711933.png
 
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,183
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