Display Field based on Date Range


#1

I have a table where I need to display the amount given (in field {amount} if it was given between particular two dates. Like 5/1/17-5/1/18 I’m at a total loss of how to do this.


#2

You can combine these functions:

  • IF to do the check
  • AND to join the 2 conditions
  • IS_BEFORE/IS_AFTER to check the 2 dates

#3

Thank you! Could you give me an example of how that might look? I’ve been playing with functions and I’m certain I’m not typing them right.


#4

Try first :sweat_smile: If you don’t get it to work you can ask for help :wink:

PD: I almost gave you the formula, just read a little the docs


#5

Thanks for the encouragement! Over the last couple of days I’ve tried various modifications of this: If((IS_BEFORE({Date}, 5/1/2017)) AND (IS_AFTER({Date},5/1/2018)), Amount, 0). I am way out of my league in figuring out programing - generally I can find answers by looking through other posts, but I haven’t found one and I feel like I keep hitting my head against a brick wall.


#6

Hi Macie

Try this:

IF(AND(IS_AFTER({Date 1},‘5/1/2017’),IS_BEFORE({Date 1},‘5/1/2018’)),Amount,0)

Where you were going wrong with your example is the way AND works in Airtable - take a look at the documentation.

You may need to consider that IS_AFTER and IS_BEFORE are not inclusive (in other words Amount would not be displayed for 5/1/2017).


#7

Thank you, Thank you! I got it to work!
And thank you for the tip on it not being inclusive. I thought it was the case - but you firmed it up! Thanks again!


#8

5 is not after nor before 5. How they would be inclusive?


#9

As you don’t have something like IS_AFTER_OR_EQUAL or IS_BEFORE_OR_EQUAL the expression must be defined to consider an inclusive or exclusive IF by default.
That was @Macie_Smith clarification.

Rgds


#10

I understand his clarification (not yours BTW :sweat_smile:), but I see it as unnecessary. One day never could be after or before itself, it’s obvious. Of course a user could miss that and do a wrong comparison and he would love to know, but it’s not a trick, a tip, or a rule, it’s just logic. I don’t like to point that type of things, I think it confuses more than helps the users.

PD: Well, the functions could be programmed to include the same day but that would be too strange for me, I think that expect that the same day is not included is the natural and logic way of thinking.


#11

Yes, my confusion came from the use of AND and OR operators, but at the end you are right.
Hope this text help to clarify if somebody who read the post is confused about including and excluding range of values.