Automatically enter date at start of month
Hello,
I have a Smartsheet that I'm using to calculate monthly metrics. I would like the sheet to auto update as time passes to add a new line for the new month. The sheet currently has a column titled "Start Date" and the first 10 rows of the sheet are the first day of the month from Jan - Oct 2024.
The automation I have currently set up is:
Trigger: when a date is reached. Every month on day 1 starting on 11/01/24 at 12:00am.
Condition: where Start Date is not a date (I've also tried "Is blank")
Action: Record a date in Start Date
The workflow says it ran, but it never seems to enter a date into the next blank line.
Answers
-
It sounds like you’re almost there, but Smartsheet automation has some limitations when it comes to adding new rows or entering data in specific blank cells based on triggers. Automation workflows can’t directly add new rows or dynamically enter data in the next available row. However, here’s an approach to achieve this monthly row addition with a workaround:
Option 1: Pre-Populate Rows for Future Months
One workaround is to pre-populate rows for future months in advance. Here’s how:
- Add Placeholder Rows:
- Create placeholder rows for upcoming months (e.g., for all of 2025), with empty values in the
Start Date
column.
- Create placeholder rows for upcoming months (e.g., for all of 2025), with empty values in the
- Use Automation to Fill in Dates as Each Month Starts:
- Set up an automation to update the
Start Date
column on the first day of each month. - Update your current automation as follows:
- Trigger: When a date is reached. Set it to trigger every month on day 1 at 12:00 am.
- Condition: Check if the
Start Date
is blank (oris not a date
, if your test didn’t work withis blank
). - Action: Use the "Record a date" action to populate the current date into the
Start Date
column of the placeholder row.
- Set up an automation to update the
By setting up placeholder rows in advance, the automation will populate each new month’s row without the need for dynamic row creation.
Option 2: Use a Helper Sheet with Automated Copy Row Workflow
If pre-populating rows isn’t ideal, you can create a helper sheet to trigger the addition of new rows each month:
- Set Up a Helper Sheet:
- Create a simple helper sheet with one column (
Trigger Date
) and set a single row with the date for the first day of the next month.
- Create a simple helper sheet with one column (
- Set Up an Automation in the Helper Sheet:
- Create an automation on the helper sheet to run every month on the 1st, setting the
Trigger Date
to update automatically.
- Create an automation on the helper sheet to run every month on the 1st, setting the
- Use a Copy Row Workflow:
- In the helper sheet, create an automation to copy a row to your main sheet whenever the
Trigger Date
is updated. - In your main sheet, set up default values (like a blank row) for new rows so they can be updated as needed.
- In the helper sheet, create an automation to copy a row to your main sheet whenever the
With either of these methods, you can effectively achieve automated monthly row additions for metrics tracking.
Please let me know if you need any more assistance!
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
- Add Placeholder Rows:
-
I'd rather not create a helper sheet if I can avoid it to keep it as clean/simple as possible.
For Option #2, do these not already count as blank/placeholder rows? I'm not understanding how these are different than me creating placeholder rows.
-
Hi @kss5229,
The Record a Date action doesn’t accept any conditions in the workflow - this is documented in this help article: Work with the Record a date action.
As an alternative solution, I’d do the following:
- Create a helper checkbox column on your sheet that can be used as a trigger for the workflow. I’ve called mine “1st of month”.
- In that column, we can use a formula to check if the date each day is the first of the month. However, we also need to make sure it only gets checked when the month of the current date is the month after the last date in the Start Date column. To do so, you could enter the following formula in row 2:
- =IFERROR(IF(AND(DAY(TODAY()) = 1, MONTH(TODAY()) = MONTH([Start Date]1 + 1)), 1, 0), " ")
- Then, you can pull this formula down to the other cells in the checkbox column - this will automatically change the cell reference for each row, so that row 3's formula will reference the Start Date in row 2, and so on. You won’t be able to convert this to a column formula since we need to reference the cell in the previous row each time. Having this formula means that when we reach the first date of the next month, (eg, when we get to 1st December), the checkbox for the latest row will be checked.
- Edit your workflow and remove the condition block, and set your trigger to “When rows are added or changed” and when “1st of month” (or your checkbox column name) changes to Checked.
- Assuming your action block is set to record the date in the Start Date column, save your workflow.
Your workflow will then trigger on the start of each month, and the date will be recorded for the new row on the 1st of every month.
You’ll need to ensure that you use the sheet each day in order to make sure that the TODAY function updates, but you can also set this to update automatically if the sheet isn’t used every day - check out the following resources for more on this:
Hope that helps!
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Georgie,
I'd looked at that link before trying to figure out why it isn't working, but I had interpreted the detail about no other actions/conditions to mean that no other actions or conditions could go after the "Record a date" action - since when it talks about that it's referring to it being a "terminal action". But it seems like I must have just misunderstood it…
I'm trying to avoid creating more helper columns since my smartsheet is already quite large, but I might have to do what you recommend if I can't figure anything else out.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!