Excel - Comparing specific date and times

[HeyYoWL]

I'm trying to create a tracker that contains a column to check if we've met SLA.

Column A - Date Received
Column B - Time Received
Column C - Date and Time Combined (A2+B2)
Column D - Date Resolved
Column E - Time Resolved
Column F - Date and Time Resolved (D2+E2)

Essentially, what I need to check is:

If the date received (Column A) and time received (Column B) was after 12 PM, the date resolved (Column D) should be the next business day by 12 PM (Column E).

Ex: Received request at 2pm on Monday July 31st, resolved Tuesday August 1st at 11 AM -> SLA Met

Ex: Received request at 2pm on Monday July 31st, resolved Tuesday August 1st at 3 PM -> SLA Not Met

I can't figure out how to combine the date and time data to plug into my formula. I've tried combining the date and time as you can see above:

=IF(AND(O2="Team Inbox", C2 >= TIME(12,0,0)), NETWORKDAYS(A2,E2,Holidays[2023Holidays])<=2, G2 <= TIME(12,0,0)), "Yes", "No")

Column O = Specific Inbox [2023Holidays] = Table of holidays for business

Any help would be appreciated!

 

[JamesCanale]

So what happens if the thing is received before noon on a given day?

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date Received	time ereceived	Date/time received	Date Resolved	Time Resolved	Date/time Resolved	Good?
2	7/31/2023	2:00 PM	Mon 7/31/2023 14:00	8/1/2023	11:00 AM	Tue 8/1/2023 11:00	SLA Met								Sunday, January 1, 2023
3	7/31/2023	2:00 PM	Mon 7/31/2023 14:00	8/2/2023	3:00 PM	Wed 8/2/2023 15:00	SLA Not Met								Monday, January 16, 2023
4	8/2/2023	2:00 PM	Wed 8/2/2023 14:00	8/3/2023	1:00 PM	Thu 8/3/2023 13:00	SLA Not Met								Monday, February 20, 2023
5	8/3/2023	10:00 AM	Thu 8/3/2023 10:00	8/4/2023	2:00 PM	Fri 8/4/2023 14:00	What if it's received before noon?								Monday, May 29, 2023
6	8/4/2023	11:00 AM	Fri 8/4/2023 11:00	8/4/2023	3:00 PM	Fri 8/4/2023 15:00	What if it's received before noon?								Monday, June 19, 2023
7	2/17/2023	2:00 PM	Fri 2/17/2023 14:00	2/21/2023	11:00 AM	Tue 2/21/2023 11:00	SLA Met								Tuesday, July 4, 2023
8	11/22/2023	3:00 PM	Wed 11/22/2023 15:00	11/27/2023	10:00 AM	Mon 11/27/2023 10:00	SLA Met								Monday, September 4, 2023
9															Thursday, October 12, 2023
10															Monday, November 6, 2023
11															Saturday, November 11, 2023
12															Thursday, November 23, 2023
13															Friday, November 24, 2023
14															Monday, December 25, 2023
Sheet14

Cell Formulas
Range	Formula
F2:F8,C2:C8	F2	=D2+E2
G2:G8	G2	=IF(B2>0.5,IF(AND(NETWORKDAYS.INTL(A2,D2,1,$O$2:$O$14)<=2,E2<=0.5),"SLA Met","SLA Not Met"),"What if it's received before noon?")

 

[HeyYoWL]

So what happens if the thing is received before noon on a given day?

Hi James,

This is tracking emails to that specific inbox, Column O is tracking which inbox it's going to. So if we receive an email before noon, it's due by the end of the day. I already have the other portions of that done, it was just this last bit I needed help with, didn't want to make it longer than necessary but I can. Here's the full scope of the situation:

There are 2 inboxes, defined in Column O by dropdown options for "Team Inbox" or Other "Team Inbox"

- Other Team Inbox, SLA is 2 business days - formula working (green font)
- Team Inbox, if email received before 12 PM, must resolve by same day - formula working (blue font)
- Team Inbox, if email received after 12 PM, must resolve by 12 pm the next business day - formula not working (orange font)

=IF(OR(AND(O2="Other Team Inbox", NETWORKDAYS(A2,D2,Holidays[2023Holidays])<=3), (AND(O2="Team Inbox", B2 <= TIME(12,0,0), NETWORKDAYS(A2,E2,Holidays[2023Holidays])<=1)), AND(O2="Other Team Inbox", C2 >= TIME(12,0,0), F2 <= TIME(12,0,0), NETWORKDAYS(A2,E2,Holidays[2023Holidays])<=2)), "Yes", "No")

 

[HeyYoWL]

I gave this a try and it runs into the same issue I had with an earlier formula, in that it only takes into account the time and not the date as well. If you look at that 1st row of data, if you leave the date and time received as is, but change D2 to 7/31 and E2 to 9 PM, it says "SLA Not Met". So even though it's within SLA, because it's not looking at the day as well it shows Not Met.

 

[JamesCanale]

