Extract time from formula

E

elynoy

Board Regular
Joined
Oct 29, 2018
Messages
160
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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 € ​
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
 
Sort by date Sort by votes
Did you try the new version without the names I just posted?
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Seems to be working unless I use the time start 21:00 and stop 06:00 (if I use the night shift alone it's gives - result.
 
Upvote 0
elynoy said:
Seems to be working unless I use the time start 21:00 and stop 06:00 (if I use the night shift alone it's gives - result.
Click to expand...

I'm not sure I understand since the calc looks ok to me.
StartStopNext DayHours of workDay Rate HoursNight Rate HoursTotal OTDaytime OTNight OT
21:006:001909101
 
Upvote 0
It gives me this result:
Testes.xlsm
ABCDEFGHIJKLMNO
1821:0006:0019091-1,77636E-15144,91 €0,00 €11,23 €3,74 €59,88 €
Folha3
Cell Formulas
RangeFormula
C18C18=IF(B18<$A18,1,0)
D18D18=(IF(C18=1,B18+1,B18)-A18)*24
E18,H18E18=D18-F18
F18F18=ROUND(IF((IF(C18=1,IF(IF(C18=1,B18+1,B18)<$B$7+1,IF(C18=1,B18+1,B18),$B$7+1),IF(AND(A18>=$B$7,IF(C18=1,B18+1,B18) > $B$7),IF(C18=1,B18+1,B18),$B$7))-IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)))*24<0,0,(IF(C18=1,IF(IF(C18=1,B18+1,B18)<$B$7+1,IF(C18=1,B18+1,B18),$B$7+1),IF(AND(A18>=$B$7,IF(C18=1,B18+1,B18) > $B$7),IF(C18=1,B18+1,B18),$B$7))-IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)))*24),2)
G18G18=MAX(0,D18-8)
I18I18=IF((IF(C18=1,IF(IF(C18=1,B18+1,B18)<$B$7+1,IF(C18=1,B18+1,B18),$B$7+1),IF(AND(A18>=$B$7,IF(C18=1,B18+1,B18) > $B$7),IF(C18=1,B18+1,B18),$B$7))-IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)))*24<0,0,IF((IF(C18=1,IF(IF(C18=1,B18+1,B18)<$B$7+1,IF(C18=1,B18+1,B18),$B$7+1),IF(AND(A18>=$B$7,IF(C18=1,B18+1,B18) > $B$7),IF(C18=1,B18+1,B18),$B$7))-IF(A18+TIME($B$8,0,0)<IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)),IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)),A18+TIME($B$8,0,0)))*24<0,0,(IF(C18=1,IF(IF(C18=1,B18+1,B18)<$B$7+1,IF(C18=1,B18+1,B18),$B$7+1),IF(AND(A18>=$B$7,IF(C18=1,B18+1,B18) > $B$7),IF(C18=1,B18+1,B18),$B$7))-IF(A18+TIME(8,0,0)<IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)),IF(C18=1,IF(A18>$B$6,A18,$B$6),IF(A18>=$B$7,$B$6,A18)),A18+TIME($B$8,0,0)))*24))
K18K18=D18*$B$2
L18L18=H18*$B$3/100*$B$2
M18M18=F18*$B$4/100*$B$2
N18N18=I18*$B$5/100*$B$2
O18O18=SUM(K18:N18)


Edit. I figured it out. I had to change the cell to number as well.
 
Upvote 0
One last question.
Testes.xlsm
ABCDEFGHIJKLMNO
10StartStopNext DayHours of workDay Rate HoursNight Rate HoursTotal OTDaytime OTNight OTBase PayDay Shift OT PremiumNight PremiumNight OT PremiumTotal Pay
1100060#NOME?#NOME?0,00 €#NOME?7,49 €#NOME?#NOME?
Folha3
Cell Formulas
RangeFormula
C11C11=IF(B11<$A11,1,0)
D11D11=(IF(C11=1,B11+1,B11)-A11)*24
E11E11=IF(E11<=0,0,D11-F11)
F11F11=ROUND(IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24),2)
G11G11=MAX(0,D11-$B$8)
H11H11=G11-I11
I11I11=IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(A11+TIME($B$8,0,0)<IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),A11+TIME($B$8,0,0)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(A11+TIME(8,0,0)<IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),A11+TIME($B$8,0,0)))*24))
K11K11=D11*$B$2
L11L11=H11*$B$3/100*$B$2
M11M11=F11*$B$4/100*$B$2
N11N11=I11*$B$5/100*$B$2
O11O11=SUM(K11:N11)


