Sign in to join the conversation:
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?
Garmon
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.
regards,
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.
@Paul Newcome
Hi Paul!
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?
Thank you!
@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.
Hi Paul,
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. 👍️
I'm receiving this message every time I try to open my sheets. I'm the only admin user. I have already performed proper troubleshooting, such as clearing the cache, logging in incognito, and logging out and back in, but the issue remains unresolved. It's been a few days, and I'm unable to track our projects and this…
I’ve created an automation that generates a document and forwards it to the individual specified in a cell. The line item also has several additional attachments associated with it. Is there a way to include those attachments with the generated document?
I have an automation set up that copies a row to another sheet each day. The automation itself works correctly, but the problem is that it copies the formulas rather than the values, which causes the formulas to break and return errors. How can I configure it to copy only the values instead of the formulas?
