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
It can start at anytime, and the formula works for 24 hours to get exact hours even if I start one day and finish at the next day.
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Any time as in whole number hours i.e. 10:00, 14:00, 22:00 or any time as in 10:03, 14:30, 22:45?
 
Upvote 0
both since it counts full 8 hours. If I start at 08:10, I'll have to end it at 16:10 for exemple.
 
Upvote 0
Well, I gave it a try and it seems to work. The requirement to have dayshift of either end of of the shift makes the formulas grow large. If this was something I had to maintain, I would probably code it in VBA rather than use formulas. I did not bother, but you can shorten the two long formulas considerably if you use the LET function found in newer versions of Excel to combine repeating elements. If I HAD to maintain it using formulas, I would use hidden helper columns to keep the formulas from being too large in any single cell.
TimeTest.xlsm
ABCDEFGHIJKLMNO
1Settings
2BaseRate10€ /hr
3OTFactor50%
4NightShiftFactor25%
5NightOTFactor75%
6Night Shift Start21:00
7Night Shift Stop6:00
8
9
10StartStopNext DayHours of workDay Rate HoursNight Rate HoursTotal OTDaytime OTNight OTBase PayDay Shift OT PremiumNight PremiumNight OT PremiumTotal Pay
118:0020:00012120440120.0020.000.000.00140.00
1220:008:0011239422120.0010.0022.5015.00167.50
1312:000:0011293413120.005.007.5022.50155.00
T3 (2)
Cell Formulas
RangeFormula
C11:C13C11=IF(B11<$A11,1,0)
D11:D13D11=(IF(C11=1,B11+1,B11)-A11)*24
E11:E13,H11:H13E11=D11-F11
F11:F13F11=ROUND(IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<Night_Shift_Stop+1,IF(C11=1,B11+1,B11),Night_Shift_Stop+1),IF(AND(A11>=Night_Shift_Stop,IF(C11=1,B11+1,B11) > Night_Shift_Stop),IF(C11=1,B11+1,B11),Night_Shift_Stop))-IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<Night_Shift_Stop+1,IF(C11=1,B11+1,B11),Night_Shift_Stop+1),IF(AND(A11>=Night_Shift_Stop,IF(C11=1,B11+1,B11) > Night_Shift_Stop),IF(C11=1,B11+1,B11),Night_Shift_Stop))-IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)))*24),2)
G11:G13G11=MAX(0,D11-8)
I11:I13I11=IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<Night_Shift_Stop+1,IF(C11=1,B11+1,B11),Night_Shift_Stop+1),IF(AND(A11>=Night_Shift_Stop,IF(C11=1,B11+1,B11) > Night_Shift_Stop),IF(C11=1,B11+1,B11),Night_Shift_Stop))-IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)))*24<0,0,IF((IF(C11=1,IF(IF(C11=1,B11+1,B11)<Night_Shift_Stop+1,IF(C11=1,B11+1,B11),Night_Shift_Stop+1),IF(AND(A11>=Night_Shift_Stop,IF(C11=1,B11+1,B11) > Night_Shift_Stop),IF(C11=1,B11+1,B11),Night_Shift_Stop))-IF(A11+TIME(8,0,0)<IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)),IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)),A11+TIME(8,0,0)))*24<0,0,(IF(C11=1,IF(IF(C11=1,B11+1,B11)<Night_Shift_Stop+1,IF(C11=1,B11+1,B11),Night_Shift_Stop+1),IF(AND(A11>=Night_Shift_Stop,IF(C11=1,B11+1,B11) > Night_Shift_Stop),IF(C11=1,B11+1,B11),Night_Shift_Stop))-IF(A11+TIME(8,0,0)<IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)),IF(C11=1,IF(A11>Night_Shift_Start,A11,Night_Shift_Start),IF(A11>=Night_Shift_Stop,Night_Shift_Start,A11)),A11+TIME(8,0,0)))*24))
K11:K13K11=D11*BaseRate
L11:L13L11=H11*OTFactor/100*BaseRate
M11:M13M11=F11*NightShiftFactor/100*BaseRate
N11:N13N11=I11*NightOTFactor/100*BaseRate
O11:O13O11=SUM(K11:N11)
Named Ranges
NameRefers ToCells
'T3 (2)'!BaseRate='T3 (2)'!$B$2K11:N13
'T3 (2)'!Night_Shift_Start='T3 (2)'!$B$6F11:F13, I11:I13
'T3 (2)'!Night_Shift_Stop='T3 (2)'!$B$7F11:F13, I11:I13
'T3 (2)'!NightOTFactor='T3 (2)'!$B$5N11:N13
'T3 (2)'!NightShiftFactor='T3 (2)'!$B$4M11:M13
'T3 (2)'!OTFactor='T3 (2)'!$B$3L11:L13
'T3 (2)'!StartRange='T3 (2)'!$A$11:$A$17I11, F11, C11:D11
 
Upvote 0
I'm am sorry, I cant seem to get it to work. After I converted all the ENG to my langguage I still get #NAME.

EDIT: I changed the cell format to number and it works but only for the day shift, the other two i get the #NAME error still.
 
Last edited:
Upvote 0
well, I found a workaround for the problem.

If I enter at 20:00 and stop at 08:00, it works if I enter 25:00 for 1AM, 26:00 for 2AM, and so on until 32:00 for 8AM.

And it's based on the Night OT cell formula wich gives the #NAME error.
 
Upvote 0
The named ranges are defined this way
Named RangeCell Address
BaseRate$B$2
OTFactor$B$3
NightShiftFactor$B$4
NightOTFactor$B$5
Night_Shift_Start$B$6
Night_Shift_Stop$B$7
 
Upvote 0
Here is a version with the named ranges removed.
Book4
ABCDEFGHIJKLMNO
1Settings
2BaseRate10€ /hr
3OTFactor50%
4NightShiftFactor25%
5NightOTFactor75%
6Night Shift Start21:00
7Night Shift Stop6:00
8
9
10StartStopNext DayHours of workDay Rate HoursNight Rate HoursTotal OTDaytime OTNight OTBase PayDay Shift OT PremiumNight PremiumNight OT PremiumTotal Pay
118:0020:00012120440120,00 €20,00 €0,00 €0,00 €140,00 €
1220:008:0011239422120,00 €10,00 €22,50 €15,00 €167,50 €
1312:000:0011293413120,00 €5,00 €7,50 €22,50 €155,00 €
Sheet1
Cell Formulas
RangeFormula
C11:C13C11=IF(B11<$A11,1,0)
D11:D13D11=(IF(C11=1,B11+1,B11)-A11)*24
E11:E13,H11:H13E11=D11-F11
F11:F13F11=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)
G11:G13G11=MAX(0,D11-8)
I11:I13I11=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))
K11:K13K11=D11*$B$2
L11:L13L11=H11*$B$3/100*$B$2
M11:M13M11=F11*$B$4/100*$B$2
N11:N13N11=I11*$B$5/100*$B$2
O11:O13O11=SUM(K11:N11)
 
Upvote 0
Yes, I got all the names in place, the only thing I removed was the TIME(8 and added a name for itand a cell in case the base time changes for more or less than 8 as well, but it's the same thing.

It only works if I use 25:00 for 01:00 and so on still
 
Upvote 0
You must be doing something different since it works for me as shown.
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,215,343
Messages
6,124,400
Members
449,156
Latest member
LSchleppi

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