Interval using time configuration

[Bugas]

I'm trying to identify which employees work at night. Between 6:30 and 23:00 is day time. Outside is night time (just by getting part of it). Without using VBA is there any formula in excel to this?

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee name[/TD]
[TD]Work Time[/TD]
[TD]Identification[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Andrew[/TD]
[TD]04:00 - 06:00[/TD]
[TD]Night Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bernie[/TD]
[TD]05:00 - 09:00[/TD]
[TD]Night Time[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Charles[/TD]
[TD]04:00 - 12:00[/TD]
[TD]Night Time[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Diana[/TD]
[TD]10:00 - 18:00[/TD]
[TD]Day Time[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Earl[/TD]
[TD]20:00 - 02:00[/TD]
[TD]Night Time[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Fergie[/TD]
[TD]22:30 - 08:00[/TD]
[TD]Night Time[/TD]
[/TR]
</tbody>[/TABLE]

 

[NeonRedSharpie]

=IF(AND(TIME(LEFT(B2,2),MID(B2,4,2),0)>0.270833,TIME(LEFT(B2,2),MID(B2,4,2),0)<0.958333,TIME(MID(B2,9,2),MID(B2,13,2),0)<0.958333,TIME(MID(B2,9,2),MID(B2,13,2),0)-TIME(LEFT(B2,2),MID(B2,4,2),0) > 0),"Day Time", "Night Time")

It's a bit of a pain, but it should work assuming your times are input in 24 hour format with leading zeros and the spaces around the dashes (as per your sample data). I like time because it's just a number from 0 to 1 at the end of the day. B2 is your time column.

The code makes sure (in order) that you start after 6:30, start before 23:00, you end before 23:00, and don't cross over the midnight threshold.

 

[Trouttrap2]

Cant' say this is any better. It might be simpler if you placed your time in separate columns. But the way it's set up now, here is another suggestion, but yields the same result.
=IF(OR(TIMEVALUE(LEFT(B2,4))<TIMEVALUE("06:30"),TIMEVALUE(RIGHT(B2,4))>TIMEVALUE("23:00")),"Night Time",IF(TIMEVALUE(RIGHT(B2,5))<TIMEVALUE(LEFT(B2,4)),"Night Time", "Day Time"))

 

[Trouttrap2]

This is what I intednded to provide
=IF(OR(TIMEVALUE(LEFT(B2,4))<TIMEVALUE("06:30"),timevalue(right(b2,4))>TIMEVALUE("23:00")),"Night Time",IF(TIMEVALUE(RIGHT(B2,5))<timevalue(left(b2,4)),"night ?Day="" Time?,="" Time?))

<TIMEVALUE(LEFT(B2,4)),"Night Time", "Day Time"))

Ok, I can't seem to copy the full formula over in once piece. Sorry about that.</timevalue(left(b2,4)),"night></TIMEVALUE("06:30"),timevalue(right(b2,4))>

 

[shg]

Add a space after each < so it isn't interpreted as the start of an HTML tag.

 

[Bugas]

=IF(AND(TIME(LEFT(B2,2),MID(B2,4,2),0)>0.270833,TIME(LEFT(B2,2),MID(B2,4,2),0)<0.958333,TIME(MID(B2,9,2),MID(B2,13,2),0)<0.958333,TIME(MID(B2,9,2),MID(B2,13,2),0)-TIME(LEFT(B2,2),MID(B2,4,2),0) > 0),"Day Time", "Night Time")

It's a bit of a pain, but it should work assuming your times are input in 24 hour format with leading zeros and the spaces around the dashes (as per your sample data). I like time because it's just a number from 0 to 1 at the end of the day. B2 is your time column.

The code makes sure (in order) that you start after 6:30, start before 23:00, you end before 23:00, and don't cross over the midnight threshold.

Thanks, it works perfect!

However start time and end time are in different collumns. I'm trying to change the formula but gives me error. Column B is start time, Column C is end time. When I identify them, they are on Column D. Is there any chance for you to help me?

 

[Trouttrap2]

Not to cheat Bugas out of a reply, but you can try this if you want:
=IF(OR(B2< TIMEVALUE("06:30"),C2> TIMEVALUE("23:00")),"Night Time",IF(C2< B2,"Night Time", "Day Time"))

Note: remove the blank space after each > and < symbol in the formula.

 

[Bugas]

Not to cheat Bugas out of a reply, but you can try this if you want:
=IF(OR(B2< TIMEVALUE("06:30"),C2> TIMEVALUE("23:00")),"Night Time",IF(C2< B2,"Night Time", "Day Time"))

Note: remove the blank space after each > and < symbol in the formula.

That's it! Thanks