Has anyone created an automation to capture open items by month?

Darla Brown

We need to report on the items that are unbilled at month's end. The person responsible for this has now left the company and I've been tasked in the interim. I can see this was manually done but they want it automated.

What formula would I use to look at the current sheet and see what was still open on May 31st? And then record that for each month's end moving forward without manual intervention?

Paul Newcome

How exactly would you want it to adjust and operate? We are in June now. A row that was still open on 31 May is flagged for purposes of reporting. It then gets closed. Would you want it still flagged in July when we are evaluating for June dates? Do you have a column with a date for when it is supposed to be closed by or one when it is actually closed?

Darla Brown

I do have an Unbilled Pivot that totals by aging status. The previous manager was manually typing in those totals into an excel sheet. She then dropped that sheet as an attachment into a Smartsheet replication and let Shuttle update. 🤨 A report on a dashboard was then updated.

I thought about creating a workflow that on the last day of the month would copy the rows on the pivot into a metric sheet with a modified date column. I could then at least capture an historical count. Possibly??

Paul Newcome

So basically you are just looking for the total of those that were not closed before the last day of the previous month? Do you have a closed date? Are you able to provide screenshots for context?

Darla Brown

Exactly.

On the source sheet, we have a Sent Date column which indicates the bill was completed. An automation is built in to move that row to a Completed sheet once a date is entered.

This is the pivot that totals what is currently unbilled by aging

I need to capture those numbers on the last day of each month.

Paul Newcome

Do you need to keep previous months to show trends, or are you just needing "last month's"?

Darla Brown

They have another document for trends. If I captured the month-end totals, I can link it to that sheet.

Paul Newcome

If you just need "last month's" then I would suggest using the copy row automation to push to another sheet on a monthly basis. Insert a Created (date) system generated column on the copy sheet only. Then you can insert a checkbox column to check off rows where the created date is last month.

=IF(AND(MONTH(Created@row) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR(Created@row) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)

Darla Brown

Got it! That's the direction I was leaning, so thanks for the confirmation!

Thanks, as always.

Paul Newcome

Happy to help. 👍️ (as always haha)