Total Break in minutes between Employees Break time and Break Start Time

Z

Zee996

New Member
Joined
Nov 30, 2021
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I could really use your help with an Excel formula to calculate the total break time based on the start and end times of each break. I'm finding it quite challenging to set up the formula because the data isn't very straightforward—employees can take multiple breaks in a day, and the way the data is organized isn't consistent. And it is not in order - Break type category can be jumbled means that the Employee ID 11 Break time might be after employee 41 (as the date is getting copied from the server). And I believe sorting the data again and again won't be feasible.

Is there a way to create a formula that can calculate the total break time for each employee, even with these challenges?
Screenshot_20241212-023402.png

Thank you in advance.
 
Sort by date Sort by votes
lrobbo314 said:
Like this?

Book2
ABCDEFG
1Emp IdBreak TypeDateTimeEmp IdTotal
211Start12/11/20248:27 AM112:17
311End12/11/20248:45 AM410:00
411Start12/11/202412:27 AM510:00
511End12/11/20241:25 AM
641Start12/11/20248:25 AM
741End12/11/20248:25 AM
851Start12/11/20248:25 AM
951End12/11/20248:25 AM
1011Start12/11/20247:25 AM
1111End12/11/20248:25 AM
Sheet4


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    DT = Table.AddColumn(Type, "DateTime", each DateTime.FromText(Text.Combine({Text.From([Date]),Text.From([Time])}," "))),
    Group = Table.Group(DT, {"Emp Id"}, {{"Total", each List.Sum(List.Transform(List.Split(_[DateTime],2),each _{1}-_{0}))}})
in
    Group
Click to expand...
Yes, thank you.

