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