#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Scheduled Monthly report of previous month's activity

✭✭✭✭✭✭
edited 12/09/19

to all my formula gurus,

Need some help thinking this through.  I want to schedule a report that runs on the first of the month and pulls all data from a sheet where a "Closed Date" occured within the previous month.

So on Feb 1, the report would pull all activity in the month of January where the "Date Closed" occurred between 1/1/17 and 1/31/17.

I could have the criteria choose "WHEN" Date Closed is in the last 31 days. This would work for the 7 months where that have 31 days. But on the other 5 months, especially Feb, it's going to pull more days.

I was trying to figure out if there was a way to use the MONTH formula and identify the month the Case was closed and then reporting rows where the Previous Month (=MONTH-1) is 1 less than the current month?  The Current Month would be contained in a 2nd column.

Any (somewhat simple) ideas?   I may just go with the 31 day rule.....

thanks!

Tags:

• ✭✭✭✭✭✭

Hello Tim,

You would have to save the tartget sheet before running the report manuallyto make sure all that the value of Tdoay() is updated.  But the formula below would mark which rows to put on the report that are from last month.

=IF(MONTH([Case Closed]1) = MONTH(TODAY()) - 1, IF(YEAR([Case Closed]1) = YEAR(TODAY()), "on Report", "Do Not Report"), "Do Not Report")

• ✭✭✭✭✭✭

Tim,

You'll need to modify Brett's formula for items closed in December.

=IF(MONTH(TODAY()) = 1, IF(AND(MONTH([Case Closed]1) = 12, YEAR([Case Closed]1) = YEAR(TODAY()) - 1), "On Report", "Not On Report"), IF(AND(MONTH([Case Closed]1) = MONTH(TODAY()) - 1, YEAR([Case Closed]1) = YEAR(TODAY())), "On Report", "Not On Report"))

Craig

• ✭✭✭✭✭✭

Brett & Craig,

I knew I could count on you two!  that works, (of course)....

Now I just need to figure out how to get the user to do a manual save on the first day of hte month. I may use an API to write data to the sheet and save the sheet....

thanks again!

• ✭✭✭✭✭✭

Tim,

If it is just one sheet, it is pretty easy.

See this post from Rob Hagan for starters:

https://community.smartsheet.com/discussion/how-force-sheet-fully-recalculate-api

Craig

This discussion has been closed.