Need Formula for Timeband Name in Answer Column as per Below Details

[JeysJeys]

[TABLE="width: 623"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Telecast Date[/TD]
[TD]Telecast Day[/TD]
[TD]Day[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Time Zone[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD]1-Aug-19[/TD]
[TD]Thursday[/TD]
[TD]Weekday[/TD]
[TD="align: right"]7:00:00[/TD]
[TD="align: right"]12:00:00[/TD]
[TD]Morning[/TD]
[TD]Prime Time[/TD]
[/TR]
[TR]
[TD]2-Aug-19[/TD]
[TD]Friday[/TD]
[TD]Weekday[/TD]
[TD="align: right"]17:00:00[/TD]
[TD="align: right"]21:00:00[/TD]
[TD]Evening[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3-Aug-19[/TD]
[TD]Saturday[/TD]
[TD]Weekend[/TD]
[TD="align: right"]13:00:00[/TD]
[TD="align: right"]16:00:00[/TD]
[TD]ROS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4-Aug-19[/TD]
[TD]Sunday[/TD]
[TD]Weekend[/TD]
[TD="align: right"]18:00:00[/TD]
[TD="align: right"]23:00:00[/TD]
[TD]Evening[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5-Aug-19[/TD]
[TD]Monday[/TD]
[TD]Weekday[/TD]
[TD="align: right"]8:00:00[/TD]
[TD="align: right"]11:00:00[/TD]
[TD]Morning[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6-Aug-19[/TD]
[TD]Tuesday[/TD]
[TD]Weekend[/TD]
[TD="align: right"]18:00:00[/TD]
[TD="align: right"]21:00:00[/TD]
[TD]Evening[/TD]
[TD] [/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Weekday[/TD]
[TD]0700 AM -1300 PM[/TD]
[TD]Prime Time[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]1700 PM - 2100 PM[/TD]
[TD]Prime Time[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Rest Timeband[/TD]
[TD]NON Prime Time[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Weekend[/TD]
[TD]All Timeband[/TD]
[TD]Non Prime Time[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hey just a couple of questions,
what answer do you expect to see when the start time is within prime hours but the end time isn't? and vice versa when the start time is not in prime time but the end time is? (for weekdays)


Try this in G2: Assuming data is in columns A through F with header row in row 1.

IF(AND(AND(C2="Weekday",OR(AND(D2>=7/24,D2<=13/24),AND(D2>=17/24,D2<=21/24))),OR(AND(E2<=13/24,E2>=7/24),AND(E2<=21/24,E2>=17/24))),"Prime Time","Non Prime Time")