I am looking to show hospital procedure times done out of hours which would be between 19:00pm to 07:59am.
For example a procedure start time at 01:15 should show as "Out of Hours" or "In Hours" if the procedure start time was between 08:00am and 18:59pm. My current coding with Column N being the Time into Theatre is =IF(N2="","",IF(N2>=(- -"07:00 PM"),"Yes","No")) but it doesn't pick up the time after midnight before 07:59 the next day.
Can anyone help please?
Try =IF(N2="","",IF(OR(N2<TIME(8,0,0),N2>=TIME(19,0,0)),"Out of Hours","In Hours"))
This conditional checks if time is before 08:00 (N2<TIME(8,0,0)) or after 19:00 (N2>=TIME(19,0,0)). If either is true, then it's Out of Hours. Otherwise, its In Hours
You're on the right track, but the logic needs to properly account for two time ranges:
In Hours: from 08:00 AM to 06:59 PM (18:59)
Out of Hours: from 07:00 PM (19:00) to 07:59 AM the next day (this wraps around midnight).
In Excel, time is stored as a fraction of a day. So:
08:00 AM = 0.3333
06:59 PM = 0.7903
07:00 PM = 0.7917
07:59 AM = 0.3326
In your case, for column N (Time into Theatre), use the following formula:
=IF(N2="", "", IF(AND(N2>=TIME(8,0,0), N2<=TIME(18,59,0)), "In Hours", "Out of Hours"))
TIME(8,0,0) = 08:00 AM
TIME(18,59,0) = 06:59 PM
The AND() ensures time is in the "In Hours" range
Anything outside that (i.e., before 08:00 AM or after 06:59 PM) is considered "Out of Hours"
N2<=TIME(18,59,0) is inaccurate. N2<=TIME(18,59,59) is better but still inaccurate. N2<TIME(19,0,0) is the way to go since it makes even 18:59:59.999 in hours.Another solution:
=IF(N2="","",IF(TIME(HOUR(N2)+5,0,0)<13/24,"Out of Hours","In Hours"))
