Skip to main content

Hi, I’m trying to build an IF statement with an OR in it but I’m not getting the expected result. I have created a simple table for tracking hours worked and calculates overtime.


Columns are as follows…

Date - is just the date in question.

Calculate Weekday - WEEKDAY(Date)


So far so good.


Because the standard work hours vary from day to day (7.5 for Mon, Tue, Wed, Thu / 7.25 for Fri / 0 for Sat, Sun), I wanted to create an IF query to return the correct hours for that day number.

Rather than going overboard with nested IF, I tried to simplify to the following, but the OR in this doesn’t seem to be doing what I expected…

IF(WEEKDAY(Date)=5,7.25,IF(WEEKDAY(Date)=OR(1,2,3,4),7.50,0))



From what I can see, the day 5, 0 & 6 work okay. But my problem comes with the OR as only day 1 works; the rest come back as 0 hours. Am I misusing this function? I was expecting the OR to then return 7.50 fo all of 1,2,3, and 4.

The issue here is not that the function is not operating successfully it is the result of the function working correctly. The OR() statement returns only a true or false value, so a 1 or a 0. So in your equation when the value 1,2,3,4 is found it returns 1, which only connects with the Monday value 1 on the Weekday formula.


I think the easiest way to achieve your goal is to use SWITCH (), this looks at a series of options and returns values based on those options. It is a feature to get away from nested IF statements. In your case the formula would be:


SWITCH(WEEKDAY(Date),0,0,1,7.5,2,7.5,3,7.5,4,7.5,5,7.25,6,0)


This would look at the result of WEEKDAY(Date) and return:

0 if WEEKDAY(Date) is 0

7.5 if WEEKDAY(Date) is 1

7.5 if WEEKDAY(Date) is 2

7.5 if WEEKDAY(Date) is 3

7.5 if WEEKDAY(Date) is 4

7.25 if WEEKDAY(Date) is 5

0 if WEEKDAY(Date) is 6


Hope this helps!

The issue here is not that the function is not operating successfully it is the result of the function working correctly. The OR() statement returns only a true or false value, so a 1 or a 0. So in your equation when the value 1,2,3,4 is found it returns 1, which only connects with the Monday value 1 on the Weekday formula.


I think the easiest way to achieve your goal is to use SWITCH (), this looks at a series of options and returns values based on those options. It is a feature to get away from nested IF statements. In your case the formula would be:


SWITCH(WEEKDAY(Date),0,0,1,7.5,2,7.5,3,7.5,4,7.5,5,7.25,6,0)


This would look at the result of WEEKDAY(Date) and return:

0 if WEEKDAY(Date) is 0

7.5 if WEEKDAY(Date) is 1

7.5 if WEEKDAY(Date) is 2

7.5 if WEEKDAY(Date) is 3

7.5 if WEEKDAY(Date) is 4

7.25 if WEEKDAY(Date) is 5

0 if WEEKDAY(Date) is 6


Hope this helps!


Hi @Brian.Swanson - This works and is even more elegant that the approach I was taking - Thanks.


Will read up properly on SWITCH now as I had a basic grasp of what it did but hadn’t thought to use it.

Reply


Terms & Conditions