Skip to main content
Solved

Help using OR in an IF statement

  • October 24, 2021
  • 2 replies
  • 43 views
S
Forum|alt.badge.img+2

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.

Best answer by Brian_Swanson

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!

2 replies

B
Forum|alt.badge.img+7
  • Brian_Swanson
  • Participating Frequently
  • Answer
  • October 24, 2021

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!

S
Forum|alt.badge.img+2
  • Stephen_Curtis
  • Author
  • Inspiring
  • October 24, 2021

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.

Terms & ConditionsAccessibility statement