See Your Daily Spending as Hours of Work With This Simple Formula for Google Sheets
This simple formula for Google Sheets instantly translates the cost of goods you’ve purchased into the hours you worked to afford them.
A few years ago, I noticed a trending Reddit “LifePro Tip” challenging people to reimagine the price of items purchased (especially impulse items) into the hours spent working to afford them.
The idea isn’t to make you feel guilty, but rather to encourage mindful spending and intentional consumption.
Seeing the cost of everything you buy translated into the hours of your life exchanged to afford them instantly puts spending into perspective.
Henry Thoreau knew this 170 years ago:
“The cost of a thing is the amount of what I will call life which is required to be exchanged for it, immediately or in the long run.”
Thoreau, “Walden”
How to visualize your spending as hours of work
In Google Sheets powered by Tiller, a simple array formula can instantly convert your spending into hours of work based on your hourly pay.
Tiller founder Peter Polson outlines the steps in this older video on YouTube.
You can see how this works with your own hourly wage with this free demo version of the Tiller Foundation Template.
1 – In your Tiller-powered Google Sheet, navigate to your Transactions sheet:
2 – In the example Transactions sheet, add a new column between the “Amount” column and the “Account” column. The new column is column F:
3. In cell F1, add the array formula below:
The array formula is:
=arrayformula(if(row(E:E)=1,"Hours to Earn",(if((D:D)="Credit Card Payment","",(if((E:E)<0,(E:E)/-33,""))))))Paste the formula into cell F1:
To calculate your hourly wage, it’s best to use your after-tax take-home rate. In the formula above (circled in the image below), the hourly rate is $33.
Modify this number to reflect your actual after-tax earnings:
This array formula will ignore any payments categorized “Credit Card Payment,” in column D, as these are transfers and not expenditures.
It also looks in column E for any payments above one dollar. The array formula will fill the whole column below with data.
You may need to slightly modify the formula to work with your spreadsheet. For example, in your Tiller sheet, if your Amount is not in Column E you’d want to update the formula to reflect that. Same if your Categories aren’t in column D.
Share your thoughts in the Tiller Community
That’s it. It’s a simple yet powerful formula. We’d love to hear what you’ve created in your Tiller sheet that you think others might want to know about. Share your thoughts here in the Tiller Community!
.
This is very clever. I added it to my sheet.
Modified to show as # h : # m (hours : minutes) using custom number format
[h]" h : “m” m"
If you do this, you have to divide the result by 24 hrs first: see below from the example in the article.
Love that you are already hacking on this and making it better, @AHB.
This is awesome!
Arrayformulas don’t work with and/or statements afaik - so if you wanted to select ‘credit card payment’ or ‘amazon store card’, for example, then you’d need
=ARRAYFORMULA(if(row(E:E)=1,"Hours to Earn",if(if((G:G)="Credit Card Payment",1,0)+if((G:G)="Amazon Store Card",1,0)> 0,if((E:E)<0,(E:E)/-33,""),"")))And then you could continue to + IF statements for other ORs
=ARRAYFORMULA(if(row(E:E)=1,"Hours to Earn",if(if((G:G)="Credit Card Payment",1,0)+if((G:G)="Amazon Store Card",1,0)+(if((G:G)="Secondary Credit Card Payment",1,0)> 0,if((E:E)<0,(E:E)/-33,""),"")))AND statements get a * instead of a +
In my transactions I realized that I really don’t want to see ANY hours calculation for anything tagged “Transfer” (this covers all credit card payments, intra-account movement, dividend reinvestment, etc). I anticipated that I’d want to exclude other categories as well. Based on this I ended up changing that IF() to a SWITCH() looking at the G column (where my categories are set). Formatted for readability:
=arrayformula( if(row(D:D)=1,"Hours to Earn",( switch((G:G), "Transfer", "",( if((D:D)<0,(D:D)/-33,"") ) ) ) ) )But after making this change I realized that “Transfer” (so far) seems to cover everything I don’t care about. But if I DID want to start adding more exclusions from the G column I’d insert them after the second comma in
"Transfer", "",such as:=arrayformula( if(row(D:D)=1,"Hours to Earn",( switch((G:G), "Transfer", "", "OtherGroup", "", "ThirdGroup", "",( if((D:D)<0,(D:D)/-33,"") ) ) ) ) )If I wanted to stick with logical expressions, to check different columns, etc (as rory.kai.foulger is doing above) then I’d have considered switching from IF() to IFS() (essentially IF combined with SWITCH functionality). This would allow a more complex evaluation to be done.
I feel like if I wanted to spend more time on this then the hourly pay should just be consumed from a value entered into a cell somewhere, the exclusions list should consume a list into an array to be checked against, and the “Hours to earn” header calculation, the exclusions, and the money->hours calculation should all be split out as custom functions that are then strung together. I think this would do wonders for readability.
I did something similar. My Transaction sheet has a Group/Type columns so I just switched to “Transfer” in there.
I really like this calculation. It’s “simple” but it does show what is necessary. I can now also get more input if I want to buy a new “toy” and visualize how long it would take me to work for it, which is a great way to cut back on discretionary spending.