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]

I suspect it is not a conditional formatting issue. Try changing the formula in column O from =N5/24 to =MAX(N5/24,0)

 

[elynoy]

It didnt work.

I think I fixed it by converting it to text and then using conditional format to make it "invisible".

15:00	23:00	0	8,0	08:00	6,0	06:00	2,0	02:00	0,0	00:00	0,00	00:00	0,0	00:00	1,0	8,0	08:00

It doesnt show on the cell, my excel crashes when I try to use the addon. but the formula is : =TEXT(N5/24;"hh:mm")
Like this seems to do the trick

Thanks once again