Automatically enter date at start of month

kss5229

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.

Genevieve P.

Hi @kss5229

Just jumping in here to help clarify! You're correct - there can be conditions before the Record a Date action, but that action is a terminal one. 🙂 Your first assumption is right.

What @mcarlson explained is also true - automations work on rows that currently exist with content in them. The 10+ completely empty rows at the bottom of your sheet data are not seen as active rows to add data into.

You would need to pre-populate a column with data in order for the automation to add a date into the row. E.g. a numbering column that you drag-fill down, or a column that has each month title listed.

So in this example, the "August" row is available to have a date recorded in the row, since the row exists:

But all the rows below, starting with row 9, have never housed any data so they are not recognized in a workflow.

I hope that helps explain!
Cheers,
Genevieve

mcarlson

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:

1. Add Placeholder Rows:
   • Create placeholder rows for upcoming months (e.g., for all of 2025), with empty values in the Start Date column.
2. 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 (or is not a date, if your test didn’t work with is blank).
     • Action: Use the "Record a date" action to populate the current date into the Start Date column of the placeholder row.

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:

1. 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.
2. 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.
3. 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.

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!

kss5229

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.

Georgie

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:

1. 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”.
2. 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:
   1. =IFERROR(IF(AND(DAY(TODAY()) = 1, MONTH(TODAY()) = MONTH([Start Date]1 + 1)), 1, 0), " ")
3. 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.
4. 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.
5. 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:

• TODAY Function
• Automatically update the TODAY function in formulas

Hope that helps!

Georgie

kss5229

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.

kss5229

Hi @Georgie,

I'm going back through to try implementing your solution alongside the resources you listed to get the today function to automatically update. I'm confused because the 2nd resource you list (Automatically update the TODAY function in formulas) does have a condition listed in the "Record a date" action. Shouldn't that work in my case then too?

Genevieve P.

Hi @kss5229

Just jumping in here to help clarify! You're correct - there can be conditions before the Record a Date action, but that action is a terminal one. 🙂 Your first assumption is right.

What @mcarlson explained is also true - automations work on rows that currently exist with content in them. The 10+ completely empty rows at the bottom of your sheet data are not seen as active rows to add data into.

You would need to pre-populate a column with data in order for the automation to add a date into the row. E.g. a numbering column that you drag-fill down, or a column that has each month title listed.

So in this example, the "August" row is available to have a date recorded in the row, since the row exists:

But all the rows below, starting with row 9, have never housed any data so they are not recognized in a workflow.

I hope that helps explain!
Cheers,
Genevieve