If there's no start and stop time, i still get 6 hours as night rate. how can I set it to 0 if there's no start/stop in the first place?
I got the day rate hours as 0 but cant figure how to do it with the big one.
Edit: and by any chance, how can I hide the #NAME as well?
 
Upvote 0
Book4
H
10Daytime OT
11-1.77636E-15
Sheet1


For unexplained reasons excel shows this tiny difference. I use cell formatting to show it as zero. The ROUND function is another possibility
 
Upvote 0
Probably something like this.
Book4
ABCDEFGHIJ
10StartStopNext DayHours of workDay Rate HoursNight Rate HoursTotal OTDaytime OTNight OT
1100000000
Sheet1
Cell Formulas
RangeFormula
C11C11=IF(B11<$A11,1,0)
D11D11=(IF(C11=1,B11+1,B11)-A11)*24
E11,I11E11=D11-F11
F11F11=IF(TRIM(A11)&TRIM(B11)="",0,ROUND(IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24),2))
G11G11=IF(TRIM(A11)&TRIM(B11)="",0,ROUND(IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24),2))
H11H11=MAX(0,D11-8)
J11J11=IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(A11+TIME(8,0,0)<IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),A11+TIME(8,0,0)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(A11+TIME(8,0,0)<IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),A11+TIME(8,0,0)))*24))
 
Upvote 0
Can you explain how can I fix the values -6 and 6? to be 0 or empty if there's no start/stop?
Testes.xlsm
ABCDEFGHIJKLMNO
10StartStopNext DayHours of workDay Rate HoursNight Rate HoursTotal OTDaytime OTNight OTBase PayDay Shift OT PremiumNight PremiumNight OT PremiumTotal Pay
1100-660#NOME?#NOME?0,00 €#NOME?7,49 €#NOME?#NOME?
Folha3
Cell Formulas
RangeFormula
C11C11=IF(B11<$A11,1,0)
D11D11=(IF(C11=1,B11+1,B11)-A11)*24
E11,H11E11=D11-F11
F11F11=ROUND(IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24),2)
G11G11=MAX(0,D11-$B$8)
I11I11=IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)))*24<0,0,IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(A11+TIME($B$8,0,0)<IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),A11+TIME($B$8,0,0)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<$B$7+1,IF(C11=1,B11+1,B11),$B$7+1),IF(AND(A11>=$B$7,IF(C11=1,B11+1,B11) > $B$7),IF(C11=1,B11+1,B11),$B$7))-IF(A11+TIME(8,0,0)<IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),IF(C11=1,IF(A11>$B$6,A11,$B$6),IF(A11>=$B$7,$B$6,A11)),A11+TIME($B$8,0,0)))*24))
K11K11=D11*$B$2
L11L11=H11*$B$3/100*$B$2
M11M11=F11*$B$4/100*$B$2
N11N11=I11*$B$5/100*$B$2
O11O11=SUM(K11:N11)


I tried to pout an if at the start but it doesnt work and hides the values if there's data in them
 
Upvote 0
elynoy said:
I tried to pout an if at the start but it doesnt work and hides the values if there's data in them
Click to expand...

I addressed this in my last post. See the formula in F11.
 
Upvote 0
I'm sorry. I missread that post.

Thank you for your help! seems to be working really good now!

when you said to maintai9n it it would be better to use hidden cells.

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

I'm asking because In case I need to edit something in there, I'm afraind that I wont be able to. Like you said, those 2 big formuilas are really confusing.

I could use vba as well but I I have no idea how to acomplish that using vba.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Super Time sheet
Replies
1
Views
253
kolekbolek1283
K
M
  • Question
Count #of Employees Worked Between Time Periods
Replies
2
Views
325
Eric W
Eric W
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
138
ChetanPuri
C
J
  • Question
Need help calculating Day/Night Shift Hours.
Replies
4
Views
795
jtedmonson42
J
C
  • Question
Calculate and Identify Ordinary Hours, Double time or 1.5 Time
Replies
0
Views
110
ChetanPuri
C

Forum statistics

Threads
1,216,119
Messages
6,128,947
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top