If date is after / greater than?

[Alison_Hooper]

Hi there, help needed!

I have an arrival and a departure date, different dates over a 2 week period, I then need 14 columns with if/then formulas so I can get a room count for each specific night. In Excel I had =if(departure=>[date],"-",if(arrival=<[date],1,"-")). In [date] I had each night’s date. I’ve tried the same in Airtable and it isn’t happy. Not sure how I should be specifying the [date] I am trying to analyse!

Hope that make sense to someone and I can find a solution!

Thanks

[AlliAlosa]

Hi there,

Dealing with dates in Airtable is definitely not as straight forward as excel, but it is doable! You’ll probably want to make use of the formula functions IS_BEFORE(), IS_AFTER(), and IS_SAME(). You can read up on all the date/time formulas Airtable offers here:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

I’m not sure if I’m understanding your excel formula correctly. When I recreated it, it returned a “1” for any date that was outside of the date range between {Arrival} and {Departure}. Is that how it should work? If you’re trying to get a room count, I would think you would want the opposite (a “1” for any date inside the date range of arrival and departure).

Here’s an example of a formula that will return “1” if {Date} is inside the range of {Arrival} and {Departure} (except if it is the same as the departure date).

IF(AND(OR(IS_SAME({Date}, {Arrival}), IS_AFTER({Date}, {Arrival})), IS_BEFORE({Date}, {Departure})), 1)

If you’re ultimately trying to add up the result of the above formula, I wouldn’t add the “-” for when the parameters aren’t met. This will cause the field to be treated as a string, forcing you to have to take extra steps down the line to treat the “1” as a number again.

Happy to help further if needed!

[Alison_Hooper]

That’s fab thanks, yes, I did the quick and dirty the wrong way round!! So, I can then generate the daily tallies and filter and list, but I cannot see how to get a room count totalling them - the sum appears at the top of the column but cannot see how to get that to appear somewhere else.

[AlliAlosa]

Hi there,

In order to add up an entire column, you’ll have to have all of those records linked to one record on another table, and use rollups to sum the values.

If you want to share your base I’m happy to demonstrate!

[Alison_Hooper]

Thank you. I’m going to have a play and have to figure out how to link anyway but will come back to you if I cannot suss it out.