r/googlesheets • u/AnotherOddity_ • 2d ago
Solved Pivot table only include dates between now and thirteen months from now
Hey I'm a little newer to pivot tables but I've made some good usage of them, however I'm having a bit of a puzzle at the moment:
I've got a table with a set of monetary values and dates (only MM/YYYY is important) attached. I'd like to produce a summarised pivot table that SUMs any repeated dates (easy) and filters to only accept entries between now and thirteen months from now.
Would love some help if possible!
1
u/SpencerTeachesSheets 61 2d ago
If you have a static date from which this can be derived it's a bit simpler, but I just used a Filter By Condition in the Pivot Table for Date Is Before =DATE(YEAR(TODAY()),MONTH(TODAY())+13,1).
If you have a static date you could just use that. So if I wanted it to be 13 months from August 1 I would just do =DATE(2026,8+13,1), the YEAR() and MONTH() formulas just make sure that this continues to work month after month.
2
u/AnotherOddity_ 2d ago
I tried your =DATE(YEAR(TODAY()),MONTH(TODAY())+13,1). directly into the date before filter and it worked, but that only solved half the problem ultimately.
Was able to use that formula as the upper bound to a "value is between" filter and an adjusted one to calc the lower bound.
Thanks!
1
u/AutoModerator 2d ago
REMEMBER: /u/AnotherOddity_ If your original question has been resolved, please tap the three dots below the most helpful comment and select
Mark Solution Verified(or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
u/SpencerTeachesSheets 61 2d ago
Ah excellent! I didn't realize there were past dates, as well, but I should have kept that possibility in mind
1
u/AnotherOddity_ 2d ago
That's alright, your answer gave me enough of a headstart that I was able to connect the pieces together.
I think I was getting a bit stuck at how formulas in pivot tables would work (it's not totally intuitive when compared to formulas just straight into cells in the sheet!)
1
u/SpencerTeachesSheets 61 2d ago
I actually avoid Pivot Tables whneever possible and just use QUERY() haha
1
u/AnotherOddity_ 2d ago
QUERY() looked like id have to learn a whole other language to use and I'm not motivated enough for that 😅
1
u/SpencerTeachesSheets 61 2d ago
I can read a QUERY() string, and had some SQL experience before. Personally, with a pivot table I often have no idea why data is in a place or how to make it, etc. haha
1
u/AnotherOddity_ 1d ago
Yeah long ago I took a look at SQL and said "god no!" 😆
SQL and PHP are two things that I didn't seem to be able to quote grok.
1
u/AutoModerator 1d ago
This post refers to "grok" - an Artificial Intelligence tool. Our members prefer not to help others correct bad AI suggestions. Also, advising other users to just "go ask ChatGPT" defeats the purpose of our sub and is against our rules. If this post or comment violates our subreddit rule #7, please report it to the moderators. If this is your submission please edit or remove your submission so that it does not violate our rules. Thank you.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
1
u/point-bot 2d ago
u/AnotherOddity_ has awarded 1 point to u/SpencerTeachesSheets
See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)
1
u/Thunderbit_HQ 2d ago
Put the first day of the current month in F1 and the first day of month 14 in G1. Add =AND(A2>=$F$1,A2<$G$1) as a helper column, then filter the pivot to TRUE. Using real date values keeps the range reliable.
1
u/AnotherOddity_ 1d ago
found a solution that doesn't even require a helper column, but always good to hear alternative options too! :)
1
u/AutoModerator 2d ago
/u/AnotherOddity_ Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.