Whether an event was within working hours

[jlaff]

Hi, I am trying to determine whether an event happened withing working hours (Mon-Fri, 9am - 5pm). I am only interested in the initial time presentation (it is research looking at when patients with heart attacks presented to hospital). I have all of their dates and times of presentations below - I just want to know if the time of presentation was 'in-hours' or 'out-of-hours'. I will manually edit out the public holidays afterwards but I have 7k patients so am looking for a formula. Any help much appreciated!

 

[MARK858]

Maybe

=IF(AND(WEEKDAY(EM2, 2) < 6, HOUR(EM2) >= 9, HOUR(EM2) < 17),"in-hours","out-of-hours")

Edited because had the wrong column

 

[Joe4]

Welcome to the Board!

For a date/time in cell EM2, try this formula:

=IF(OR(WEEKDAY(EM2,3)>4,MOD(EM2,1)<(9/24),MOD(EM2,1)>(17/24)),"Off hours","")

If will return "Off hours" for any weekend date, or any time before 9:00 AM or after 5:00 PM.

 

[jlaff]

Mark/ Joe, thank you guys so much!

I have spent hours trawl;ing google trying to find how to do this You guys are great! Really appreciate it!

 

[Joe4]

You are welcome.
Glad we could help!

If you analyze them, you will see that Mark and I use the same underlying logic, but a little different functionality. That is one of the great things about Excel, there are often many different ways to accomplish the same thing. Mark's use of the HOUR function is probably a little simpler (and more intuitive) than using the MOD function. Had I seen his answer before I posted, I probably wouldn't have posted my solution.