Convert Date Selected to the Next Tuesday Date

Help Center Get Answers
1

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:

IF(2-WEEKDAY({START DATE})<0,DATEADD({START DATE},2-(WEEKDAY({START DATE}))+7,‘days’),DATEADD({START DATE},2-(WEEKDAY({START DATE})),‘days’))

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

Dan Rushton

2

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

image

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

image
image585×750 12.9 KB

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}

image
image586×863 15.1 KB

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
3

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

1 Like
4

Hi @StephenChapman

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
5

Yep! You can read more about it here.
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
6

Hi @StephenChapman

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