Using Formula to Calculate Earnings

[Nancy_Cavillone]

I am using the Time Tracker block, and the Pivot Table block to track hours and total hours. Now, I need to calculate earnings based on the total hours. I’ve played around with a few formulas and field types and can’t get it QUITE right. I have my clients listed in a multiple select column. I need the total earnings to be calculated by client and I’m having trouble wrapping my head around how to set up the base to get to this calculation.

[Jeremy_Oglesby]

Hi @Nancy_Cavillones - welcome!

The first thing you’ll want to do is create a new Table for your Clients. Then, change that multi-select field where your Clients are listed into a linked-record field that is linked to the Clients table.

When you change the field type from multi-select to linked-record, your Client names won’t disappear, they will just be converted into new records(rows) in your Clients Table.

The effect this will have is that now each of your “Projects” or “Jobs”, or whatever you’ve called them, will be linked to a Client record in the Clients Table, and that Client record will serve as a spot for aggregating data from all the jobs linked to that Client. You can now use a rollup field in the Clients table to get a Sum of all the Hours worked for a particular client, or all the Earnings, or whatever other fields you may have that you’d like to see summed at the Client level.

If you get to some point in this and aren’t sure what to do or don’t see how it is supposed to be working, ping back and i’ll provide further help.

[Nancy_Cavillone]

Thank you! I got pretty far. The next thing I need to figure out is how to get calculate earnings based on the time tracked – basically, hours x rate = total earnings. Right now, it’s seeing the Duration format as a regular integer and returning HUGE numbers. I wish I earned that much… LOL

[Nancy_Cavillone]

I found this formula in the forum but it’s returning an error for me. ```
({Duration field}/3600) * {Currency field}

[Nancy_Cavillone]

I got it!! Thanks for your help!!

[Jeremy_Oglesby]

Glad to hear it, @Nancy_Cavillones. If you felt my response was helpful in addressing your main concern in this post, would you mind marking it as the Solution? That makes it easier for future forum visitors to find solutions to their problems on the forums.

[Jeremy_Oglesby · Accepted solution]

Looking at your screenshot here, @Nancy_Cavillones, and I’d suggest re-thinking putting the “Rate” on a “Client” record. The way you have it set up will make it impossible for you to do work for the same client at a different rate in the future. Any time you link a “To Do” to “Client A”, it’s going to use the same “Rate” value as its multiplier. If you change that “Rate” value for a particular client in the future, it’s going to affect the Rate multiplier for past jobs as well.

If the “Rate” charged can change based on the nature of a “To Do” for any given client, or for future work done for that client, then I’d suggest tying the “Rate” to a “To Do”, rather than to the “Client”. Then, either do your “Earnings” math in the “To Do” table, EDIT: or else pull your “Rate” into the “Clients” table from the “To Do” table via a Lookup. : on second thought, just do the “Earnings” math on a per To Do basis in the “To Do” table, and Sum the earnings for each “To Do” in a Rollup in the “Clients” table.

Hope that makes sense. This is coming from the perspective of someone who learned many of these lessons “the hard way” :slightly_smiling_face:

[Nancy_Cavillone]

Your reasoning makes sense. I work a flat rate per client, and I don’t charge by the task (To Do) but I see how it could be an issue when I raise my rates. Thanks for the suggestion!

[Nancy_Cavillone]

@Jeremy_Oglesby Is it possible for the value in the rate column to be conditional on the client, even if the rate is on a To Do record? For example, even though I have a flat rate per client, I have one client that not only has a different flat rate from my other clients, but two different rates :grimacing: That’s why I have her twice in my list - Alexa WPS and Alexa WIP KBP. I thought about writing a script, maybe an “If, Then” type of script but I’m not sure what I need to call. I’m researching it now but thought I’d throw it out there.

[Jeremy_Oglesby]

Well, this really depends on how robust you need the ability to vary your rates to be. If you tie up your rates in a formula, you have to be careful that when you want to update rates in the future, you modify the formula in such a way that it does not affect the calculation of past To Do’s (ie, a non-destructive modification). This can be a pain to do.

I’m gathering that you don’t want to manually enter rates on To Do’s – perhaps this is too onerous. I can think of two potential approaches to solving this problem, each of which is very different from the other. I’ll start writing out the first approach, which is possible to do in any Airtable workspace, “Free” or “Pro”, in a new post, while I await your response to this question…

Do you or will you use the “Pro” subscription, such that you will have access to Blocks, and particularly the Scripting Block, once it is no longer available to “Free” workspaces in September? I ask because approach number 2 would use the Scripting Block.