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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
you need to provide the public holidays worksheet too, please?, never mind!
 
Upvote 0
what are the dates in row1 and row3? Is New Years 3 consecutive holidays?
 
Upvote 0
@Stelio , I am assuming your weekends are Saturday and Sunday?
Where the ##th day is a regular workday... by ## Business days do you mean 80 hours or that the ##th day ends at 4 pm regardless of start time? Or should we just consider 80 hours regardless? Can you clarify? My impression is you want the calcuation to result in a workday between the hours of 800 and 1600? If that is wrong please provide a different explanation of what you want.
 
Upvote 0
@Stelio, here is my attempt at it. I do have some differences between your expectations and what I have calculated on two records. Please review and advise:

mr excel questions 15.xlsm
CDEFGHIJK
46Rectification StatusWork Order Issued Date TimeResponse Due DateCompletion Due Date TimeNew Response Due DateNew Completion Due Date
47In Progress2023-02-03 14:10:472023-02-17 14:10:472023-02-22 14:10:472023-02-20 14:10:472023-02-23 14:10:47Day after New Year’s Day2023-01-01 00:00
48In Progress2023-02-03 14:11:202023-02-17 14:11:202023-02-22 14:11:202023-02-20 14:11:202023-02-23 14:11:202023-01-03 00:00
49In Progress2023-02-07 17:41:402023-02-10 16:00:002023-02-15 16:00:002023-02-10 16:00:002023-02-15 16:00:002023-01-02 00:00
50Completed2023-02-11 11:40:322023-02-15 16:00:002023-02-20 16:00:002023-02-15 16:00:002023-02-20 16:00:00Waitangi Day2023-02-06 00:00
51Completed2023-02-12 10:03:382023-02-15 16:00:002023-02-20 16:00:002023-02-15 16:00:002023-02-20 16:00:00Good Friday2023-04-07 00:00
52Completed2023-02-12 11:20:182023-02-15 16:00:002023-02-20 16:00:002023-02-15 16:00:002023-02-20 16:00:00Easter Monday2023-04-10 00:00
53Completed2023-02-18 00:13:012023-03-03 16:00:002023-03-08 16:00:002023-03-03 16:00:002023-03-08 16:00:00Anzac Day2023-04-25 00:00
54In Progress2023-02-21 10:43:552023-02-24 10:43:552023-03-01 10:43:552023-02-24 10:43:552023-03-01 10:43:55King’s Birthday2023-06-05 00:00
55In Progress2023-02-23 09:47:312023-03-09 09:47:312023-03-14 09:47:312023-03-09 09:47:312023-03-14 09:47:31Matariki2023-07-14 00:00
56In Progress2023-02-23 11:00:492023-03-09 11:00:492023-03-14 11:00:492023-03-09 11:00:492023-03-14 11:00:49Labour Day2023-10-23 00:00
57In Progress2023-02-23 11:02:272023-03-09 11:02:272023-03-14 11:02:272023-03-09 11:02:272023-03-14 11:02:27Christmas Day2023-12-25 00:00
58In Progress2023-02-23 15:12:492023-03-09 15:12:492023-03-14 15:12:492023-03-09 15:12:492023-03-14 15:12:49Boxing Day2023-12-26 00:00
59In Progress2023-02-24 14:08:562023-03-10 14:08:562023-03-15 14:08:562023-03-10 14:08:562023-03-15 14:08:56
60In Progress2023-02-24 15:31:342023-03-10 15:31:342023-03-15 15:31:342023-03-10 15:31:342023-03-15 15:31:34
61In Progress2023-02-24 15:46:372023-03-10 15:46:372023-03-15 15:46:372023-03-10 15:46:372023-03-15 15:46:37
62Completed2023-02-25 05:31:512023-03-01 16:00:002023-03-06 16:00:002023-03-01 16:00:002023-03-06 16:00:00
63Completed2023-02-25 07:34:532023-03-01 16:00:002023-03-06 16:00:002023-03-01 16:00:002023-03-06 16:00:00
64Completed2023-02-26 05:34:342023-03-01 16:00:002023-03-06 16:00:002023-03-01 16:00:002023-03-06 16:00:00
65Completed2023-02-26 06:51:512023-03-01 16:00:002023-03-06 16:00:002023-03-01 16:00:002023-03-06 16:00:00
66In Progress2023-02-27 16:06:002023-03-13 16:00:002023-03-16 16:00:002023-03-13 16:00:002023-03-16 16:00:00
67In Progress2023-02-27 16:30:412023-03-13 16:00:002023-03-16 16:00:002023-03-13 16:00:002023-03-16 16:00:00
68In Progress2023-02-27 16:48:002023-03-13 16:00:002023-03-16 16:00:002023-03-13 16:00:002023-03-16 16:00:00
69In Progress2023-02-28 14:40:332023-03-14 14:40:332023-03-17 14:40:332023-03-14 14:40:332023-03-17 14:40:33
70In Progress2023-02-28 18:30:472023-03-14 16:00:002023-03-17 16:00:002023-03-14 16:00:002023-03-17 16:00:00
71In Progress2023-02-28 18:37:142023-03-14 16:00:002023-03-17 16:00:002023-03-14 16:00:002023-03-17 16:00:00
72In Progress2023-02-28 19:58:592023-03-03 16:00:002023-03-08 16:00:002023-03-03 16:00:002023-03-08 16:00:00
Export
Cell Formulas
RangeFormula
G47G47=WORKDAY.INTL($D47,IF($A47="P4",10,3),1,PublicHolidays) + IF(OR(MOD($E47,1)<=18/24,MOD($E47,1)>=8/24),MOD($E47,1),18/24)
H47:H72H47=WORKDAY.INTL($D47,IF($A47="P4",13,6),1,PublicHolidays) + IF(OR(MOD($E47,1)<=18/24,MOD($E47,1)>=8/24),MOD($E47,1),18/24)
G48:G72G48=WORKDAY.INTL(D48,IF(A48="P4",10,3),1,PublicHolidays) + IF(OR(MOD(E48,1)<=18/24,MOD(E48,1)>=8/24),MOD(E48,1),18/24)
Named Ranges
NameRefers ToCells
PublicHolidays=Export!$K$47:$K$58G47:H72
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H47:H71Expression=ROUND(F47,6)<>ROUND(H47,6)textNO
F47:F72Expression=ROUND(F47,6)<>ROUND(H47,6)textNO
E47:E72Expression=ROUND(E47,6)<>ROUND(G47,6)textNO
G47:G72Expression=ROUND(E47,6)<>ROUND(G47,6)textNO
 
