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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

B
  • Solved
Change cell contains depends on time and date
Replies
2
Views
197
borg
B
K
  • Question Question
Super Time sheet
Replies
1
Views
539
kolekbolek1283
K
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
446
myall_blues
myall_blues
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
K
  • Question Question
Copy data from rows with the dates (Monday to Sunday)
Replies
1
Views
71
K_Stevs1
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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