I have a number of reports that calulate variously monthly totals that are used to feed into dashboards. I currently need to change the month it reports on manually every month - is there any way to automate this change?
You could automate this through the sheet(s).
Something along the lines of a checkbox column with a basic
=IF(MONTH([Date Column Name]@row) = MONTH(TODAY()), 1)
This will check the box for any date that has the same month as the current date. You could then set your report up to pull based on that box being checked.
Of course the TODAY() function will only update to the current date if the sheet is activated, so it will need to be opened at least once a month for this to work.
Thanks Paul, that's a really neat way of going about it. I'll give it a spin.
Happy to help!
I needed this for a reporting template for my team. Multiple projects were displaying the same type of information in the same layout and pulling the same type of information for reporting. The problem was... One project needed the last 2 weeks. Another needed the past month. Some others needed year to date. Even had a few month to date reports.
Because the timeline for the reporting could change with each project, I basically included a few dropdowns for the requirements and built an IF statement to check boxes in a helper column based on the dropdown selections.
Then all I had to do was build the report to pull based on that box being checked.
That way we could easily "Save as New" and set our reporting requirements quickly and easily for each project without having to make a bunch of changes to individualize each one.
I used your formula above for one of my reports, and it worked exactly as it should have but then I realized I have prior year's information in my sheet. I want it to check the current month, and not check the past year's month with it. Is there a way to do the formula and have it only pull the current year's date?
@Megan Harry Try something along these lines...
=IF(AND(MONTH([Date Column Name]@row) = MONTH(TODAY()), YEAR([Date Column]@row) = YEAR(TODAY())), 1)
Thank you! This worked perfectly. I am super excited about this formula I think I will utilize it a lot moving forward. Thank you for the help!
@Megan Harry Happy to help. 👍️
I personally use similar combinations a lot when pulling metrics because that same combination can be used in cross sheet references an in numerous other functions.
I have been playing around with this and had a new question. What if I were want the prior month's information in my report. So say it is currently April, but I want to view March's metrics for reporting reasons and look at the month's totals. Would I add onto this existing formula?
Thank you for the help!
That gets a little bit trickier. Try something like this...
=IF(AND(MONTH([Date Column Name]@row) = MONTH(TODAY()) + IF(MONTH(TODAY()) = 1, 11, -1), YEAR([Date Column]@row) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)
Thank you Paul! This worked perfectly. Both of these formulas will be super helpful.
Happy to help. 👍️
Looking to connect a dynamic view of HR dataset to a Power BI dashboard pulling information from multiple other sources. Appreciate any guidance, thanks
Hello! I copied a form I had made for a similar event I am working on now. I made minor adjustments to the form to match the needs of the event. I distributed the form for people to complete, so the form is active, but the data is not populating onto my Smartsheet. Please advise. I would prefer not to have people redo the…
Hello, when I modify a start date on a task I lose the predecessor links. Is there a method to modify task start dates without losing the links? THANKS.
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.