Formula to return a weekly event date

Baserow Help
1

Are you using our SaaS platform (Baserow.io) or self-hosting Baserow?

SaaS

What do you need help with?

I’ve got a weekly event that happens every Friday and people submit things in preparation for the event each week. I’m looking for a way to attach the submissions (which come in via a form in my application) to the applicable Friday. Submissions are due by Wednesday, so the submission week runs Thursday to Wednesday, i.e. a Thursday submission shouldn’t be attached to the Friday that immediately follows but to the next one.

I thought it would be easiest to have a formula that returns the day of the week, based on the submission date, and then an if formula that adds the appropriate number of days and returns the applicable Friday’s date – but I can’t figure out a way to make this work. Is there a way to return ‘date’ + 4 days (or something analogous)?

It’s also possible that there’s an easier way to go about this that I’m missing…

2

@frederikdc could you please help with this request?

3

There is not a very easy way, but it is also not that hard :slight_smile:

  1. Create a formula field named Day with the following formula tonumber(datetime_format(field(‘date’),‘d’)). This returns a number between 1 (Sunday) and 7 (Saturday)
  2. Create a formula field named Next Friday with the following formula: if(field(‘Day’) < 5, 6 - field(‘Day’),13 - field(‘Day’)). If the day is before Thursday (5), it is scheduled for the Friday of this week, otherwise the Friday of the next week (13 = 7 + 6)
  3. Create a formula field named Meeting Date with the following formula: field(‘date’) + date_interval(concat(field(‘Next friday’),’ days’)) This adds the number of days from the previous formula to the date that the issue was reported.

If can combine everything into a single formula field, but that might make everything hard to read