Apr 07 2022 05:16 AM
Apr 07 2022 05:16 AM
I have a sort of a challenge for the Excel Experts, I think.
At my job people can work on their day off and depending on the requesting entity and depending on the time of day, they get paid different amounts.
So basically, there are 2 tables (1 and 2) for differenciating requesting entities and the time of day, which is divided in day time and night time.
Table 1 Day time Fee - 20€/Night time Fee - 35€
Table 2 Day time Fee - 15€/Night time Fee - 30€
What I want help with is creating an excel spreadsheet that would automatically calculate a fee, according to the cell colors so if it's blue it's always 20€ and if it's red it's always 35€ (not the real values) since there will be two spreadsheets for each table rotation, but both have to be in sequence.
Meaning, worker 2 will only be cast after worker 1 has been casted or if worker 1 is unavaiable. This means I will put the date in the cell so I don't cast the same worker for the same day and there will be times where some workers are cast several times before another one is due to unavailability.
When I put the date, I'm creating a sequence and I can see which workers have had more services than the other one, so I will start casting the one with the lesser amount first.
This rotation is done every 3 months, so there might be times where one worker during the first month will work 11 times and during the second month he will only work 4 times, which would mean it's balanced.
I think it's sort of clear of what I need for my job, but if you have any doubts, please do contact me. Thanks in advance!
Apr 07 2022 07:47 AM
You are describing a situation with multiple moving parts here. It would be good to break them apart and deal with them in sequence rather than trying verbally to describe the whole mess.
Let me suggest you start with the varying pay tables, Tables 1 and 2, day and night, and make that into a single table. And don't use color to differentiate; use a code of some kind. The color can be derived from the code, but Excel is far more facile at reading and interpreting codes--and then applying color--than it is at going in reverse...seeing color and knowing what it means. For you as a human, presuming you're not color blind, there's no difference....but we're asking Excel to do the work.
So that single table would look something like this:
I'm going to leave that for now and ask that you confirm that this makes sense.
Second, though, what do the entries in the red and blue cells mean? 03/abr etc. What does the "abr" refer to? Are there other codes that could appear?
In general, it's not a good idea to mix numeric and alpha characters in a single cell, especially if (as I think is the case) you're expecting mathematical calculations to be made based on the numbers. So if these are simply codes, that's fine, but if that means "3 hours ...." and the hours are going to be multiplied by the appropriate rate, then you'll need to find a different way to register that.
Let's leave aside for the moment the sequence of casting workers and just try to figure out how this pay process is toadministered.
Apr 08 2022 08:04 AM
Apr 08 2022 01:02 PM - edited Apr 08 2022 01:04 PM
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.
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.
Apr 08 2022 05:30 PM
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:
Apr 09 2022 05:05 AM
Apr 10 2022 03:48 AM
Apr 14 2022 08:03 AM
I'm attaching a spreadsheet in Excel where there are now two tabs, the first of which takes care of compensation calculations based on the days and shifts worked. I gave two ways to do that, one of which works with the entire list of shifts, the other with selected start and end dates. This also uses the FILTER function, which won't work in Google Sheets; it requires the most current version of Excel as well.
The second tab is the separate matter of scheduling, and I need to ask you to write out the rules in far more detail there. It may also be necessary to write a Power Query or macro/VBA routine, things that I do not know how to do. But let's begin with really clear rules. You'll see how I suggest you conceive of that in a text box on that second tab.
May I suggest that, when you send back a response, you try to attach your Excel sheet to the posting here on these boards. If that doesn't work, let me know and we'll try a different route.