Best Of

Thank you @Gillian C - thats a great idea on Option 1, i never thought of that! appreciate it!

Hi @KIMST

Great question! You're definitely on the right track for automating weekly and monthly reporting. Here's a way to do it using the Close Date column,

Step 1: Add helper checkbox columns

https://app.smartsheet.com/b/publish?EQBCT=596595fa4d484e62be27d195c0caa344

Add two new checkbox columns to your sheet:

• This Week
• This Month

Use these formulas:

This Week:

=IF(ISDATE([Close Date]@row), AND(WEEKNUMBER([Close Date]@row) = WEEKNUMBER(TODAY()), YEAR([Close Date]@row) = YEAR(TODAY())))

This Month:

=IF(ISDATE([Close Date]@row), AND(MONTH([Close Date]@row) = MONTH(TODAY()), YEAR([Close Date]@row) = YEAR(TODAY())))

These formulas check if a row has a valid Close Date and whether that date falls within the current week or month. They also handle year-end situations correctly.

Step 2: Create reports

Create two separate reports:

• One filtered where "This Week" is checked
• One filtered where "This Month" is checked

This way, only closed claims from this week or this month will appear.

Step 3: Add Report Summary fields

In each report, use the summary section to:

• Count the number of rows with a Close Date (this gives you the number of closed claims)
• Sum the Account Balance column (this gives you the total balance of those claims)

Step 4: Add reports to a dashboard

You can display the summaries or the full report using widgets in a dashboard for easy tracking at the end of each week or month.

https://app.smartsheet.com/b/publish?EQBCT=35b52ff612e84ad1b127e5412c806f9f

Hi,

Would it hinder your workflow to use the simple =[Task End Date]1 formula? When you drag it down, it continues down the column.

THanks