Upvote 0
awoohaw said:
@Stelio , I am assuming your weekends are Saturday and Sunday?
Where the ##th day is a regular workday... by ## Business days do you mean 80 hours or that the ##th day ends at 4 pm regardless of start time? Or should we just consider 80 hours regardless? Can you clarify? My impression is you want the calcuation to result in a workday between the hours of 800 and 1600? If that is wrong please provide a different explanation of what you want.
Click to expand...
Hi Awoohaw,

Thanks for looking at this.
Weekends are Saturdays and Sundays only.
Public holiday list changes from year to year and I will update that list every year.

The business day ends at 4pm. So let’s say a work order is issued on a Monday as a P3 at 8:03am as a P3. The response time should be 3 full business days so the result should be Thu 8:03am. This calculates it as 24 full business hours.
But if the work order is issued on Monday at 4:01pm the response result should be Thu 4pm, calculated as 3 full business days.

It may help to mention that I need the existing formulas tweaked to achieve the result as they forem part of larger formulas…if possible.
 
Upvote 0
okay, I think those are the assumptions I loaded into the formulas above.
 
Upvote 0
also, note my date/time format is different from yours. I hope it does not confuse you too much.
 
Upvote 0
@Stelio , I just had a thought What happens when the work order arrives between 800 and 1600 on a weekend day?
 
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,180
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