need to update report to add automatic month update.

quality.doc

I have a row report that is currently showing the current month's data and I would like to know if I can set up a formula or sort setting to change the month to current month when the month changes. currently i'm just using the simple filter but need to update each month.

Paul Newcome

You would need to put a helper column in the source sheet(s) that has a formula to output something specific such as checking a box on each row where the date is part of the current month based on today's date. Then your report filter will look for rows that are checked.

The formula for checking a box in a column would be something along the lines of…

=IF(AND(MONTH([Date Column Name]@row) = MONTH(TODAY()), YEAR([Date Column Name]@row) = YEAR(TODAY())), 1)

Adam Murphy

There may be better/easier method but I solved this by creating "relative date" helper columns in my sheets. I do this for week, month, quarter and year for certain date fields, and have the results in the helper field be either "This Week/Month/Quarter/Year", "Last Week/Month/Quarter/Year", "Older" or "Out of Range".

For the Relative Month, here is the formula I use: =IFERROR(IF(DATE(YEAR([Date]@row), MONTH([Date]@row)) = DATE(YEAR(TODAY()), MONTH(TODAY())), "This Month", IF(MONTH(TODAY()) = 1, IF(DATE(YEAR([Date]@row), MONTH([Date]@row)) = DATE(YEAR(TODAY()) - 1, 12), "Last Month", "Older"), IF(DATE(YEAR([Date]@row), MONTH([Date]@row)) = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1), "Last Month", "Out of Range"))), "")

I then use this relative date helper field in report filter where that field = "This Month". Hope this helps!

quality.doc

the formula works but how do i make it appear in each new row if new entries are from a form.

Paul Newcome

You would set it as a column formula.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

.