RIght you are...

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date Received	time ereceived	Date/time received	Date Resolved	Time Resolved	Date/time Resolved	Good?
2	7/31/2023	2:00 PM	Mon 7/31/2023 14:00	7/31/2023	9:00 PM	Mon 7/31/2023 21:00	SLA Met								Sunday, January 1, 2023
3	7/31/2023	2:00 PM	Mon 7/31/2023 14:00	8/2/2023	3:00 PM	Wed 8/2/2023 15:00	SLA Not Met								Monday, January 16, 2023
4	8/2/2023	2:00 PM	Wed 8/2/2023 14:00	8/3/2023	11:00 AM	Thu 8/3/2023 11:00	SLA Met								Monday, February 20, 2023
5	8/3/2023	10:00 AM	Thu 8/3/2023 10:00	8/4/2023	2:00 PM	Fri 8/4/2023 14:00	SLA Not Met								Monday, May 29, 2023
6	8/4/2023	11:00 AM	Fri 8/4/2023 11:00	8/4/2023	3:00 PM	Fri 8/4/2023 15:00	SLA Met								Monday, June 19, 2023
7	2/17/2023	2:00 PM	Fri 2/17/2023 14:00	2/21/2023	11:00 AM	Tue 2/21/2023 11:00	SLA Met								Tuesday, July 4, 2023
8	11/22/2023	3:00 PM	Wed 11/22/2023 15:00	11/27/2023	10:00 AM	Mon 11/27/2023 10:00	SLA Met								Monday, September 4, 2023
9															Thursday, October 12, 2023
10															Monday, November 6, 2023
11															Saturday, November 11, 2023
12															Thursday, November 23, 2023
13															Friday, November 24, 2023
14															Monday, December 25, 2023
Sheet14

Cell Formulas
Range	Formula
F2:F8,C2:C8	F2	=D2+E2
G2:G8	G2	=IF(NETWORKDAYS.INTL(A2,D2,1,$O$2:$O$14)+E2<=2.5,"SLA Met","SLA Not Met")

 

[HeyYoWL]

RIght you are...

We're getting somewhere! That works, but when I plug into my existing formula, emails sent before 12 PM aren't working now. Anything sent before 12 PM should be resolved on the same business day.

Ex: A2=8/1/23, B2=11 AM, D2= 8/1/23, E2 = 11:59 PM -> SLA Met
Ex: A2=8/1/23, B2=11 AM, D2= 8/2/23, E2 = 10 AM -> SLA Not Met

Right now I have:

=IF(OR(AND(O2="Team Inbox", B2 <= TIME(12,0,0), NETWORKDAYS(A2,D2,Holidays[2023 Holidays])<=1), AND(M2="PL Cares", NETWORKDAYS(A2,D2,Holidays[F''23])+E2<=2.5)), "Yes", "No")

Can we combine into one formula:

- If received before 12 PM, response = same business day
- If received after 12 PM, response = next business day before 12 PM

Right now the formula calculating only if it was resolved by 12 PM the next business day.

 

[JamesCanale]

We will get there.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date Received	time ereceived	Date/time received	Date Resolved	Time Resolved	Date/time Resolved	Good?
2	7/31/2023	2:00 PM	Mon 7/31/2023 14:00	7/31/2023	9:00 PM	Mon 7/31/2023 21:00	SLA Met								Sunday, January 1, 2023
3	7/31/2023	2:00 PM	Mon 7/31/2023 14:00	8/2/2023	3:00 PM	Wed 8/2/2023 15:00	SLA Not Met								Monday, January 16, 2023
4	8/2/2023	2:00 PM	Wed 8/2/2023 14:00	8/3/2023	11:00 AM	Thu 8/3/2023 11:00	SLA Met								Monday, February 20, 2023
5	8/3/2023	10:00 AM	Thu 8/3/2023 10:00	8/4/2023	10:00 AM	Fri 8/4/2023 10:00	SLA Not Met								Monday, May 29, 2023
6	8/4/2023	11:00 AM	Fri 8/4/2023 11:00	8/4/2023	3:00 PM	Fri 8/4/2023 15:00	SLA Met								Monday, June 19, 2023
7	2/17/2023	2:00 PM	Fri 2/17/2023 14:00	2/21/2023	11:00 AM	Tue 2/21/2023 11:00	SLA Met								Tuesday, July 4, 2023
8	11/22/2023	3:00 PM	Wed 11/22/2023 15:00	11/27/2023	10:00 AM	Mon 11/27/2023 10:00	SLA Met								Monday, September 4, 2023
9															Thursday, October 12, 2023
10															Monday, November 6, 2023
11															Saturday, November 11, 2023
12															Thursday, November 23, 2023
13															Friday, November 24, 2023
14															Monday, December 25, 2023
Sheet14

Cell Formulas
Range	Formula
F2:F8,C2:C8	F2	=D2+E2
G2:G8	G2	=IF(B2>=0.5,IF(NETWORKDAYS.INTL(A2,D2,1,$O$2:$O$14)+E2<=2.5,"SLA Met","SLA Not Met"),IF(NETWORKDAYS.INTL(A2,D2,1,$O$2:$O$14)=1,"SLA Met","SLA Not Met"))

 

[HeyYoWL]

Thanks I'll give this a try!

 

[HeyYoWL]

Great, it works, thanks a bunch! Now just because I like to learn for next time, would you mind explaining how this works? I get most of it but what about:

+E2<=2.5

and =1

Makes all this work?

 

[JamesCanale]

networkdays between two adjacent dates will result in a 2. the time in Column E turns into a fraction of a day. So noon is 0.5. adding that to the networkdays and you are looking for a number that is less than or equal to 2.5 - noon on the second day. So if something comes in at 1pm, and gets done by 5 on the same day, it'll come to 1+17/24 which is less than or equal to 2.5 too.

The =1 means that the networkdays are the same day.