Find the next real date

[Doug_Gregan]

OK, all you smart people out there. (I know there are a lot of you!) I need a formula for my database and I need some help, (because I’m horrible with math and algebra.)

I have different churches with different services and studies through the week. The services are stored in the database with an unspecified date/day/time. (e.g. Bible study is every Tuesday night at 7:00pm) On an Airtable calendar I want to display an actual date to the user. (i.e. Tuesday, March 10th.) In order to do this I have to calculate what the next actual future date will be from whatever is the current date. It has to dynamically adjust with any given day the user might be looking at the calendar.

So…

1. Get the current date (e.g. March 4th)
2. Get the stored generic day (i.e. Tuesday)
3. This is where I get stuck !

[Jeremy_Oglesby]

Hi @Doug_Gregan,

Do you have access to Blocks in your base? IE, do you pay for the Pro plan?

If so, the Scripting Block might make this task a lot easier.

Either way, it would be helpful to know quite how you have your base set up. For starters, I might suggest a setup similar to this:

[Doug_Gregan]

Hello Jeremy,

Yes, that’s essentially my setup, with the exception of the frequency, which I love and will add.

It would be quite accurate to say that I don’t write javascript. I’ve modified some code, but not much else.

[Jeremy_Oglesby]

Doug_Gregan:

It would be quite accurate to say that I don’t write javascript. I’ve modified some code, but not much else.

Fair enough – but if you have access to the Scripting Block, somebody else could write JavaScript for you that would make this alot easier. Doing what you are asking in a formula is more complicated than doing it in JavaScript, for someone who knows JavaScript.

[Justin_Barrett]

Not sure why my previous post is pending, but I’ve got something similar to this that I can share with you if you wish to reverse-engineer it and adapt it to your situation. I don’t have time to delve into the inner workings right now, although I do plan to share a how-to in some form (video, probably) at some unknown point in the future.

[Justin_Barrett]

Here’s a screenshot from my setup. It’s flexible enough that I can specify lots of different intervals, and they all calculate correctly. This is all formula-driven.

[Doug_Gregan]

I would be interested in attempting to modify whatever you can share, Justin. Thank you!

[Justin_Barrett]

Here’s a version that anyone can mess with:

Airtable

Setting Custom Intervals - Airtable

A stripped-down portion of my task tracking base, showing how I've set it up for maximum flexibility when it comes to choosing unique schedule intervals for each task. Justin Barrett (@Justin_Barrett in the community)