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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Here is a way using Power Query.

Book2
ABCDEF
1Emp IdBreak TypeDate TimeEmp IdBreak Time Total
211Start12/11/2024 8:27110.02:16:00
311End12/11/2024 8:45410.00:00:00
411Start12/11/2024 0:27510.00:00:00
511End12/11/2024 1:25
641Start12/11/2024 8:25
741End12/11/2024 8:25
851Start12/11/2024 8:25
951End12/11/2024 8:25
1011Start12/11/2024 7:25
1111End12/11/2024 8:25
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    RC = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[#"Break Type"]), "Break Type", "Date Time"),
    Diff = Table.AddColumn(Pivot, "Diff", each [End]-[Start]),
    Group = Table.Group(Diff, {"Emp Id"}, {{"Break Time Total", each List.Sum([Diff]), type duration}})
in
    Group
 
Upvote 0
Power Query Solution

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date and Timestamp", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End", each if [Break Type]="End" then [Date and Timestamp] 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", "Start"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Break Type"}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [End] - [Start], type duration),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Length of Break"}})
in
    #"Renamed Columns1"

Book1
ABCD
1Emp IdBreak TypeDate and Timestamp
211Start12/10/2024 8:27
311End12/10/2024 8:45
411Start12/10/2024 0:27
511End12/10/2024 1:25
641Start12/10/2024 8:25
741End12/10/2024 8:25
851Start12/10/2024 8:25
951End12/10/2024 8:25
1011Start12/10/2024 7:25
1111End12/10/2024 8:25
12
13Emp IdStartEndLength of Break
141112/10/2024 8:2712/10/2024 8:450.00:18:00
151112/10/2024 0:2712/10/2024 1:250.00:58:00
164112/10/2024 8:2512/10/2024 8:250.00:00:00
175112/10/2024 8:2512/10/2024 8:250.00:00:00
181112/10/2024 7:2512/10/2024 8:250.01:00:00
Sheet1
 
Upvote 0
This is the best PQ version I could come up with.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Emp Id"}, {{"Total", each List.Sum(List.Transform(List.Split(_[Date Time],2),each _{1}-_{0}))}})
in
    Group
 
Upvote 0
Try

