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.



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

 

[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")