Set new date depending on the created time of a record

[Max_Ha]

Dear fellow airtable wizards,

Today my quest has put me up to a challenge I hope you guys can help me with.

Short backstory: We offer courses which people can subscribe to (their record enters the base using forms) The first course starts the 3rd of January. Whenever they register we send them an email (using automations). In this email we confirm that the first course is on the 3rd of January.

I came up with the following formula:

IF(IS_BEFORE({Created time},{Start date}), DATETIME_FORMAT({Start date},‘DD MMMM’))

Now comes the tricky part… If a person registers a day after the 3rd of January, I want my formula to let them know their first course starts a week later (the 10th of January). If they register after the 10th, they start on the 17th… and so on.

I’m looking for a formula that knows how far apart the {Created time} and the {Start date} is, so it can add 7 (or 14 or 21…etc.) days to the {Start date}.

I know there’s an easy way of doing this by simply making a formula that tells me the date of the upcoming monday… But I need the starting date for my administration later on. That’s why it shouldn’t update itself every week.

Thank you guys in advance!

Gr. Max

PS: First one to answer gets a bonus question :stuck_out_tongue_winking_eye: : I also want to make exceptions during holidays… So I’m adding a new field: {Holiday date} If the outcome of the original formula is {Holiday date} I want it to add another 7 days to the {Start date}. I don’t know if this can be added to the original formula. If not, I don’t mind this being a separate formula.

[Julian_E_Post · Accepted solution]

Hi Max!
Try this:

IF(IS_BEFORE({Created time},{Start date}), 
DATETIME_FORMAT({Start date},'DD MMMM'),
DATETIME_FORMAT(DATEADD({Start date},ROUNDUP((VALUE(DATETIME_FORMAT({Created time},'DDD'))-VALUE(DATETIME_FORMAT({Start date},'DDD')))/7,0)*7,'days'), 'DD MMMM'))

My brain already hurts from that one, so I’m gonna pass on holidays :thinking:

EDIT: I changed the formula to fix an error that was making it fail on certain days

[Max_Ha]

Wow Julian… You’re an absolute genius for this one. It totally works!

I’m not totally new to airtable but this one was definitely out of my league. I never used the ROUNDUP or VALUE in a formula before. Thank you so much

I’m gonna try and see if I can make sense of what you did. Hopefully I can figure out how to make exceptions for holiday dates. If it doesn’t work I’ll come back to you :winking_face:

Cheers and happy holidays.

[Max_Ha]

Hi Julian,

I thought it worked perfectly until I found out that the formula is actually one month off… It’s telling me the right day but for some reason it’s a month ahead each time… I tried to give it a lazy solution by adding a DATEADD(… , -1 ‘month’) to the formula, but that ruins the entire logic of the original formula.

I’m clueless of why this is happening so I’m reaching out to you again… Any idea?

[Julian_E_Post]

That’s weird! I just went back and checked it in my base, and it seems to work fine. Is your start date always the same? Here is the base: https://airtable.com/shrRjaOwd7aL0X87r

[Julian_E_Post]

Hi Max,
I did find an error, which I’ve corrected in the base and in the original reply. Basically the formula needs to find out how many weeks out the new start date is, but it needs to know where the original start date was in order to do that. I had added an offset that did this just for Jan 3, not for any date. It pulls that info from the start date field now.

[Max_Ha]

Julian_E_Post:

IF(IS_BEFORE({Created time},{Start date}), 
DATETIME_FORMAT({Start date},'DD MMMM'),
DATETIME_FORMAT(DATEADD({Start date},ROUNDUP((VALUE(DATETIME_FORMAT({Created time},'DDD'))-VALUE(DATETIME_FORMAT({Start date},'DDD')))/7,0)*7,'days'), 'DD MMMM'))

Sorry, you’re absolutely right… The actual starting date is in February and next season will start around September so I’m glad it’s sorted now. Thank you for your additional solution. It works great now.