Calculate Week number from multiple choice dates column

igors

Hi,

We are running a Wellness challenge for a charity. Participants log the days and duration when they performed the activity.

We allow selecting multiple dates for the same activity.

I try to total Duration for a week. "Duration" mean the number of hours for each of the selected days.

First, I need to convert a date into a Week. How can I do it considering that dates are in a multi-select column?

Thanks in advance.

Paul Newcome

If you just need to multiply the duration by the number of dates selected then you could use something like this:

=[Duration (Hours)]@row * COUNTM([Dates Completed]@row)

igors

Hi Paul,

Thank you! It will allow us to calculate the total hours.

But we have another problem: I would like to calculate how many hours Per Week each participant accumulated. I need to convert each date into a Week and then total the hours per person.

How can I extract individual dates from the Multiple Select column?

Then, I can just total hours per week per person.

Paul Newcome

Extracting the dates can be a little burdensome. To start, you will need a date type column for as many possible dates as will be selected for a single entry. So if someone can select no more than 10 dates, you will need 10 additional columns. Then we would need to put together a rather complex formula to parse out the selections and then convert them into actual dates. It is possible, but it is a fair amount of work. I'd be happy to help with the formulas if you are wanting to go down that route.

The absolute easiest way to solve both problems... Have the users fill out a single form for each date/entry instead of lumping a bunch of text values that look like dates together and then trying to parse them out.

igors

Hi Paul,

We decided to allow users to enter multiple dates in a Sheet instead of a Form for two customer service reasons:

1. Users can do it once per week and report multiple daily activities on one line instead of filling the Form seven times.
2. Users can see how many other people are doing it and it will motivate them to do it too.

I like the idea of creating seven columns. One for each day. And then extracting each day from multi-select into each of the columns.

If someone enters more than seven days, I will manually split it into two records with seven or fewer entries.

How can we write a formula to extract days into a separate columns?

TIA