Using Automations to Add End of Month Date to New Row
I'm relatively new to Smartsheet. I have a sheet that I would like to use a workflow to automatically enter the date at the end of the month to the next row. So, on January 31, I would like the workflow to run and enter 1/31/24 in a date column on the next blank row. I've tried using the Record a Date action in a workflow, but it changes the date for all rows. Is there a way to do this?
Answers
-
Hi @JeremyS,
Will the calculated row always be the date at the end of the month for the date the row was created?
THanks,
John
-
John, if I'm understanding your question correctly, the workflow I was trying to setup would only run on the last day of the month, so yes, it should always be the end of the month date. Basically, each row in the sheet will have data entered from the end of the month by others. On the first of the month, they get a notification to go in and enter their data for the previous month. What I wanted to do was have the end of the month date already in the sheet on a new row, so they know where to enter their data.
Thanks
-
You would need to set up a second sheet to do this. First you would have a checkbox column with the box on row 1 manually checked. Then a date column populated by a record a date automation set to run daily at 12:00am with a condition of the checkbox being checked. Then you would need a flag type column with this formula in it.
=IF(TODAY() = IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, 1)
Finally you would set up a copy row automation set to copy the row when the flag column changes to "flagged".
-
Paul,
Thanks for the response. In your example, does the check box have to be manually checked each month? Or just initially?
Thanks!
-
Just initially. This will restrict the record a date automation to populating only a single row on the sheet instead of multiple rows which in turn could end up creating multiple copies.
-
What would the formula for the flag column be if I wanted this to run weekly?
I have the checkbox column, a week end date column, and the Week Flag column. I have automations setup to add the current date in the week end date column @ 7:00PM on Sundays. I can't seem to get the formula figured out to turn on the flag in the Week Flag column.
-
@bw1981 To run it weekly, you would just need to flag based on the WEEKDAY function of TODAY. You will still need to use the date type column with the daily updating Record A Date automation, but then the formula to flag would simply be
=IF(WEEKDAY(TODAY()) = #, 1)
Just change the # to whatever day number of the week you want it to flag for (Sunday is 1, Monday is 2, etc.).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!