If-then with greater than lesser than filter?

Ask for Help
1

Hi folks,

This is to do with dates. In short, there is an admin allocating dates to every user. When planning something for a particular user, I want to check if there is someone else who can be grouped with this user - i.e. if another user has an allocated date that is 15 days before or after our considered user.

I have reached until here:

image
image717×480 83.1 KB

With some dancing around I am setting the base date for the batch calculation. -That’s my first column seen here. If the row doesn’t belong to the considered (baseline user) then I subtract or add 15 days from their date (which serves as baseline date) and populate the +/- 15 for all users (2nd and 4th column). The calculated dates are then shown in the 3rd and 5th columns, where 3rd = 1st plus 2nd, 5th = 3rd+4th column.

I want to create a conditional math column where I can say if calculated date is less than (ie earlier) allocated date by admin then “no” the user isn’t a batch mate, else “yes” the user is a possible batch mate.

Logically … straightforward. Executing it here though…haha, it’s got me stumped! All the IF conditions are non mathematical - how do I set a ‘if lesser than X then Y’ condition? I can’t use excel because these columns have to be user specific columns in my functionality - multiple people could be planning for the users, so each of them should see ‘relative’ batch filtering based on the user that they chose.

Can someone please point me in the right direction to explore/help me solve this? TYSM!

2

I think you might be making this a bit more complicated than it needs to be. Assuming that I’m understanding correctly, I’d do something like the following:

  • When a user is selected, write their allocated date to a user specific date/time column
  • Use a Single Value column to take that user specific value and apply it to all user rows
  • Then create two math columns to calculate the date 15 days before and after the selected users allocated date
  • Finally, an if-then-else column to compare all users allocated dates to the calculated earliest/latest dates:
    – If user date is before earliest date, then “no”
    – If user date is after latest date, then “no”
    – Else “yes”
2 Likes
3

Hey there, thanks for replying Darren! Ohhh SV column - I completely forgot its existence. So, an elegant workaround with fewer than a million columns? Wild, I say! lol… yeah I think overcomplicated it there. But the last part of what you said is where I’m stuck even now. If-then-else doesn’t seem to support a less than or greater than comparison right? It offers is, is not, contains… but not a math comparison? Have I misunderstood that?

4

It does, but only when you’re comparing numerical values. In this case, you’re comparing dates. So what you want is “earlier than” and “later than”.

2 Likes
5

Oh my gawd! I didn’t realise the IF conditions change automatically for date values… I thought it was always the equal, includes etc. Sigh. So much dumb. Thanks a lot Darren!