Unpaid break deduction formula

G

gotchamehdi

New Member
Joined
Feb 22, 2021
Messages
5
Platform
  1. MacOS
Hi All,

I am after a formula that can deducted break from working hours

  • Employees who work more than six hours are entitled to a 30 minute break and if they work more than 8 hours they are entitled to another 30 min.
  • A=start time
  • B=finishing time
  • Total hours -break ...

Many thanks in advance
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi,

May be something like this:

Book3.xlsx
ABCD
1Total Hrs - Break
22/22/2021 10:30:00 PM2/23/2021 6:00:00 AM7
32/22/2021 10:30:00 PM2/23/2021 8:00:00 AM8.5
42/22/2021 9:00:00 AM2/22/2021 5:00:00 PM7
52/22/2021 8:00:00 AM2/22/2021 12:00:00 PM4
Sheet787
Cell Formulas
RangeFormula
D2:D5D2=(B2-A2)*24-IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))
 
Upvote 0
jtakw said:
Hi,

May be something like this:

Book3.xlsx
ABCD
1Total Hrs - Break
22/22/2021 10:30:00 PM2/23/2021 6:00:00 AM7
32/22/2021 10:30:00 PM2/23/2021 8:00:00 AM8.5
42/22/2021 9:00:00 AM2/22/2021 5:00:00 PM7
52/22/2021 8:00:00 AM2/22/2021 12:00:00 PM4
Sheet787
Cell Formulas
RangeFormula
D2:D5D2=(B2-A2)*24-IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))
Click to expand...
Thank you Jtakw, I've tried to use =G6-(IF(G6>TIME(8,0,0),TIME(0,60,0),IF(G6>TIME(6,0,0),TIME(0,30,0),G6))) but in vain. G6 where I have the total hours.
 
Upvote 0
I'm not sure what you're after, in OP, you said you wanted:

gotchamehdi said:
Total hours -break ...
Click to expand...

Here's a few options:

Book3.xlsx
ABCDEF
1Total Hrs - BreakTotal Hrs include BreakTotal Break Only
22/22/2021 10:30:00 PM2/23/2021 6:00:00 AM77.50:30
32/22/2021 10:30:00 PM2/23/2021 8:00:00 AM8.59.51:00
42/22/2021 9:00:00 AM2/22/2021 5:00:00 PM781:00
52/22/2021 8:00:00 AM2/22/2021 12:00:00 PM440:00
6Format as Time
Sheet787
Cell Formulas
RangeFormula
D2:D5D2=(B2-A2)*24-IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))
E2:E5E2=(B2-A2)*24
F2:F5F2=IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))/24
 
Upvote 0
In case you want everything in Time format, use E, F, G, & H column formulas:

Book3.xlsx
ABCDEFGHI
1Total Hrs - Break<Same<SameTotal Hrs include Break<SameTotal Break Only
22/22/2021 10:30:00 PM2/23/2021 6:00:00 AM77:007:007.57:300:30
32/22/2021 10:30:00 PM2/23/2021 8:00:00 AM8.58:308:309.59:301:00
42/22/2021 9:00:00 AM2/22/2021 5:00:00 PM77:007:0088:001:00
52/22/2021 8:00:00 AM2/22/2021 12:00:00 PM44:004:0044:000:00
6Format as TimeFormat as TimeFormat as TimeFormat as Time
Sheet787
Cell Formulas
RangeFormula
D2:D5D2=(B2-A2)*24-IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))
E2:E5E2=(B2-A2)-IF((B2-A2)<0.25,0,IF((B2-A2)>=1/3,TIME(1,0,0),TIME(0,30,0)))
F2:F5F2=(B2-A2)-IF((B2-A2)<0.25,0,IF((B2-A2)>=1/3,1/24,1/48))
G2:G5G2=(B2-A2)*24
H2:H5H2=B2-A2
I2:I5I2=IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))/24
 
Last edited:
Upvote 0
Too late to Edit:

jtakw said:
In case you want everything in Time format, use E, F, G, & H column formulas:
Click to expand...

I meant E, F, H, & I Column formulas for Time format.
 
Upvote 0
jtakw said:
In case you want everything in Time format, use E, F, G, & H column formulas:

Book3.xlsx
ABCDEFGHI
1Total Hrs - Break<Same<SameTotal Hrs include Break<SameTotal Break Only
22/22/2021 10:30:00 PM2/23/2021 6:00:00 AM77:007:007.57:300:30
32/22/2021 10:30:00 PM2/23/2021 8:00:00 AM8.58:308:309.59:301:00
42/22/2021 9:00:00 AM2/22/2021 5:00:00 PM77:007:0088:001:00
52/22/2021 8:00:00 AM2/22/2021 12:00:00 PM44:004:0044:000:00
6Format as TimeFormat as TimeFormat as TimeFormat as Time
Sheet787
Cell Formulas
RangeFormula
D2:D5D2=(B2-A2)*24-IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))
E2:E5E2=(B2-A2)-IF((B2-A2)<0.25,0,IF((B2-A2)>=1/3,TIME(1,0,0),TIME(0,30,0)))
F2:F5F2=(B2-A2)-IF((B2-A2)<0.25,0,IF((B2-A2)>=1/3,1/24,1/48))
G2:G5G2=(B2-A2)*24
H2:H5H2=B2-A2
I2:I5I2=IF((B2-A2)*24<6,0,IF((B2-A2)*24>=8,1,0.5))/24
Click to expand...
Many Many thanks
 
Upvote 0
jtakw said:
You're welcome.
Click to expand...
jtakw said:
You're welcome.
Click to expand...

Hi Jtakw, I've just realised the formula deduct 1 hour when employee works 8 hours, i think it requires a quick fix : so if they work <=6 no breaks apply if they work >6, 30 minutes get deducted and if they work >8 1 hour though.
=(B2-A2)-IF((B2-A2)<0.25,0,IF((B2-A2)>=1/3,TIME(1,0,0),TIME(0,30,0)))
Many thanks in advance
 
Upvote 0
gotchamehdi said:
Hi Jtakw, I've just realised the formula deduct 1 hour when employee works 8 hours, i think it requires a quick fix : so if they work <=6 no breaks apply if they work >6, 30 minutes get deducted and if they work >8 1 hour though.
=(B2-A2)-IF((B2-A2)<0.25,0,IF((B2-A2)>=1/3,TIME(1,0,0),TIME(0,30,0)))
Many thanks in advance
Click to expand...

That's exactly what my formula is doing. See sample results in my table in Post #5

If I'm misunderstanding your requirements, please explain again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
661
felixstraube
F
R
  • Solved
Time formula
2
Replies
12
Views
237
Dave Patton
D
P
  • Question
Calculate time between start and finish minus the lunch break
2
Replies
13
Views
727
paulalex93
P
S
  • Question
If formula to calculate worked hours
Replies
1
Views
497
Peter_SSs
Peter_SSs
S
  • Solved
Deduct 20 mins break
Replies
4
Views
196
Sandy42
S

Forum statistics

Threads
1,214,826
Messages
6,121,797
Members
449,048
Latest member
greyangel23

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