Using Automations to Add End of Month Date to New Row

JeremyS

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?

John_Foster

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

JeremyS

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

Paul Newcome

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".

JeremyS

Paul,

Thanks for the response. In your example, does the check box have to be manually checked each month? Or just initially?

Thanks!

Paul Newcome

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.

bw1981

https://community.smartsheet.com/discussion/comment/418107#Comment_418107

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.

Paul Newcome

@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.).