Automate change of month on reports

Garmon D

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

Paul Newcome

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.

Garmon D

Thanks Paul, that's a really neat way of going about it. I'll give it a spin.

 

regards,

 

Garmon

Paul Newcome

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.

Megan Harry

@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!

Paul Newcome

@Megan Harry Try something along these lines...

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

Megan Harry

@Paul Newcome

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!

Paul Newcome

@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.

Megan Harry

@Paul Newcome

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!

Paul Newcome

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)

Megan Harry

@Paul Newcome

Thank you Paul! This worked perfectly. Both of these formulas will be super helpful.

Paul Newcome

Happy to help. 👍️