Convert Date Selected to the Next Tuesday Date

Hello!

Super nube here who is switching from Airtable to Knack. I am in need of some help for our course registration process. We register our students for Tuesday start dates. Is there a way to either only show Tuesday’s in the date field OR like we did in Airtable, convert the date chosen to the nearest Tuesday? Below is the formula I used to use in Airtable:

Any help anyone can provide will be greatly appreciated! Thanks.

Dan Rushton

Hey @Dan3,
You’re going to need three fields here. The start date, an equation to calculate the start date day of the week, and an equation to calculate the next Tuesday.
Note that if the start date is a Tuesday, it will return the same day (e.g. start date = 19 Sep, then nearest Tuesday = 19 Sep).

The Day of week equation field looks like this:
`getDateDayOfWeek({Start date})`

The Nearest Tuesday date equation field looks like this:
`{Day of week}<=3 ? {Start date}+3-{Day of week} : {Start date}+10-{Day of week}`

I believe due to the nature of how the Equation field calculates either numerically or date (and not both), merging the two equation fields into one won’t work properly, so avoid trying the below:
`getDateDayOfWeek({Start date})<=3 ? {Start date}+3-getDateDayOfWeek({Start date}) : {Start date}+10-getDateDayOfWeek({Start date})`

2 Likes

You ARE AMAZING!!! Thank you thank you thank you!

1 Like

This is great I never knew that you use a ternary statement in an equation field. This will remove the need for quite a few JavaScript functions.

Also this specific answer will solve something we have been trying to figure out for a while which is finding out if something has happened before the following weeks friday of the previous date it happened on.

Great thank you.

1 Like

Unfortunately ternary operators are only bound to numerical values, and don’t work when trying to assess if a field is blank or equals certain text.
It would be a gamechanger if they were in text formulas too.

2 Likes

I completely agree about having non numeric in a ternary. We have used this for when we work out days since… if the field is blank the equation field puts a massive number and we can’t default to zero as that means something.

We now have a date equation field to work out days since, then a number equation field with the ternary:

`{daysSince} > 1000 ? -1 : {daysSince}`

We then have a ‘Short Text’ field with 2 conditional rules:

If number equation field is -1 then put ‘Not…’ in the field,
if number equation field is not -1 then put daysSince in the field.

Long story short it takes 3 fields to do the job of 1 very annoying. It ended up being quicker to do this with javaScript than using Knack which is a no code database.

If Knack could fix this it would make things a lot easier and simpler.

Craig