Help

Display Field based on Date Range

Topic Labels: Formulas
6390 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Macie_Smith
4 - Data Explorer
4 - Data Explorer

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.

10 Replies 10

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

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.

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

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

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.

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

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!

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

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

I understand his clarification (not yours BTW :grinning_face_with_sweat: ), 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.