How can I auto populate the start of the week and the end of the week?

I’m very new to airtable but have learned a lot by watching tutorials and searching the community. I’m not sure if I’m searching for the right thing for this particular issue though.

I’m setting up an airtable for a client who has a form that needs to be completed by his clients on a weekly basis. He needs to know who hasn’t submitted the form 3 days past the day he sends it out which will be on the same day each week.

For instance, he’ll send the email with the form attached to be completed every Monday and if someone doesn’t fill it out by Thursday, we need to automate an email to follow up and remind them to complete it.

I’m thinking I need a field dedicated to auto-populating whatever the current day is or at least a formula that shows when the week starts and when it ends.

What formula do I need for this? This is literally the last thing I need in order to complete this airtable setup but I can’t figure it out.

Any help would be greatly appreciated!

Is it an Airtable form or an external form?

Hello, thank you for responding. It’s an Airtable form.

And the person filling out the form need to manually enter her/his name? Or does each client have their own Airtable account?

I have it set up to where all they have to do is select their name. I’ve added all of their names into Airtable already. Hopefully this screen shot will come up so you can see what I mean. (These are all fake names I made up to test it out). They don’t have their own Airtable account, but the coaches assigned to each client will have one.

Great! Have you added a “Created time” field to your table?

Yes, I have a “Date Submitted” field. I have a place on the form that asks for Today’s Date that they select and it goes into a “Date Submitted”.

You don’t need to do that. There’s actually a type of field called “Created time” that will automatically show you when the record was created. I.e. when the form was submitted. I’ll have to think about what to do next :slight_smile:

1 Like

Oh wow!! Ok That might be better because I was thinking what if they accidentally select the wrong date.

I’ll create a “Created Time” field now! Thank you. And thank you for taking the time to help me walk through this!

Also, I wanted to add that this is the formula I’m using to show that they are 3 days overdue. I’ll have to change “Date of submission” to the “created time” field though.

IF(DATETIME_DIFF(TODAY(),{Date of Submission},‘days’)>3,“Overdue”)

Will you have a separate table for each form, or is it the same form and table every week?

It will be the same form and same table every week. I should add, I’ll be able to see all of their form submissions when I click on their name if that makes sense. I have a tab for Clients and a tab for the form and the answers will be linked to the same client when the submit the form multiple times.

Then how will Airtable now which week’s form the client have submitted? I.e. if the client filled out the form 1 day, 8 days or 15 days etc. after they got the link?

I created a loom video to try to explain so you can see how i have it set up. This is just a rough draft where I’m testing things out.

https://www.loom.com/share/0ed6badf5bbc4527a995a712d5b6a436

My client will be sending an email on the same day every week and the have 3 days to complete it. So I was thinking I need a field to indicate the start of the week so I can create a formula to show when they are over due when it’s been beyond 3 days.

It will always be sent on a Monday and it always be late if it’s not submitted by Wednesday on any given week.

I have a grid view set up to show the past week and past two weeks so they can see.

I’m not sure if I understand. This formula looks at the “Date Submitted” field and calculates which weekday it was. The output is “In time” for Monday/Tuesday/Wednesday and “Overdue” for any other day:

SWITCH(WEEKDAY({Date Submitted}, "Monday"),
	0, "In time",
	1, "In time",
	2, "In time",
	"Overdue"
)

I’m not sure that’s what you’re asking for?

The problem with this is that if a client makes a submission on, let’s say a tuesday, Airtable won’t know if he/she submitted last week’s form (6 days overdue) or this week’s form (in time).

1 Like

Thank you so much for that formula! This looks like it’s almost exactly what I need. Is there a way for the formula to be based on a Monday of any given week instead of being based on the date submitted?

I planned on using filters to show the past week and past 2 weeks of submissions since those will be the only ones that matter.

I don’t understand the question. Could you elaborate? The formula can be based on any date you give it. What date would you like to use and what would you like the output of the formula to be?

Ok I’m struggling to articulate this lol

I want there to be a way for Airtable to know what the current day is automatically or what the start of any week is automatically populated.

I think I can explain it better with a video:

The video did not make it any clearer for me unfortunately :smiley: I want to know what you want it to say in the column “Week Start”. A date? If so, which date? Airtable does know what day it is today and that September 6th was a monday and so on.

EDIT: I watched the video again and I think I know what you mean. I’ll be back.

1 Like

Try this. Change “Date” to whatever date you want and the formula will return the monday of that week.

DATEADD(Date, -WEEKDAY(Date, "Monday"), 'days')
1 Like