Need Help with Formula to Determine if Date/Time is After Hours

[ndlongo]

I have a Date in D2, a time in E2 as well as a cell with both the date and time (same as D2/E2 just combined) in I2. I need to return a value of 1 if the time is considered after hours. After hours for me would be before 8:30 am on weekdays, after 18:30 on weekdays and all weekends. I have tried a few different ways to accomplish this and I never get the formula to work for all scenarios.

 

[Trouttrap2]

Does this work for you
=IF(OR(WEEKDAY(D1)=1,WEEKDAY(D1)=7),1,IF(OR(E1<8.5/24,E1>18.5/24),1,""))

 

[ndlongo]

Does this work for you
=IF(OR(WEEKDAY(D1)=1,WEEKDAY(D1)=7),1,IF(OR(E1<8.5/24,E1>18.5/24),1,""))

Unfortunately it did not, if it helps, my time is formatted as hh:mm:ss

 

[Trouttrap2]

The time format should not matter. Can you post some of your data? Are all your dates and times real dates and times?
This formula works for me =IF(OR(WEEKDAY(D2)=1,WEEKDAY(D2)=7),1,IF(OR(E2<8.5/24,E2>18.5/24),1,""))

[TABLE="width: 273"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/2/2014[/TD]
[TD]08:15:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/3/2014[/TD]
[TD]18:30:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/4/2014[/TD]
[TD]18:31:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3/5/2014[/TD]
[TD]08:45:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3/6/2014[/TD]
[TD]08:31:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3/7/2014[/TD]
[TD]18:30:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3/8/2014[/TD]
[TD]18:31:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3/9/2014[/TD]
[TD]08:30:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3/10/2014[/TD]
[TD]08:15:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]3/11/2014[/TD]
[TD]18:30:00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]3/12/2014[/TD]
[TD]18:31:00[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3/13/2014[/TD]
[TD]08:30:00[/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col></colgroup>[/TABLE]

 

[ndlongo]

The time format should not matter. Can you post some of your data? Are all your dates and times real dates and times?
This formula works for me =IF(OR(WEEKDAY(D2)=1,WEEKDAY(D2)=7),1,IF(OR(E2<8.5/24,E2>18.5/24),1,""))

I used =INT(I2) for the Date column (D), =TIME(HOUR(I2), MINUTE(I2), SECOND(I2)) for Military Time column (E), you can ignore column H.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Date (D)
[/TD]
[TD]Military Time of Day (E)
[/TD]
[TD]Hour of Day (F)
[/TD]
[TD]After Hours (G)
[/TD]
[TD]Abondoned (H)
[/TD]
[TD]Call Date/Time (I)
[/TD]
[/TR]
[TR]
[TD]12/29/13
[/TD]
[TD]2:50:37
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]12/29/2013 02:50:37 AM
[/TD]
[/TR]
[TR]
[TD]12/30/13
[/TD]
[TD]18:57:08
[/TD]
[TD]18
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]12/30/2013 18:57:08 PM
[/TD]
[/TR]
[TR]
[TD]12/31/13
[/TD]
[TD]12:05:37
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]12/31/2013 12:05:37 PM
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Trouttrap2]

I am not able to duplicate the problem. What results are you getting with my formula? Is my example in previous post giving you desired results?

 

[Trouttrap2]

How about this instead? Does this work?
=IF(OR(WEEKDAY(I2)=1,WEEKDAY(I2)=7),1,IF(OR(MOD(I2,1)<8.5/24,MOD(I2,1)>18.5/24),1,""))

This reads as:
If its a Sun or Mon, give it a 1
If its a weekday then - earlier than 0830 and later than 1830 give it a 1

 

[ndlongo]

How about this instead? Does this work?
=IF(OR(WEEKDAY(I2)=1,WEEKDAY(I2)=7),1,IF(OR(MOD(I2,1)<8.5/24,MOD(I2,1)>18.5/24),1,""))

This reads as:
If its a Sun or Mon, give it a 1
If its a weekday then - earlier than 0830 and later than 1830 give it a 1

This worked - thank you!!!