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’))
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!
+1I 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!
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
