Creating an automatic fee calculator based on formulas

[Gonsa17]

Hello everyone.

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.

Example:

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!

[#2]

@Gonsa17 

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:

Situation	DayNight	Rate
1	D	20
1	N	35
2	D	15
2	N	30

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.

[#3]

Well I might've got ahead of myself. Sorry!
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.

[#4]

@Gonsa17 

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. 

1. 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.
2. 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.

[#5]

@mathetes 

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=10442...

[#6]

I 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.

[#7]

Right. 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.

[#8]

@Gonsa17 

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.