See Your Daily Spending as Hours of Work With This Simple Formula for Google Sheets

Originally published at: See Your Spending As Hours Of Work With This Simple Formula

This simple formula for Google Sheets instantly translates the cost of everything you buy into the hours you worked to pay for them.

2 Likes

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.

=arrayformula(if(row(E:E)=1,"Hours to Earn",(if((D:D)="Credit Card Payment","",(if((E:E)<0,(E:E)/-33/24,""))))))
1 Like

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.

Interested in using this but it doesn’t work for me for some reason. My columns are set up correctly according to the original formula and I just get a REF error. Category is Column D and Amount is Column E. Any thoughts for the group?

edit: Found the issue…was putting formula in F2 and not F1. Goodness, I am not bringing my A game lately. :face_with_diagonal_mouth:

Glad you figured it out @YouBet96. :ninja: