Forum Discussion
Creating an automatic fee calculator based on formulas
First, the table makes sense. We have 4 different outcomes so to speak.
Second, "abr" is short for "ABRIL" which is APRIL in portuguese, so I want to fill in the cells with the day they are cast for the work for personal management. So I only move to the next cell (from C2 to D2 when C2 is filled with a date, in this example) when the previous one has a date on it.
First, the table makes sense. We have 4 different outcomes so to speak.
Glad to hear that.
But I think the next part, where you enter the specifics about each of three workers, needs some clarification.
- Although you use color to differentiate between day and night, you don't give any indication in that part of your first sheet as to which of the two scales (each of which has its own day and night rate) is applicable. So I've created a different way to display the assignments, and strongly recommend that instead of using 03/abr to designate the 3rd of April, you use 4/3/22 (or 3/4/22, whichever is your country's normal way of displaying dates. That will enable easy data manipulation when we get to that stage of calculating pay for a given period.
- Second, it's not at all clear how you get from Worker 1's first four periods of night time work to the pay of 325 Euros. What was the mathematical calc there? Neither the 35Euro rate nor the 30Euro rate relates directly to 325....so there must be another unmentioned variable buried there--perhaps an hourly rate.
So I've created a working spreadsheet for you....please fill in the two columns as requested (or explain why something else is needed). I'm hoping you can post it back in this thread; if not, we may need to use OneDrive or GoogleDrive or SharePoint for you to post the revised file.
- Gonsa17Apr 09, 2022Copper Contributor
1. Oh I see, I see.
2. The total was made up. I just needed to demonstrate how i needed the table to be done. So I'll update the spreadsheet you gave me and explain it more thoroughly. But the hours don't really matter. We always get paid for 4 hour shifts, even if the work is done in 2 hours. If you do 2 shifts you get ahead of other workers for that amount.
And unfortunately I cannot share the excel document, so here is the link for the google drive doc:
https://docs.google.com/spreadsheets/d/15r_aoR8LadWILbPDL8xFnQUtYPU4ZUzk/edit?usp=sharing&ouid=104424707493914611539&rtpof=true&sd=true
- mathetesApr 09, 2022Silver ContributorI inserted a text box....basically asking for some more clarification on the pay rate to be assigned to each worker for each shift. I'm suggesting we delay the whole matter of seniority for the time being and get the other part resolved first. It's not just a matter of whether it was day or night, it's whether it was red_day or red_night or blue_day or blue-night. You can't just record day or night. Who gets assigned to the various days is a different matter. One thing at a time.
I'm going to be largely unavailable for the rest of the weekend....As time permits I'll try to check back, but don't expect immediate responses.- Gonsa17Apr 10, 2022Copper ContributorRight. I'm sorry again.
The pay rate will always be for 4 hour shifts. It's a set value which is the 20/35/15/30, depending on Red_D/Red_N/Blue_D/Blue_N.