Calc. 'Week Of' Date Based on Different Survey Submission Dates

[Nickolas]

Hello,

I'm looking to calculate a 'Week of' or 'Week Ending' date based on the completion dates from a Microsoft Forms survey. The survey is completed on a weekly basis but on different days. I want to group those weekly responses into one 'Week of' or 'Week Ending' category for purposes of summarizing the data.

For example, our most recent survey was completed for the week ending 4/10. People completed the survey over the course of several days ranging from 4/10-4/14.

Our next survey will be for this week ending 4/17, so I want the formula to be dynamic on both a historical and go-forward basis.

Thanks for the help!

Nick

[Maskell_Rascal]

Hi @Nickolas 

If its safe to assume that the surveys will always take place on or after the week ending date, but before the week ending date of the following week, I have a solution that should work for you.

I'm using a DateTime tool to create a column to identify the day of the week. I then use the Formula and Multi-Row Formula tools to populate the Week Ending date for each survey completion. 

Let me know if this works for you.

Thanks!

Phil

[Nickolas]

Thanks, Phil.

You are correct that surveys will always start on Friday (week ending date) and responses are completed before the following week Friday/next week ending date.

What you shared is close and something I might be able to build upon. Unfortunately, there are weeks where we don't always have someone complete the survey on a Friday so there are gaps in the sequence using the Multi-Row Formula tool.

Nick

[Maskell_Rascal · Accepted solution]

Hi @Nickolas 

In that case, here is a simpler solution, albeit more complex formula. This is calculating the previous Friday for every date in the data series, unless the date is a Friday.