I made a short video to explain my challenge: https://vimeo.com/575868703/4099a1186a
Basically, I’d like to create a formula, so I get only the coming date within a list of dates. Hope someone can help me with this.
Thank you guys!
Solved
Formula for "only next date"
Best answer by Justin_Barrett
Welcome to the community, @Marc_Loskill! :grinning_face_with_big_eyes: Thanks for sharing the video, which really helps to clarify your desired goal. As you might have noticed, Airtable records can’t directly access other record data in the same table, so there’s no way to say, “Look at the previous/next record for comparison.” True, you could add a link field and link each record to the one before it, but a) that’s a pain, and b) even that has limitations.
My first thought was that slight variation of your second formula might work. Instead of checking to see if the date is in the same week as NOW(), add one week to NOW() and compare against that:
IF(IS_SAME({Webinar date}, DATEADD(NOW(), 1 "week"), "week"), "YES", "no")
If your webinars are mid-week, though, that’s going to start giving you “no” at the beginning of the week leading up to the next webinar, which isn’t what you want.
A more accurate method would be to use DATETIME_DIFF() to look at how many days there are until the next webinar date, and only mark those with “YES” where that difference is 14 days or less (I noticed that most of your dates are 14 days apart, though a couple varied from this pattern). Try this:
IF(
AND(
{Webinar date} > NOW(),
DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14
),
"YES",
"no"
)
You could also simplify this a bit: drop the outer IF() function and the text output, which will put a 1 in the record of the next webinar, and a 0 in all the others. That would still allow you to filter for that next webinar (assuming that you’re using the output to drive a filter).
AND({Webinar date} > NOW(), DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14)
If your schedule ever varies away from the 14-day pattern again, you could change 14 to a slightly higher number and it should still work.
+21Welcome to the community, @Marc_Loskill! :grinning_face_with_big_eyes: Thanks for sharing the video, which really helps to clarify your desired goal. As you might have noticed, Airtable records can’t directly access other record data in the same table, so there’s no way to say, “Look at the previous/next record for comparison.” True, you could add a link field and link each record to the one before it, but a) that’s a pain, and b) even that has limitations.
My first thought was that slight variation of your second formula might work. Instead of checking to see if the date is in the same week as NOW(), add one week to NOW() and compare against that:
IF(IS_SAME({Webinar date}, DATEADD(NOW(), 1 "week"), "week"), "YES", "no")
If your webinars are mid-week, though, that’s going to start giving you “no” at the beginning of the week leading up to the next webinar, which isn’t what you want.
A more accurate method would be to use DATETIME_DIFF() to look at how many days there are until the next webinar date, and only mark those with “YES” where that difference is 14 days or less (I noticed that most of your dates are 14 days apart, though a couple varied from this pattern). Try this:
IF(
AND(
{Webinar date} > NOW(),
DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14
),
"YES",
"no"
)
You could also simplify this a bit: drop the outer IF() function and the text output, which will put a 1 in the record of the next webinar, and a 0 in all the others. That would still allow you to filter for that next webinar (assuming that you’re using the output to drive a filter).
AND({Webinar date} > NOW(), DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14)
If your schedule ever varies away from the 14-day pattern again, you could change 14 to a slightly higher number and it should still work.
Wow @Justin_Barrett, thanks for this great answer. Did not expect so much effort :).
I am going to use your last version with the “14 days” as a fixed value. Too bad though, that there is no way to relatively say “look at the next row”. In Google sheet this is possible, but I still like to get to know more and more airtable. Is there any chance that such a function might be possible in the future?
+21Wow @Justin_Barrett, thanks for this great answer. Did not expect so much effort :).
I am going to use your last version with the “14 days” as a fixed value. Too bad though, that there is no way to relatively say “look at the next row”. In Google sheet this is possible, but I still like to get to know more and more airtable. Is there any chance that such a function might be possible in the future?
@Marc_Loskill Not likely. Airtable has many features that are similar to spreadsheets, but underneath it all its design is much closer to a database, where records don’t necessarily have a set order until they are retrieved and sorted for specific purposes. Airtable’s default grid view creates an impression of order, with records appearing to be “before” or “after” other records, but even when a view has been set to sort the records a specific way, the order has no inherent meaning at the data level, and records have no default connection to other records. It’s a key point to know that distinguishes databases from spreadsheets, and it occasionally leads to some debate over whether or not Airtable should provide some default record-to-record connection/relationship. While the developers do try to occasionally add features that make it more friendly to folks coming from a world of spreadsheets, my personal feeling is that this particular behavior isn’t likely to change.
While typing that up, I thought about another way to address this issue. It’s not necessarily better, but may help you to understand some of Airtable’s inner workings more deeply. This solution involves linking to another table, and the most logical way that I could think of to incorporate that would be to build a table for all of the webinars that you plan on hosting/producing. Even if it’s only one webinar for now, there’s some benefit to having general data about the webinar in its own space, plus it opens the door for this other solution.
I made a [Webinars] table for this purpose and made a single record in that table. I then made a link field in the other table—which I’m calling [Webinar Sessions] for now—and set that to link to records in [Webinars]. I won’t get into the nitty gritty of link fields, but if you’re not familiar with them, I recommend checking out this overview:
In that field, I linked each webinar session record to the lone webinar record from the [Webinars] table. With that done, it looks like this:
Over in the [Webinars] table, that lone record looks like this, with incoming links from all session records in the first field.
In this table I can add a rollup field. Rollup fields are designed to process data from linked records. This is done by targeting a single field from the table that’s the source of the linked records, and then telling Airtable what you want to do with the data collected from that field.
I’ll start setting up the rollup field like this, telling it to give me the contents of the {Webinar date} field:
One of the options that you see there is to restrict the collected data to records that match a certain criteria. Switch that on, then set it as follows:
That isolates those records for webinars in the future.
Now we need to add an aggregation formula. This tells Airtable how to process the data collected from those fields—in this case, the collection of webinar dates that are later than today. We can find the earliest of those dates with the MIN() function, which returns the lowest option in a collection (in this case, the lowest/earliest date). The data collected is made available in the formula via the “values” keyword, so the aggregation formula becomes this:
MIN(values)
The aggregation formula field defaults to simple formulas like this, so just start typing “min” and you’ll see the list of suggested options pare down. When you see “MIN(values)” appear, you can either click it with the mouse to insert it, or use the cursor up/down keys to select it and then hit Enter.
With that set and the rollup field saved, we have this:
Now no matter how close or far apart you schedule your webinar sessions, you can always see the one coming up next in that rollup field.
BTW, there’s also a way to take this info and get the same “Yes”/“No” output in the [Webinar Sessions] table. If you want to go that far, let me know.
+29@Marc_Loskill Not likely. Airtable has many features that are similar to spreadsheets, but underneath it all its design is much closer to a database, where records don’t necessarily have a set order until they are retrieved and sorted for specific purposes. Airtable’s default grid view creates an impression of order, with records appearing to be “before” or “after” other records, but even when a view has been set to sort the records a specific way, the order has no inherent meaning at the data level, and records have no default connection to other records. It’s a key point to know that distinguishes databases from spreadsheets, and it occasionally leads to some debate over whether or not Airtable should provide some default record-to-record connection/relationship. While the developers do try to occasionally add features that make it more friendly to folks coming from a world of spreadsheets, my personal feeling is that this particular behavior isn’t likely to change.
While typing that up, I thought about another way to address this issue. It’s not necessarily better, but may help you to understand some of Airtable’s inner workings more deeply. This solution involves linking to another table, and the most logical way that I could think of to incorporate that would be to build a table for all of the webinars that you plan on hosting/producing. Even if it’s only one webinar for now, there’s some benefit to having general data about the webinar in its own space, plus it opens the door for this other solution.
I made a [Webinars] table for this purpose and made a single record in that table. I then made a link field in the other table—which I’m calling [Webinar Sessions] for now—and set that to link to records in [Webinars]. I won’t get into the nitty gritty of link fields, but if you’re not familiar with them, I recommend checking out this overview:
In that field, I linked each webinar session record to the lone webinar record from the [Webinars] table. With that done, it looks like this:
Over in the [Webinars] table, that lone record looks like this, with incoming links from all session records in the first field.
In this table I can add a rollup field. Rollup fields are designed to process data from linked records. This is done by targeting a single field from the table that’s the source of the linked records, and then telling Airtable what you want to do with the data collected from that field.
I’ll start setting up the rollup field like this, telling it to give me the contents of the {Webinar date} field:
One of the options that you see there is to restrict the collected data to records that match a certain criteria. Switch that on, then set it as follows:
That isolates those records for webinars in the future.
Now we need to add an aggregation formula. This tells Airtable how to process the data collected from those fields—in this case, the collection of webinar dates that are later than today. We can find the earliest of those dates with the MIN() function, which returns the lowest option in a collection (in this case, the lowest/earliest date). The data collected is made available in the formula via the “values” keyword, so the aggregation formula becomes this:
MIN(values)
The aggregation formula field defaults to simple formulas like this, so just start typing “min” and you’ll see the list of suggested options pare down. When you see “MIN(values)” appear, you can either click it with the mouse to insert it, or use the cursor up/down keys to select it and then hit Enter.
With that set and the rollup field saved, we have this:
Now no matter how close or far apart you schedule your webinar sessions, you can always see the one coming up next in that rollup field.
BTW, there’s also a way to take this info and get the same “Yes”/“No” output in the [Webinar Sessions] table. If you want to go that far, let me know.
You can also take Justin’s solution one step further and pass that “Next Date” back to the Webinar Session records with another rollup. Then have each Webinar Session record compare its date with that “Next Date for Webinar” to see if it is the next one or not. That way you can view and filter for the “Next Date” records in the original Webinar Sessions table.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.