Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

One Record For Every Monday

Topic Labels: Formulas
1451 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

2 Replies 2

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!

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!