Hello.
I'd like to know if it's possible to extract tiome from a fórmula.
I'll try to explain:
I have a formula wich I get the total hours in a day, night hours based on a specific time, and the amount of hours in the day as normal hour so to speak.
the formula I have is this one:
with this for the rest:
And this one
That formula gets me the amount of time and extra hours on those 24 hours.
what I'd liiek to know if it's possible to make it "extract" 8 hours from whatever start hour I start to work.
exemple on this table:
The table, after 8 working hours the percentage is 50% higher day extra hour or 25% higher if it's night shift. If it's night shift it has an extra 75% if it's extra hour (after 8+ hours worked)
edit: I forgot to mention that the night shift starts at 21:00 untill 06:00.
I'm sorry if it's confusing, but I'm having a hard time trying to figure how this might work.
I tried making the calculation manual, but then I get stuck since I cant get the right times needed for each percentage based oh the amount of hours.
And, I dont even know if it's possible to achieve this.
Either way, thanks in advance if someone is whilling to help me in a way of doing it.
eLy
I'd like to know if it's possible to extract tiome from a fórmula.
I'll try to explain:
I have a formula wich I get the total hours in a day, night hours based on a specific time, and the amount of hours in the day as normal hour so to speak.
the formula I have is this one:
Excel Formula:
=(($C40>$D40)*MED(0;$D40-$AP$19;MED($AP$19;$AP$18))+MÁXIMO(0;MÍNIMO($AP$18;$D40+($C40>$D40))-MÁXIMO(1/4;$C40)))
with this for the rest:
Excel Formula:
=RESTO(D40-C40;1)
And this one
Excel Formula:
=O40-P40
That formula gets me the amount of time and extra hours on those 24 hours.
what I'd liiek to know if it's possible to make it "extract" 8 hours from whatever start hour I start to work.
exemple on this table:
work start time | 08:00 | 09:00 | 10:00 | 11:00 | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | ||
| 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 7,49 € | 7,49 € | 7,49 € | 7,49 € | 74,87 € | |
| | | | | | | | | | 50% | 50% | 50% | 50% | ||
| | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
work start time | 20:00 | 21:00 | 22:00 | 23:00 | 00:00 | 01:00 | 02:00 | 03:00 | 04:00 | 05:00 | 06:00 | 07:00 | 08:00 | ||
| 4,99 € | 6,24 € | 6,24 € | 6,24 € | 6,24 € | 6,24 € | 6,24 € | 6,24 € | 6,24 € | 9,98 € | 9,98 € | 7,49 € | 7,49 € | 89,85 € | |
| | | | | | | | | | 75%+25% | 75%+25% | 50% | 50% | ||
| | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
work start time | 12:00 | 13:00 | 14:00 | 15:00 | 16:00 | 17:00 | 18:00 | 19:00 | 20:00 | 21:00 | 22:00 | 23:00 | 00:00 | ||
Regular hour price | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 4,99 € | 9,98 € | 9,98 € | 9,98 € | 9,98 € | 84,83 € | |
Percentage based on hours worked (8+) | | | | | | | | | | 75%+25% | 75%+25% | 75%+25% | 75%+25% | ||
Number of hours worked | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
The table, after 8 working hours the percentage is 50% higher day extra hour or 25% higher if it's night shift. If it's night shift it has an extra 75% if it's extra hour (after 8+ hours worked)
edit: I forgot to mention that the night shift starts at 21:00 untill 06:00.
I'm sorry if it's confusing, but I'm having a hard time trying to figure how this might work.
I tried making the calculation manual, but then I get stuck since I cant get the right times needed for each percentage based oh the amount of hours.
And, I dont even know if it's possible to achieve this.
Either way, thanks in advance if someone is whilling to help me in a way of doing it.
eLy