Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Help using OR in an IF statement

Topic Labels: Formulas
Solved
Jump to Solution
246 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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))

image

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.

1 Solution

Accepted Solutions

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!

See Solution in Thread

2 Replies 2

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.