Pull unique dates, add a count, and group them by weeks

David Seijo

I have a project sheet with a series of dates of when tasks were completed. I need to build a dashboard to display how many tasks were completed per week. For example Nov 2 - 3 tasks, Nov 8 - 10 tasks, Nov 15 - 5 tasks

Is there a way to pull into a metric sheet the unique dates and then group them by weeks?

Genevieve P.

Hi @David Seijo

The way I would do this is to create a helper column in order to identify what Week the date is in, using WEEKNUMBER:

=WEEKNUMBER([Date Column]@row)

Once this is applied down your whole sheet in a Column Formula, then you can use this number to collect specific rows together.

To do this, I would create a Report based on this sheet, and use the GROUP feature to Group all the rows by this helper column that indicates the Week. Once you have your rows Grouped, you can then apply a SUMMARY to Count how many rows appear in each week/Group!

See: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

BethWork

@Genevieve P. I know this is a really old thread, but I was searching for this exact solution. This mostly works for my needs better than any solution I was trying. My only issue is that the week starts on a Monday and run throughs Sunday instead of starting on Sunday and running through Saturday. Do you know of another quick formula to accomplish that?

Genevieve P.

Hi @BethWork

Yes! Our weeks are Sunday - Saturday as well. Here's how I fixed that:

=IF(WEEKDAY([Date Column]@row) = 1, IFERROR(WEEKNUMBER([Date Column]@row) + 1, ""), IFERROR(WEEKNUMBER([Date Column]@row), ""))

Cheers,

Genevieve

BethWork

Hi @Genevieve P.

That's helpful! When I put it into my sheet it comes back as unparseable. I copied your formula, but updated the column name to reflect the name on my sheet. Is there something I need to change to make it work?

=IF(WEEKDAY([Install Date HW]@row) = 1, IFERROR(WEEKNUMBER([Install Date HW]@row) + 1, ""), IFERROR(WEEKNUMBER([Install Date HW]@row), "")))

Genevieve P.

Hey @BethWork

My apologies! The formula in my sheet had other content so the closing parentheses are 3 in mine ))) but should be 2 for yours ))

=IF(WEEKDAY([Install Date HW]@row) = 1, IFERROR(WEEKNUMBER([Install Date HW]@row) + 1, ""), IFERROR(WEEKNUMBER([Install Date HW]@row), ""))

BethWork

@Genevieve P. That's perfect! Worked like a charm. I appreciate your help.