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.
Solved
Using Formula to Calculate Earnings
Best answer by Jeremy_Oglesby
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 
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:
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.
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
I found this formula in the forum but it’s returning an error for me. ```
({Duration field}/3600) * {Currency field}
I found this formula in the forum but it’s returning an error for me. ```
({Duration field}/3600) * {Currency field}
I got it!! Thanks for your help!!
I got it!! Thanks for your help!!
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.
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
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:
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:
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!
@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 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.
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.
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.
I do have a Pro account and I was playing around with the scripting block. I’m all ears…eyes…whatever.
As I started writing out the first approach I was going to suggest, I got half way through and remembered that the final steps of it, when I implemented it for a client once, required a Zapier integration. I don’t want to steer you that direction, and you’ve affirmed that you are using a “Pro” workspace, so I’ll go with that… more coming.
This approach would make use of a new Table where you define your “Rates”. Each time you need to define a new “Rate” for a “Client”, you’d create a new record in this table with the Rate value (dollar amount), a Time created field, and then a linked record field to link it to the Client that Rate is for.
In the Client’s table, you’d need a Rollup field that Rolls up this “Rate” linked field by the MAX({Time created}) – this should ensure that the “Client” record always reflects the most recent rate. You might have to do that Rollup to get the most recent “Created at” time, then pull that back into the “Rates” table with a Lookup, and in a Formula in the “Rates” table, check whether that Looked up MAX({Time created}) matches the {Time created} for the Rate record – and then, once again, use a Lookup in the Client’s table to pull that value in. Oy — it’s been a while since I did this and I’m remembering now how awkward it was to set up.
After all that hoopla :point_up:t2: (which you’ll only have to set up once), you can create new Rates, link them to Clients, and ensure that the Client always reflects the most recent Rate for the Client, while also retaining the historical Rates you’ve used.
Now we need to get that Rate into the To Do’s, and I’d suggest a Scripting Block for this - while JavaScript may not be your proficiency, it will prevent having to pull in any external services.
The Script will just need to grab all of your To Do’s that don’t yet have a “Rate” defined (this can happen by defining a View that only shows To Do’s where “Rate” is blank and querying that View, or else querying all To Do’s and filtering them in the script), and fill in the “Rate” with the value that is currently reflected in the Client’s “most recent” rate.
By doing this with a Rates Table and a Script, we avoid having the Rate field in the To Do’s be a dynamic formula – we don’t want it to be a dynamic formula because we don’t want the rate on past To Do’s to change (and change our record of billing). Formulas are, by their very nature, dynamic, so storing that data in a dynamic field can be dangerous.
With that set up, you just need to click the button to run the script, and it will fill in the Rate for you on any and all To Do’s that have a Client defined, but no Rate defined yet. You still have to intervene and click that button every now and then. Not quite as tedious as looking up rates and manually entering them on a per To Do, per Client basis, though.
I always get through these things and look back and realize just how much set up work I’ve suggested for you. And the cross-lookup thing to get the value of only the most recent Rate is a little complex and difficult to explain. I’m happy to help further and more directly with you – I’ll PM you about that.
This approach would make use of a new Table where you define your “Rates”. Each time you need to define a new “Rate” for a “Client”, you’d create a new record in this table with the Rate value (dollar amount), a Time created field, and then a linked record field to link it to the Client that Rate is for.
In the Client’s table, you’d need a Rollup field that Rolls up this “Rate” linked field by the MAX({Time created}) – this should ensure that the “Client” record always reflects the most recent rate. You might have to do that Rollup to get the most recent “Created at” time, then pull that back into the “Rates” table with a Lookup, and in a Formula in the “Rates” table, check whether that Looked up MAX({Time created}) matches the {Time created} for the Rate record – and then, once again, use a Lookup in the Client’s table to pull that value in. Oy — it’s been a while since I did this and I’m remembering now how awkward it was to set up.
After all that hoopla :point_up:t2: (which you’ll only have to set up once), you can create new Rates, link them to Clients, and ensure that the Client always reflects the most recent Rate for the Client, while also retaining the historical Rates you’ve used.
Now we need to get that Rate into the To Do’s, and I’d suggest a Scripting Block for this - while JavaScript may not be your proficiency, it will prevent having to pull in any external services.
The Script will just need to grab all of your To Do’s that don’t yet have a “Rate” defined (this can happen by defining a View that only shows To Do’s where “Rate” is blank and querying that View, or else querying all To Do’s and filtering them in the script), and fill in the “Rate” with the value that is currently reflected in the Client’s “most recent” rate.
By doing this with a Rates Table and a Script, we avoid having the Rate field in the To Do’s be a dynamic formula – we don’t want it to be a dynamic formula because we don’t want the rate on past To Do’s to change (and change our record of billing). Formulas are, by their very nature, dynamic, so storing that data in a dynamic field can be dangerous.
With that set up, you just need to click the button to run the script, and it will fill in the Rate for you on any and all To Do’s that have a Client defined, but no Rate defined yet. You still have to intervene and click that button every now and then. Not quite as tedious as looking up rates and manually entering them on a per To Do, per Client basis, though.
I always get through these things and look back and realize just how much set up work I’ve suggested for you. And the cross-lookup thing to get the value of only the most recent Rate is a little complex and difficult to explain. I’m happy to help further and more directly with you – I’ll PM you about that.
Oy is right but thank you so much. I’m going to get this running. This makes sense to me on the surface and will make more sense once I get in there and start doing it. I’ll report back.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.