Nested IF applied only if the date range includes Fridays and/or Saturdays

[Jozsef_Kurucity]

Hi,
I have these functions in two fields:

Field “Dani”:
DATETIME_DIFF({Od},{Do},'days')-1
“Od” field is a date without time, and “Do” is also a date. Sometimes they could be the same dates.

Field “Test date function”:
IF( Smena=1,Dani-1, IF( Smena=2,Dani-1, Dani) )
“Smena” field can be only one of these three numbers: 1, 2 or 3.

Would it be possible to run the IF function of “Test date function” field only if the date range between “Od” and “Do” include Friday(s) and/or Saturday(s)?

[Jozsef_Kurucity]

In the meantime I was thinking more about this and to me it seems like too complicated to accomplish. Because there could be cases when the range between “Od” and “Do” could be, let’s say even up to 60 days, and during that period “Smena” wouldn’t remain constant. Even in cases of 14 days, “Smena” would change so that makes everything too complicated in my opinion :slightly_smiling_face:

[Justin_Barrett]

Yeah, that’s a tricky one. One of the key things that makes it tricky is, as you guessed, the variable length of time between “Od” and “Do”, but not just because of how that affects the value in the “Smena” field. It’s tricky because Airtable doesn’t have a way of iterating through an arbitrary number of items like a span of days, so trying to see if any of the days in that span is a Friday or Saturday isn’t currently possible.

On a different note, here’s a different way to structure the function in the {Test date function} field:

SWITCH(Smena, 1, Dani-1, 2, Dani-1, 3, Dani)

SWITCH is ideal when you’re checking a single field to see if it’s one of a known number of values/options, and need specific output based on each option. In many cases it’s more compact (and easier to read) than a series of nested IFs. Anyway, this obviously doesn’t help with your core issue, but I’m just throwing it out there for what it’s worth.

[Jozsef_Kurucity]

Thank you for reply!
Yes, I was checking SWITCH function too. It should be more simple than IF, but I actually didn’t get how it works :slightly_smiling_face:
And how do I nest SWITCH function? Is that possible at all? Like, let’s say the function you sent, works like: If Smena is 1 then subtract 1 from Dani (Dani-1), if Smena is 2, do the same (Dani-1), if Smena is 3 don’t change it. Is that correct? If yes, how do I use other operators with SWITCH? What if I want to have something like this: If “Dani” is <=-3 and “Smena” is 1 then do Dani-1? It’s actually more about combining SWITCH and AND function. If I got SWITCH correctly then I don’t see the way to combine it with AND function.
I have the idea about AND function, but I’m not sure how to implement it. IF X is less or equal to Y AND Z is NOT larger than 50 THEN do this ELSE do that. Is it possible to do something like that with Airtables in some simple and logical way? :slightly_smiling_face:

[Justin_Barrett]

Jozsef_Kurucity:

Like, let’s say the function you sent, works like: If Smena is 1 then subtract 1 from Dani (Dani-1), if Smena is 2, do the same (Dani-1), if Smena is 3 don’t change it. Is that correct?

Correct.

Jozsef_Kurucity:

If yes, how do I use other operators with SWITCH? … If I got SWITCH correctly then I don’t see the way to combine it with AND function.

Correct. That the limitation of SWITCH. It’s perfect if you’re checking a single value—like the value of a single field—and outputting specific things based on that value. There’s also the option of including a fallback/default value at the end in case none of the supplied comparison values match. Depending on your circumstances, that might be useful. However, once more complex comparisons come into play, SWITCH won’t work, and you’ll have to revert to IF.

Jozsef_Kurucity:

IF X is less or equal to Y AND Z is NOT larger than 50 THEN do this ELSE do that. Is it possible to do something like that with Airtables in some simple and logical way?

Yes. Here’s a pseudo-code example of how that would look:

IF(AND(X <= Y, Z <= 50), do_this, do_that)

The key here is that an Airtable IF function can only do a single comparison. When you need to make multiple comparisons, they have to get wrapped up by one or more AND/OR functions in the place where that single IF comparison normally lives. So the AND function is calculated first and returns either True or False, which is then processed by IF, returning the do_this value if it’s true, or do_that if false. Make sense?

[Jozsef_Kurucity]

OK. Good! Very useful!

By the way, when you said in your first reply

Justin_Barrett:

but not just because of how that affects the value in the “Smena” field

Smena is not affected by length of time between Od and Do. Smena is changed weekly regardless of the length of time. Anyway, if Airtable can’t easily subtract days in the way I imagined then it’s too complicated. The solution I currently have is user interaction :slightly_smiling_face: in the way that the person filling up the form chooses the number of days to subtract manually from drop down list and that’s it. What to do :slightly_smiling_face: I don’t see other way to do it for now.

[W_Vann_Hall]

1. Would using WORKDAY_DIFF() help here?
2. If not, would the code posted here help?

[Jozsef_Kurucity]

Thanks for reply.
WORKDAY_DIFF() exclude weekends and that’s it. I needed the exclusion to happen based on “Smena” field condition. And the problem is that if the length between dates is 14 days or more, for example, “Smena” is not the same anymore for two weekends included in the range of dates. Also, I guess, WORKDAY_DIFF() exclude 2 days for weekend. And the third problem with WORKDAY_DIFF() could be that the weekend is Saturday and Sunday, because I’m not sure I can choose which and how many days are the weekend days with that function.

I checked the second solution too but I am not getting what that formula does exactly?

[W_Vann_Hall]

Let me start by asking what exactly does {Smena} indicate? Is it the shift worked by the employee? Is there a way to derive the shift for different parts of the time period for date ranges extending past 14 days?

There isn’t a way to set what days WORKDAY_DIFF() excludes as weekend days — but the second snippet of code I referenced does. The problem is that I don’t recall what else that code does; that is, I don’t remember if it supports date ranges greater than a week, because not long after I wrote it, Airtable announced WORKDAY_DIFF(). However, I’m certain it can be modified to meet your needs — assuming your needs can be met. (For instance, if employees work a non-predictable rotation of shifts, and the shift worked is important to the calculation, you’re limited to calculating hours worked manually.) If you can explain the interaction of {Smena} to the calculation, though, I’m pretty comfortable we can find a solution for you.

[Jozsef_Kurucity]

Yes, {Smena} is the shift. The shift changes weekly. In the way that the Employee who worked the first shift (6am to 2pm) on Friday becomes second shift (2pm to 10pm) on Sunday, and the one who worked second shift on Thursday becomes third shift (10pm to 6am) on Saturday.