One Record For Every Monday

[Mary_Going]

I am trying to create one record for each Monday of the year. The following formula is one that I found on this forum. It works, of course, but I have to enter the date {Date} which defeats the purpose. Is there a way for it to point to the previous record’s date field? (Like, last week’s Monday plus 7?)

IF(1-WEEKDAY({Date})<=0,DATEADD({Date},1-(WEEKDAY({Date}))+7,‘days’),DATEADD({Date},1-(WEEKDAY({Date})),‘days’))

[AlliAlosa]

Sadly, there’s no really simple way to do this. You can’t reference a previous record without some complicated linking and even then it can get messy. I don’t think that would help you in this situation.

Here’s a workaround that might help.

Let’s say we’re working with all the Monday’s in 2019. To prevent the dates from changing in January 2020, I would add a field called {Year}, possibly a single select field, and populate it with “2019”.

Next, you’ll need a number field, maybe called {Monday #}. I would fill it in sequentially with the numbers 1-52. In plain terms, the numbers represent weeks; i.e. 1 is the first Monday of the year.

The following formula will take {Monday #} and give you the date based on the {Year}.

IF({Monday #}, DATEADD(DATEADD(DATETIME_PARSE("1/1/" & {Year}), (WEEKDAY(DATETIME_PARSE("1/1/" & {Year})) * -1),'days'), (7 * {Monday #}) -6, 'days'))

It’s not perfect, but at least you can base this off of typing one number (two if you count the {Year}), rather than having to retype the date!

[Taylor_Diken]

I had the same issue and found a workaround that isn’t the most elegant, but it does work. Airtable has a feature like Excel’s Autofill if you squint.

For my base that keeps track of my bills and payments, I needed to create records with + 1 month for the next 12 months. After I made the first two records with the date fields a month apart (2019-07-01 and 2019-08-01) I selected those two cells and dragged (as though using Autofill in Excel) to fill in the dates in this pattern. You have to manually create the new records (there was a lot of CTRL+SHIFT happening) but at least you don’t need to use the date picker every time. This should work for all types of intervals, so if you have 2019-07-01 and 2019-07-08 selected, dragging down from that would give you 2019-07-15, 2019-07-22, etc. etc.

I hope this helps!