I just wanted to confirm: will this show the different break times for each employee? For example, if an employee with Emp ID 11 takes three breaks in a single day, will show each break entries in different rows taken by the employee. Is that possible?
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
How about this?

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    DT = Table.AddColumn(Type, "DateTime", each DateTime.FromText(Text.Combine({Text.From([Date]),Text.From([Time])}," "))),
    Index = Table.AddIndexColumn(DT, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    RC = Table.RemoveColumns(IntDiv,{"Index","Date","Time"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[#"Break Type"]), "Break Type", "DateTime"),
    Diff = Table.AddColumn(Pivot, "Diff", each [End]-[Start])
in
    Diff
 
Upvote 0
Thank you so much. I have tried your solution and it does gives the exact output. As the date and Timing is in different columns.
Would it be possible to get the time difference in minutes?
Click to expand...
In your example you have provided, the date and time are in the same column. If they are different, then please show an example of how your data is actually. My solution shows the minutes based upon the example you have provided. If I am not understanding your issue correctly then do a mock up of what you have and what your expected results should look like. I unfortunately have lost my crystal ball.
 
Upvote 0
Looking back on it, I like this solution better.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
    DT = Table.AddColumn(Type, "DateTime", each DateTime.FromText(Text.Combine({Text.From([Date]),Text.From([Time])}," ")),DateTime.Type),
    Split = Table.Split(DT,2),
    Records = Table.FromList(List.Transform(Split,each [ID=_[Emp Id]{0},Start=_[DateTime]{0},End=_[DateTime]{1},Diff=[DateTime]{1}-_[DateTime]{0}]),Splitter.SplitByNothing(),null,null),
    Expand = Table.ExpandRecordColumn(Records, "Column1", {"ID", "Start", "End", "Diff"}, {"ID", "Start", "End", "Diff"})
in
    Expand
 
Upvote 0
Zee996 said:
Thank you so much. I wanted to run a pivot for a report. It it possible for pivot to dynamically updates as the data increases?
Click to expand...

You can change as per your data range like this

Book1
ABCDEFGHIJKLM
1Emp IdBreak TypeDate TimeEmp IdBreak Time TotalEmp IdStartEndTotal Break
211Start12/11/24 8:27112:16:001112/11/24 8:2712/11/24 8:450:18:00
311End12/11/24 8:45410:00:001112/11/24 0:2712/11/24 1:250:58:00
411Start12/11/24 0:27510:00:004112/11/24 8:2512/11/24 8:250:00:00
511End12/11/24 1:255112/11/24 8:2512/11/24 8:250:00:00
641Start12/11/24 8:251112/11/24 7:2512/11/24 8:251:00:00
741End12/11/24 8:25
851Start12/11/24 8:25
951End12/11/24 8:25
1011Start12/11/24 7:25
1111End12/11/24 8:25
12
13
14
15
16
17
Sheet1
Cell Formulas
RangeFormula
E2:E4E2=UNIQUE(A2:A11)
H2:I6H2=CHOOSECOLS(FILTER(A2:C1100,B2:B1100=I1),1,3)
J2:J6J2=CHOOSECOLS(FILTER(A2:C1100,B2:B1100=J1),3)
F2:F4F2=-(SUMIFS(C:C,B:B,"Start",A:A,E2)-SUMIFS(C:C,B:B,"End",A:A,E2))
K2:K6K2=J2-I2
Dynamic array formulas.
 
Upvote 0
alansidman said:
In your example you have provided, the date and time are in the same column. If they are different, then please show an example of how your data is actually. My solution shows the minutes based upon the example you have provided. If I am not understanding your issue correctly then do a mock up of what you have and what your expected results should look like. I unfortunately have lost my crystal ball.
Click to expand...
Thank you for your help. Here is how the dataset looks like with the desired output.
1734034149576.png
 
Upvote 0
Another fun way of doing it.

Book2
ABCDEFGHIJKLM
1Emp IdBreak TypeDateTimeDateTimeEmp IDStartEndBreak TimeEmp IDTotal
211Start12/11/20248:27 AM12/11/2024 8:271112/11/2024 8:4512/11/2024 8:270:18112:16
311End12/11/20248:45 AM12/11/2024 8:451112/11/2024 1:2512/11/2024 0:270:58410:00
411Start12/11/202412:27 AM12/11/2024 0:274112/11/2024 8:2512/11/2024 8:250:00510:00
511End12/11/20241:25 AM12/11/2024 1:255112/11/2024 8:2512/11/2024 8:250:00
641Start12/11/20248:25 AM12/11/2024 8:251112/11/2024 8:2512/11/2024 7:251:00
741End12/11/20248:25 AM12/11/2024 8:25
851Start12/11/20248:25 AM12/11/2024 8:25
951End12/11/20248:25 AM12/11/2024 8:25
1011Start12/11/20247:25 AM12/11/2024 7:25
1111End12/11/20248:25 AM12/11/2024 8:25
Sheet4
Cell Formulas
RangeFormula
G1:J6G1=REDUCE({"Emp ID","Start","End","Break Time"},SEQUENCE(ROWS(Table3[Emp Id])/2,,,2),LAMBDA(s,c,VSTACK(s,LET(r,CHOOSEROWS(Table3,c,c+1),HSTACK(INDEX(r,1,1),INDEX(r,2,5),INDEX(r,1,5),INDEX(r,2,5)-INDEX(r,1,5))))))
L2:M4L2=LET(u,UNIQUE(Table3[Emp Id]),d,DROP(G1#,1),HSTACK(u,BYROW(u,LAMBDA(br,SUM((INDEX(d,,1)=br)*(INDEX(d,,4)))))))
E2:E11E2=[@Date]+[@Time]
Dynamic array formulas.
 
Upvote 0
Modified to reflect your latest expected format

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date and Timestamp", type datetime}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date and Timestamp", type text}}, "en-US"), "Date and Timestamp", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date and Timestamp.1", "Date and Timestamp.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "End", each if [Break Type] = "End" then [Date and Timestamp.2] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"End"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Break Type] = "Start"),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Date and Timestamp.2", "Start"}, {"Date and Timestamp.1", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Break Type"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Start", type time}, {"End", type time}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each [End] - [Start], type duration),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Length of Break"}})
in
    #"Renamed Columns1"
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
87
cooleomter
C
R
  • Question Question
Time sheet formulas
Replies
2
Views
371
rodnaaaaay
R
R
  • Question Question
Leave Database Time Tracking to equal full day after 8 hours
Replies
6
Views
322
NoSparks
N
P
  • Question Question
Calculate time between start and finish minus the lunch break
2
Replies
13
Views
1K
paulalex93
P
R
  • Question Question
Advice required for dynamic master table
Replies
0
Views
298
RichPinn
R

Forum statistics

Threads
1,224,750
Messages
6,180,740
Members
452,996
Latest member
nelsonsix66

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