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

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 :slightly_smiling_face:

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

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

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 :grinning_face_with_big_eyes: 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.

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

THANK YOU!!! It works!!! Thank you so much!!! I truly appreciate your help with this!!!

No problem. If I understand your need correctly I think you could just use the formula I gave you initially (pasted below). You want to know if the submission was overdue or not, right?

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

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

This doesn't work if there is a time in the date field