Book1
ABCDEFGHIJKL
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:C11,B2:B11=I1),1,3)
J2:J6J2=CHOOSECOLS(FILTER(A2:C11,B2:B11=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
lrobbo314 said:
Here is a way using Power Query.

Book2
ABCDEF
1Emp IdBreak TypeDate TimeEmp IdBreak Time Total
211Start12/11/2024 8:27110.02:16:00
311End12/11/2024 8:45410.00:00:00
411Start12/11/2024 0:27510.00:00:00
511End12/11/2024 1:25
641Start12/11/2024 8:25
741End12/11/2024 8:25
851Start12/11/2024 8:25
951End12/11/2024 8:25
1011Start12/11/2024 7:25
1111End12/11/2024 8:25
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    RC = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[#"Break Type"]), "Break Type", "Date Time"),
    Diff = Table.AddColumn(Pivot, "Diff", each [End]-[Start]),
    Group = Table.Group(Diff, {"Emp Id"}, {{"Break Time Total", each List.Sum([Diff]), type duration}})
in
    Group
Click to expand...
Thank you so much.

I have try to run this query, but I am getting a null value. Date and Time are in separate column in my dataset. So, I have tried to include this in the solution you have provided.



Power Query: 
let
    // Load the source table
    Source = Excel.CurrentWorkbook(){[Name="Break_Time"]}[Content],

    // Ensure "Date" and "Timing" columns are of correct types
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Timing", type time}, {"Status", type text}, {"ID #", type text}}),

    // Combine "Date" and "Timing" into a single "Date Time" column
    #"Added DateTime" = Table.AddColumn(#"Changed Type", "Date Time", each DateTime.From([Date] & [Timing]), type datetime),

    // Add an index column to facilitate pairing
    Index = Table.AddIndexColumn(#"Added DateTime", "Index", 0, 1, Int64.Type),

    // Add an integer division column to group Start and End rows
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),

    // Remove unnecessary columns
    RC = Table.RemoveColumns(IntDiv, {"Index", "Date", "Timing"}),

    // Pivot the data to align "Start" and "End" as columns
    Pivot = Table.Pivot(RC, List.Distinct(RC[#"Status"]), "Status", "Date Time"),

    // Calculate the duration between "Start" and "End"
    Diff = Table.AddColumn(Pivot, "Diff", each [In For Break] - [Out For Break], type duration),

    // Group by "ID #" and sum the durations
    Group = Table.Group(Diff, {"ID #"}, {{"Break Time Total", each List.Sum([Diff]), type duration}})
in
    Group
 
Upvote 0
alansidman said:
Power Query Solution

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date and Timestamp", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End", each if [Break Type]="End" then [Date and Timestamp] 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", "Start"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Break Type"}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [End] - [Start], type duration),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "Length of Break"}})
in
    #"Renamed Columns1"

Book1
ABCD
1Emp IdBreak TypeDate and Timestamp
211Start12/10/2024 8:27
311End12/10/2024 8:45
411Start12/10/2024 0:27
511End12/10/2024 1:25
641Start12/10/2024 8:25
741End12/10/2024 8:25
851Start12/10/2024 8:25
951End12/10/2024 8:25
1011Start12/10/2024 7:25
1111End12/10/2024 8:25
12
13Emp IdStartEndLength of Break
141112/10/2024 8:2712/10/2024 8:450.00:18:00
151112/10/2024 0:2712/10/2024 1:250.00:58:00
164112/10/2024 8:2512/10/2024 8:250.00:00:00
175112/10/2024 8:2512/10/2024 8:250.00:00:00
181112/10/2024 7:2512/10/2024 8:250.01:00:00
Sheet1
Click to expand...
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?


Power Query: 
let
    // Load the source table
    Source = Excel.CurrentWorkbook(){[Name="Break_Time"]}[Content],

    // Ensure "Date" and "Timing" columns are of correct types
    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}, {"Timing", type time}, {"Status", type text}}),

    // Combine "Date" and "Timing" into a single "Date and Timestamp" column
    #"Added DateTime" = Table.AddColumn(#"Changed Type", "Date and Timestamp", each DateTime.From([Date] & [Timing]), type datetime),

    // Add a new "End" column for rows where "Status" is "In For Break"
    #"Added Custom" = Table.AddColumn(#"Added DateTime", "In For Break", each if [Status] = "In For Break" then [Date and Timestamp] else null),

    // Fill down the "End" column to propagate values
    #"Filled Up" = Table.FillUp(#"Added Custom", {"In For Break"}),

    // Filter rows where "Break Type" is "Start"
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Status] = "Out For Break")),

    // Rename "Date and Timestamp" to "Start"
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows", {{"Date and Timestamp", "Out For Break"}}),

    // Remove the "Break Type" column as it's no longer needed
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns", {"Status"}),

    // Calculate the duration between "Start" and "End"
    #"Inserted Time Subtraction" = Table.AddColumn(#"Removed Columns", "Subtraction", each [In For Break] - [Out For Break], type duration),

    // Rename "Subtraction" column to "Length of Break"
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Time Subtraction", {{"Subtraction", "Length of Break"}})
in
    #"Renamed Columns1"
 
Upvote 0
Sufiyan97 said:
Try

Book1
ABCDEFGHIJKL
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:C11,B2:B11=I1),1,3)
J2:J6J2=CHOOSECOLS(FILTER(A2:C11,B2:B11=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.
Click to expand...
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?
 
Upvote 0
lrobbo314 said:
This is the best PQ version I could come up with.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Emp Id"}, {{"Total", each List.Sum(List.Transform(List.Split(_[Date Time],2),each _{1}-_{0}))}})
in
    Group
Click to expand...
Thank you so much. I have implemented this this group is giving Error. I have two separate column for Date and Timing. Would it possible to get the total time in minutes?
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Break_Time"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
        Source,
        {
            {"ID #", type text},
            {"Password", type text},
            {"Status", type text},
            {"Submission ID", type text},
            {"Submission Create Date", type datetime},
            {"Date", type date},
            {"UserName", type text},
            {"Timing Old Setup", type time},
            {"Timing", type time},
            {"Shift", type text},
            {"Total Breaks", Int64.Type},
            {"Month", type text}
        }
    ),
    ConvertTimingToFraction = Table.TransformColumns(
        #"Changed Type",
        {{"Timing", each Duration.TotalSeconds(Time.From(_)) / 86400, type number}}
    ),
    AddDurationColumn = Table.AddColumn(
        ConvertTimingToFraction,
        "Duration",
        each #duration(
            0,
            Number.RoundDown([Timing] * 24),
            Number.RoundDown(Number.Mod([Timing] * 1440, 60)),
            Number.Round(Number.Mod([Timing] * 86400, 60))
        ),
        type duration
    ),
    GroupedTable = Table.Group(
        AddDurationColumn,
        {"ID #"},
        {{"TotalMinutes", each List.Sum(List.Transform([Duration], Duration.TotalMinutes))}}
    )
in
    GroupedTable
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,225,628
Messages
6,186,106
Members
453,337
Latest member
fiaz ahmad

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