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

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

Date Time

Help

Accepted answers

Maskell_Rascal

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.

Find Week Ending Date for Survey V2.yxmd

All comments

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

Find Week Ending Date for Survey.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703