Interval using time configuration

Bugas

New Member
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:

 A B C 1 Employee name Work Time Identification 2 Andrew 04:00 - 06:00 Night Time 3 Bernie 05:00 - 09:00 Night Time 4 Charles 04:00 - 12:00 Night Time 5 Diana 10:00 - 18:00 Day Time 6 Earl 20:00 - 02:00 Night Time 7 Fergie 22:30 - 08:00 Night Time

<tbody>
</tbody>

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Code:
``=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.

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

Last edited:
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))>

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

Code:
``=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?

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.

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

Replies
15
Views
396
Replies
5
Views
311
Replies
3
Views
262
Replies
1
Views
114
Replies
41
Views
968

1,203,241
Messages
6,054,330
Members
444,717
Latest member
melindanegron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back