Is there a way to compare custom time periods for example Q1 2019 to Q1 2020? This would be very helpful! If not, is there a work around? I just want to be able to choose my time periods!
ohhh this is a great idea. I’d love an option to compare quarters, months, weeks.
Exactly -Right now I can’t compare monthly expenses from 2019 to 2020 to see comparison trends. It was also great having the pie graph visual to quickly and clearly see those changes from custom time periods.
Hi @rambruinllc and @susandennis ,
The Comparison report offers lots of pre-built time periods in cell B3 and C3 including:
This Month, Last Month, This Week, Last Week, This Year to Date, Last Year to Date, This Quarter, Last Quarter, Last Year
If you want to manually enter your own time period, change the dates in cell H13 and I13 for the 1st column and H14 and I14 for the 2nd column. If you make this change, the dropdown ranges won’t work anymore because you will have overridden the formula for them. You might also want to add the manual date range above the dropdown options, since they dropdown selection won’t be used anymore.
To get the sheet back to normal, you can reinstall it from the Tiller Money Labs Add-on.
Here’s more about the Comparision sheet just in case:
Jon
1 Like
Thanks, Jon. I have played around, broken, reinstalled, played some more with the Comparison report. I guess really what I’m looking for is the drop downs to include more options. Last month last year to last month this year, etc. Manually entering the time periods works for sure, when I don’t screw it up. And reinstalling to get the original back is a bit of a clunk.
I tailored Net Worth Comparison much the same way and now just do without the original since I can’t figure out how to have both.
Jon, could it be in the works to have the comparison report rejiggered around so the user could add specific date ranges, much like was done with the net worth tracker?
Susan, check out the net worth tracker if you have not already done so. It may help.
Thank you, Blake. Net Worth does not really do what I want and if I tailor it then I lose (or have to re-install) the original. Like the Net Worth Snap Shot and the Comparison sheet, I can either have my version or the original version but not both. I, of course, want both 
Got it…I meant to say net worth snapshot, not net worth tracker.
@susandennis,
Give this a try. If it works we might consider adding it to the existing template:
In B3 and C3, select Data → Data validation and add Custom with a comma before it to the list of options.
Select Row 12 and Insert 1 below, adding a new row.
In the hidden part, enter:
G12 = Custom Column B
G13 = Custom Column C
Put your custom start and end dates in H12, I12, H13, and I13.
Change the formulas in:
H14
=SWITCH(B3,"This Month",H3,"Last Month",H4,"This Week",H5,"Last Week",H6,"This Year to Date",H7,"Last Year to Date",H8,"This Quarter",H9,"Last Quarter",H10,"Last Year",H11,"Custom",H12)
I14
=SWITCH(B3,"This Month",I3,"Last Month",I4,"This Week",I5,"Last Week",I6,"This Year to Date",I7,"Last Year to Date",I8,"This Quarter",I9,"Last Quarter",I10,"Last Year",I11,"Custom",I12)
H15
=SWITCH(C3,"This Month",H3,"Last Month",H4,"This Week",H5,"Last Week",H6,"This Year to Date",H7,"Last Year to Date",H8,"This Quarter",H9,"Last Quarter",H10,"Last Year",H11,"Custom",H13)
I15
=SWITCH(C3,"This Month",I3,"Last Month",I4,"This Week",I5,"Last Week",I6,"This Year to Date",I7,"Last Year to Date",I8,"This Quarter",I9,"Last Quarter",I10,"Last Year",I11,"Custom",I13)
Now, when you select custom from the dropdown and custom dates in the hidden part, they will work. Plus you can also use the existing options.
One more change will help show the custom ranges in Row 2.
In B2:
=IF(B3="Custom",TEXT(H14,"m/d/y")&"-"&TEXT(I14,"m/d/y"),IFERROR(1/0))
In C2:
=IF(C3="Custom",TEXT(H15,"m/d/y")&"-"&TEXT(I15,"m/d/y"),IFERROR(1/0))
This approach worked for me. Hopefully it’s not too complicated. Let us know if this helps.
Jon
3 Likes
JON! This is perfect!! I now have January of 2020 lined up right next to January of 2021. What a beautiful picture that gives me!
It was just complicated enough. I did it wrong the first time but once I studied what you were doing, I easily figured out where my error was so that I could fix it and am now just a tiny bit spreadsheet smarter. Thank you so much for showing me how with such great detail.
Exceptionally cool.
3 Likes
@susandennis,
I’m very glad you say it’s perfect. Also glad I could help make you a bit more spreadsheet smarter.
1 Like
Please see below…
@jono ,
Here is what @susandennis , me, and others want but we do not know it yet.
I jazzed it up a bit based on my very basic spreadsheet skills. Users should do what you said above and then do what shows below.
- Add rows 1 and 2.
- Add what you see in cells A1, B1, C1, D1, A2, B2, C2, D2, A4, D4
- Change cell H14 to =B1
- Change cell I14 to =B2
- Change cell H15 to =C1
- Change cell I15 to =C2
In my example, I am comparing a one day period in 2020 to a two day period in 2021. This provides absolute customization without the need to get into the hidden columns on the right.
This should suffice until it gets added to the template.
Enjoy,
Blake
3 Likes
Nice work @Blake and thanks for sharing.
My suggestions were a quick fix to enable custom dates, but it had some flaws since it required the use of the hidden part of the sheet.
Jon
1 Like