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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
i'l have to work on it later, i'm tired though, sorry.
 
Upvote 0
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

B
  • Solved
Group number of purchased SKUs by counting customer
2
Replies
16
Views
292
shaowu459
shaowu459
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
280
amphead
A
M
  • Solved
Formula Help.
Replies
3
Views
128
Fluff
Fluff
R
  • Solved
Import Data Date Format Wrong
Replies
9
Views
337
Georgiboy
Georgiboy
RichTom44
  • Question
How to highlight a certain time range
Replies
3
Views
323
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,943
Messages
6,127,826
Members
449,411
Latest member
adunn_23

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