IF Formula with date and time parameters

[nmckever]

I have an excel spreadsheet, I need to create a formula that will indicate who is responsible for each request given the parameters that are set at the bottom. The responsibility will either be with IND or SCH depending on the date and time that the request was submitted.

Today's Date	MU	Agent Name	Activity	Request Date	Submitted Date	Submitted Time	Day	Turnaround	Responsible
11/16/2016	1234 Test Lvl 1	Mouse, Mickey	VTO	11/18/2016	11/14/2016	9:35 AM	Mon
11/16/2016	1234 Test Lvl 1	Mouse, Mickey	PTO Excused	11/25/2016	11/14/2016	4:29 PM	Mon
11/16/2016	2 Tester Villiage	Way, Ronnie	PTO Excused	11/17/2016	11/15/2016	3:42 PM	Tue
11/16/2016	2 Tester Villiage	Dickson, Mouse	Cancel OT- WFM	11/18/2016	11/15/2016	6:02 AM	Tue
11/16/2016	200 Orlion Way	Street, Joe	PTO Excused	11/25/2016	11/15/2016	10:07 PM	Tue
11/16/2016	Sender Que	Jackson, Michael	VTO	11/16/2016	11/16/2016	8:53 AM	Wed
11/16/2016	12345 Test Lvl 2	Jackson, Janet	VTO	11/25/2016	11/19/2016	8:53 AM	Sat
11/16/2016	123 Test lvl 3	Jackson, Tito	VTO	11/30/2016	11/20/2016	9:30 AM	Sun
11/16/2016	456 Test lvl 4	Mouse, Minnie	VTO	11/23/2016	11/21/2016	7:50 AM	Mon
11/16/2016	789 Test lvl 5	Duck, Donald	Specialist OT	11/18/2016	11/16/2016	5:30 PM	Wed
Total		10
Parameters
If the request date is the same as the submitted date regardless of the day - IND is responsible
Anything submitted after 4pm - IND is responsible
Anything submitted before 8am - IND is responsible
Anything submitted on Sat and Sun - IND is responsible
Anything Submitted between 8a-4pm Mon - Fri - SCH is responsible

<colgroup><col><col><col><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Gerald Higgins]

As you haven't told us your column references, I'll have to generalise, but this might get you going...

=if(or(([request date]=[submitted date]),([submitted time]> 16/24),([submitted time]< 8/24),[day]="Sat",[day]="Sun"),"IND","SCH")

Replace the bits in square brackets with the actual cell references.
Assumptions
DAY is text, not number formatted to appear as day.
1st parameter takes precedence over final parameter.

 

[nmckever]

The 'day' column is a formula formatted to reflect the actual day. Is there a way I can remove the formula and it still reflect the information that I am trying to recover?

 

[Gerald Higgins]

OK, then you can probably embed a couple of instances of the WEEKDAY function to deal with that.

For example
=if(or(([request date]=[submitted date]),([submitted time]> 16/24),([submitted time]< 8/24),weekday([day],1)=1,weekday([day],1)=7),"IND","SCH")