Extract time from formula

elynoy · Feb 15, 2023

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:

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

Regular hour price

4,99 €

7,49 €

74,87 €

Percentage based on hours worked (8+)

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 €

9,98 €

7,49 €

89,85 €

Percentage based on hours worked (8+)

75%+25%

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 €

9,98 €

84,83 €

Percentage based on hours worked (8+)

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 · Feb 19, 2023

elynoy said:
How can I accomplish it? can I split the formulas? I have no problem is using extra cells and hide then.

Here is one example of using hidden columns. I did this quickly, so if there are errors from the previous version you will need to find and fix them yourself.

tmp.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

1

Settings

(named ranges removed)

2

BaseRate

10

€ /hr

3

OTFactor

50

%

4

NightShiftFactor

25

%

5

NightOTFactor

75

%

6

Night Shift Start

21:00

7

Night Shift Stop

6:00

8

9

Hidden Column

10

Start

User Entered Stop

Next Day

Hours of work

Day Rate Hours

Night Rate Hours

Total OT

Daytime OT

Night OT

Normal Pay

OT Pay

Night Pay

Night OT Bonus

Total Pay

Math Check

Stop (Adjusted)

Night Shift Start

Night Shift Stop

OT Start

Night OT Start

11

12

8:00

20:00

13

20:00

8:00

14

12:00

0:00

15

12:00

0:00

16

21:00

6:00

T2 X

Cell Formulas
Range	Formula
C11:C16	C11	=IF(B11<$A11,1,0)
D11:D16	D11	=(R11-A11)*24
E11:E16,H11:H16	E11	=D11-F11
F11:F16	F11	=IF(TRIM(A11)&TRIM(B11)="",0,ROUND(IF((T11-S11)24<0,0,(T11-S11)24),2))
G11:G16	G11	=MAX(0,D11-8)
I11:I16	I11	=IF((T11-S11)24<0,0,IF((T11-V11)24<0,0,(T11-V11)*24))
K11:K16	K11	=D11*$B$2
L11:L16	L11	=H11$B$3/100$B$2
M11:M16	M11	=F11$B$4/100$B$2
N11:N16	N11	=I11$B$5/100$B$2
O11:O16	O11	=SUM(K11:N11)
Q11:Q16	Q11	=D11=E11+F11
R11:R16	R11	=IF(C11=1,B11+1,B11)
S11:S16	S11	=IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11))
T11:T16	T11	=IF(C11=1,IF(R11<$B$7+1,R11,$B$7+1),IF(AND(A11>=$B$7,R11 > $B$7),R11,$B$7))
U11:U16	U11	=A11+TIME(8,0,0)
V11:V16	V11	=IF(U11<S11,S11,U11)

elynoy · Feb 23, 2023

Can you tell me how I can add more shifts to your formula like this:

Testes.xlsm

C

D

E

F

G

H

4

START 1

STOP 1

START 2

STOP 2

START 3

STOP 3

5

08:00

12:00

16:00

20:00

21:00

00:00

Jan

I tried to add your formulas to more cells and then make them SUM but then It wont get extra hours since the shifts are split.

rlv01 · Feb 23, 2023

elynoy said:
Can you tell me how I can add more shifts to your formula like this:

You mean without tossing everying done up until now into the rubbish bin and starting from scratch? I'm not sure it can be done.

I'd suggest using 3 rows.

tmp.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

10

Start

User Entered Stop

Next Day

Hours of work

Day Rate Hours

Night Rate Hours

Total OT

Daytime OT

Night OT

Normal Pay

OT Pay

Night Pay

Night OT Bonus

Total Pay

11

8:00

12:00

12

16:00

20:00

13

21:00

0:00

14

T2 X

elynoy · Feb 23, 2023

I understand.

Shoul've said it before.
Using 3 rows wont work since it wont count the OT cells still.

Thank you for your help with all of this!
Best regards!

elynoy · Mar 4, 2023

Is it possible to convert it to time instead of number as cell format? If I start at 08:00 and end ad 16:30 it gives me 8.5 hours instead of 8.30

MARK858 · Mar 4, 2023

Divide it by 24 and format the cell as time

rlv01 · Mar 5, 2023

elynoy said:
Is it possible to convert it to time instead of number as cell format?

Is it possible? Yes it is possible. In theory you can do as @MARK858 says and divide by 24 and reformat to display as time.

But as a practical 'how-to' matter there are some significant caveats that may require you to modify several other formulas. I don't know what your worksheet looks now, but chances are it is not just that one cell you must modify. You will likely need to do the divide-by-24 conversion in formulas in several cells.

Bear in mind that dividing by 24 changes the cell value. Yes, the cell can be then reformatted to display as 08:30 but the underlying value is still changed from 8.5 to 0.35417 (8.5 / 24 = 0.35417). So if another formula was trying to multiply 8.5 hr x 4.99 €/hr to calculate pay it will not produce the correct result until you go fix that formula. This means that if you make the change (by dividing by 24 and reformatting as time) you will need to follow through and adjust the other formulas that depend on the cells you performed the divide-by-24 operation on, and test with various values until you are sure that everything is producing correct results again.

elynoy · Mar 5, 2023

Thanks for the answers.

I added a new column next to them and divided it by 24 but I left the calculations as normal number. I just need it to show the time instead of a number.

So in theory everything sould work as intended and the formulas should work the same, I just hid the number and show the time and let the formulas work as number as you said.

Thanks for everything by the way.

elynoy · Mar 6, 2023

there's a problem but I dont know how to explain.
I have the conditional format do make the cells with 0 value stick to the color like if it's empty but I still get 0 in there and I get the #### in some cells as well but not all of them.

teste.xlsm

C

D

E

F

G

H

I

J

K

L

M

N

O

5

15:00