Extract time from formula

[elynoy]

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:

=(($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:

=RESTO(D40-C40;1)

And this one

=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​

Regular hour price

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 €

Percentage based on hours worked (8+)

​

​

​

​

​

​

​

​

​

50%​

50%​

50%​

50%​

Number of hours worked

​

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​

Regular hour price

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 €

Percentage based on hours worked (8+)

​

​

​

​

​

​

​

​

​

75%+25%​

75%+25%​

50%​

50%​

Number of hours worked

​

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

 

[rlv01]

if it's possible to make it "extract" 8 hours from whatever start hour I start to work.

I have no idea what this means. I think maybe you are including too much information not relvant to your basic problem. If you start work at 22:00 hours what numerical value represents an 8 hour 'extraction'?

 

[elynoy]

Sorry, i didnt explain it right.

If i start as 08:00 and end at 20:00 its 12 hours of work. I need it to extract 8 hours from thise 12 and count the 4 left as extra hours of work.

The fórmula I have I get that right, but if I start work at 22:00 and end 12 hours later, at 10:00, I need it to count as night shift after 21:00 wich is 25% extra value per hour, and From those 12 hours, I need to get 8 working hours as normal price and the other 4 as extra. And if the extra 4 are between 21:00 and 06:00 its 75%+25% (night shift is 25% and extra hour as night shift is 75% extra)

Maybe now the table a obove will be more helpfull.

Thank you
Ely

 

[elynoy]

I tried to explain it better in the table below.

Testes
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
5	Entry time (start work at)	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
6	Price for each hour	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 €
7	Extra percentage										50%	50%	50%	50%
8	Hour count		1	2	3	4	5	6	7	8	9	10	11	12
9
10			Regular Hours of the day shift wich is 8 hours of work and the price for those regular 8 hours of work wich is 4,99€								Extra hours of work after the other 8 wich totals 12 hours of work. Since it's day shift the price for the hour increases 50%, so these 4 cost 4,99+50% wich is 7,49€ each hour.
11
12
13	The formula I have I get the data on the table correct. The formula counts the total amount of hours worked, wich is 12, and I get those 4 as extra hours with the correct percentage for the payment.
14
15	The problem I'm having is on this table below:
16
17	Entry time (start work at)	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
18	Price for each hour	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 €
19	Extra percentage										75%+25%	75%+25%	50%	50%
20	Hour count		1	2	3	4	5	6	7	8	9	10	11	12
21			First, between 21:00 and 06:00, it's considered night hours wich cost 25% more over the 4,99€. So, 4,99+25% equals 6,24€.								These 4 hours are considered extra hours since I worked 8 already. And since it's night shift wich started at 21:00, it's the normal 25% over the regular price, and since it's extra, it's extra 75% over the 4,99 so in the end the price is 9,98€ p/ hour. Yet again, since the night shift ends at 06:00, the price falls back to only 50% extra since it's "day time" again.
22
23
24	So, in the end, I'd like to know if it's possible to make that formula to "find" when it's just extra and be 50% extra, when it's night and it's 25%, and when it's night and extra wich is 25%+75%.
25
Folha1

Hope this helps this time.

eLy

 

[Skybluekid]

What version are you using?

 

[elynoy]

excel 2016 and 2021

 

[Fluff]

excel 2016 and 2021

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[elynoy]

Thank you. I though I had it already.

 

[elynoy]

Here's an exemple with What to expect:

Testes.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Day		Night		Day		Night		Total of Hours worked	Total of Extra Hours	Total of Extra Hours at 50% price 6/21	Total of Extra Hours at 75% price 21/6	Extra Hours50% and 25% as night hour at 75%+25% price
2	IN	OUT	IN	OUT	Total Amount of Hours in the shift	Day Hours	Total Amount of Hours in the shift	Night Hours
3	08:00	20:00	N/A	N/A	12:00	12:00	N/A	N/A	12:00	04:00	04:00
4	N/A	N/A	20:00	08:00	N/A	N/A	12:00	10:00	12:00	04:00	02:00		02:00
5	12:00	00:00	N/A	N/A	12:00	08:00	04:00	04:00	12:00	04:00			04:00
Folha2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:D3	Cell Value	<0	text	NO
G3:H3	Cell Value	=0	text	NO
I3	Cell Value	=0	text	NO
E3:F3	Cell Value	=0	text	NO
J3:M3	Cell Value	=0	text	NO

eLy

 

[rlv01]

Can the shift start times be anything or is it just limited to certain times?