Formula to check if working out of hours

0
votes

I'm trying to find out if someone was not working during 'out of hours'.

  • If Monday to Friday, not between the hours of, 8am to 9pm, then the answer would be "Yes"

  • If it's a Saturday, not between the hours of, 8am to 5pm, the answer would be "Yes"

  • If it's a Sunday, not between the hours of, 9am to 5pm, the answer would be "Yes".

Otherwise the answer would be "No".

There are 4 columns: 1. Person's Name (A) 2. Date (B) 3. Time (C) 4. Out of Hours (D)

I've copied in the formula below which is working correctly for Monday to Friday, but I'm struggling with the second part of the weekend one. 

=IF(OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7),"Yes",IF(AND(C2>=8/24,C2<21/24),"No","Yes"))

If anyone could advise on how to change the formula to work with 'out of hours' for Saturday and Sunday?

It would be greatly appreciated.

2
excelexcel-formulaexcel-2016

2 Answers

0
votes

Since your WEEKDAY(B2) will be from 1 (Sunday) to 7 (Saturday), you can use CHOOSE to select the Start/End time for your "In-Hours" period:

Start: =Choose(Weekday(B2),9,8,8,8,8,8,8)

End: =Choose(Weekday(B2),17,21,21,21,21,21,17)

So, if HOUR(C2) is >= Start, and HOUR(C2) is < End then you are "In-Hours" ("No"), otherwise you are "Out of Hours" ("Yes")

=IF(AND(HOUR(C2)>=CHOOSE(WEEKDAY(B2),9,8,8,8,8,8,8), HOUR(C2)<CHOOSE(WEEKDAY(B2),17,21,21,21,21,21,17)),"No","Yes")

Of course, I would recommend replacing the CHOOSEs with a pair of VLOOKUPs, and having a hidden sheet where you list the Weekday and the Start/End-of-Hours hours. (Much easier to update if The Boss decides to change the hours!)

-1
votes
=IF(WEEKDAY(B2)=7,IF(OR(C2<TIMEVALUE("8:00"),C2>TIMEVALUE("17:00"))=TRUE,"Yes","No"),IF(WEEKDAY(B2)=1,IF(OR(C2<TIMEVALUE("9:00"),C2>TIMEVALUE("17:00"))=TRUE,"Yes","No"),IF(WEEKDAY(B2)<>1,IF(OR(C2<TIMEVALUE("8:00"),C2>TIMEVALUE("21:00"))=TRUE,"Yes","No"),"No")))

You will have to use nested ifs. Hope above formula will work.