Has anyone created an automation to capture open items by month?
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?
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
Answers
-
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?
-
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??
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
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?
-
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.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Do you need to keep previous months to show trends, or are you just needing "last month's"?
-
They have another document for trends. If I captured the month-end totals, I can link it to that sheet.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
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)
-
Got it! That's the direction I was leaning, so thanks for the confirmation!
Thanks, as always.
Darla Brown
What you meditate on, you empower!
Overachiever - Core Product Certified - Mobilizer - EAP
-
Happy to help. 👍️ (as always haha)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!