Automated Workflow Monthly Archiving

AlexP

Hi everyone,

I am attempting to set up a specific automated workflow to perform archiving.

Normally this wouldn't be an issue however I'm using this function to try and have a chart on the dashboard to display the current months data and 11 months prior - so it is a "rolling" chart so to speak.

The nature of my current sheets means that for example, come first of January 2021, I want to move rows between first and last day of January 2020 into an archive sheet. Then come first of February 2021, rows between first and last day of February 2020 into an archive sheet, and so on.

I am using a date field to try and perform the conditional logic in the automated workflow section however finding that it just doesn't quite work right. See below for example. 365 days here is not really correct, it would need to be something closer to 335 days (30 days in a month) - the main issue here being the different months have different numbers of days. not to mention leap years.

It is critical in my case to move all the data from the month so that my chart doesn't show data for January across two different years, for instance.

Please advise if this is doable using automated workflows in this case or whether I need a different approach. Any comments appreciated.

Thanks,

Alex

MCorbin

This is a case where there might be an easier way, but here's how I would do it:

First: I have a sheet that lists the last day of every month. The formula I'm about to give you references this sheet:

Next, create a helper column in your data sheet. I made mine a checkbox, so my formula checks the box if the row has a month that's not in the last 12 months from today's date.... I called it "Archive" (You'll also see a Today column that I used to test the formula)

In the Archive Column, this is the formula I used:

=IF([Date Submitted]@row <= DATE(YEAR(TODAY() - 365), MONTH(TODAY()), INDEX({Last Day}, MATCH(MONTH(TODAY()), {Month No}, 0))), 1, 0)

{Last Day} refers to the Last Day of the month column in the first screen shot (my helper sheet). {Month No} refers to the Month Number column in that helper sheet.

You would then set your Archive automation to select any rows where the Archive column is checked.

MCorbin

This is a case where there might be an easier way, but here's how I would do it:

First: I have a sheet that lists the last day of every month. The formula I'm about to give you references this sheet:

Next, create a helper column in your data sheet. I made mine a checkbox, so my formula checks the box if the row has a month that's not in the last 12 months from today's date.... I called it "Archive" (You'll also see a Today column that I used to test the formula)

In the Archive Column, this is the formula I used:

=IF([Date Submitted]@row <= DATE(YEAR(TODAY() - 365), MONTH(TODAY()), INDEX({Last Day}, MATCH(MONTH(TODAY()), {Month No}, 0))), 1, 0)

{Last Day} refers to the Last Day of the month column in the first screen shot (my helper sheet). {Month No} refers to the Month Number column in that helper sheet.

You would then set your Archive automation to select any rows where the Archive column is checked.