Scheduled Monthly report of previous month's activity

Tim Meeks

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!

Brett Evans

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

J. Craig Williams

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

Tim Meeks

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!

J. Craig Williams

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