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

Shauna_Renee · ‎Sep 21, 2021

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!

Shauna_Renee · ‎Sep 22, 2021

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.

Per_Carlen · ‎Sep 22, 2021

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?

Shauna_Renee · ‎Sep 22, 2021

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

Shauna_Renee · ‎Sep 22, 2021

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.

Per_Carlen · ‎Sep 22, 2021

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

Shauna_Renee · ‎Sep 23, 2021

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.

Per_Carlen · ‎Sep 23, 2021

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?

Shauna_Renee · ‎Sep 23, 2021

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:

Per_Carlen · ‎Sep 23, 2021

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.

Per_Carlen · ‎Sep 23, 2021

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