Sum

C

chadlaw32

Board Regular
Joined
Jul 29, 2014
Messages
84
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I need to add time punches leaving out over lapping times
Columns
A time of punch
D Queue
E Agent Name
F action (clock in or clock out)
J total time between clock in and out
I need to add up the times in column J that match an agent name and times do not over lap
NONEinet-commJohn Doe(W)UNPAUSE
NONEinet-commJohn Doe(W)PAUSE
NONEinet-commJohn Doe(H)UNPAUSE
NONEinet-commJohn Doe(H)PAUSE
NONEeagle-inetFrank ClarkUNPAUSE
NONEeagle-inetFrank ClarkPAUSE
NONEinet-commFrank ClarkUNPAUSE
NONEinet-commFrank ClarkPAUSE
NONEeagle-inetPatrick MahomesUNPAUSE
NONEeagle-inetPatrick MahomesPAUSE
NONEeagle-inetPatrick MahomesUNPAUSE
NONEeagle-inetPatrick MahomesPAUSE
NONEeagle-inetPatrick MahomesUNPAUSE
NONEeagle-inetPatrick MahomesPAUSE
NONEeagle-inetTyreek Hill(W)UNPAUSE
NONEeagle-inetTyreek Hill(W)PAUSE
NONEinet-commTyreek Hill(W)UNPAUSE
NONEinet-commTyreek Hill(W)PAUSE
NONEeagle-inetTyreek Hill(H)UNPAUSE
NONEeagle-inetTyreek Hill(H)PAUSE

<colgroup><col style="mso-width-source:userset;mso-width-alt:4644;width:95pt" width="127"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:1609;width:33pt" width="44"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="mso-width-source:userset;mso-width-alt:4937;width:101pt" width="135"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:1682;width:35pt" width="46"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> </colgroup><tbody>
[TD="width: 127"]time[/TD]
[TD="width: 77"]stamp[/TD]
[TD="width: 44"]callid[/TD]
[TD="width: 68"]queue[/TD]
[TD="width: 135"]agent[/TD]
[TD="width: 70"]action[/TD]
[TD="width: 46"]reason[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 92"]Time logged in[/TD]

[TD="class: xl64, align: right"]10/10/2019 09:17:37[/TD]
[TD="align: right"]1570717057[/TD]

[TD="class: xl63, align: right"]236.65[/TD]

[TD="class: xl64, align: right"]10/10/2019 13:14:16[/TD]
[TD="align: right"]1570731256[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 14:13:13[/TD]
[TD="align: right"]1570734793[/TD]

[TD="class: xl63, align: right"]227.00[/TD]

[TD="class: xl64, align: right"]10/10/2019 18:00:13[/TD]
[TD="align: right"]1570748413[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 06:00:29[/TD]
[TD="align: right"]1570705229[/TD]

[TD="class: xl63, align: right"]121.87[/TD]

[TD="class: xl64, align: right"]10/10/2019 08:02:21[/TD]
[TD="align: right"]1570712541[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 06:00:31[/TD]
[TD="align: right"]1570705231[/TD]

[TD="class: xl63, align: right"]121.27[/TD]

[TD="class: xl64, align: right"]10/10/2019 08:01:47[/TD]
[TD="align: right"]1570712507[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 08:02:19[/TD]
[TD="align: right"]1570712539[/TD]

[TD="class: xl63, align: right"]237.80[/TD]

[TD="class: xl64, align: right"]10/10/2019 12:00:07[/TD]
[TD="align: right"]1570726807[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 13:03:00[/TD]
[TD="align: right"]1570730580[/TD]

[TD="class: xl63, align: right"]202.57[/TD]

[TD="class: xl64, align: right"]10/10/2019 16:25:34[/TD]
[TD="align: right"]1570742734[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 16:37:11[/TD]
[TD="align: right"]1570743431[/TD]

[TD="class: xl63, align: right"]24.53[/TD]

[TD="class: xl64, align: right"]10/10/2019 17:01:43[/TD]
[TD="align: right"]1570744903[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 17:06:57[/TD]
[TD="align: right"]1570745217[/TD]

[TD="class: xl63, align: right"]235.00[/TD]

[TD="class: xl64, align: right"]10/10/2019 21:01:57[/TD]
[TD="align: right"]1570759317[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 18:02:55[/TD]
[TD="align: right"]1570748575[/TD]

[TD="class: xl63, align: right"]175.98[/TD]

[TD="class: xl64, align: right"]10/10/2019 20:58:54[/TD]
[TD="align: right"]1570759134[/TD]

[TD="class: xl63"][/TD]

[TD="class: xl64, align: right"]10/10/2019 09:00:15[/TD]
[TD="align: right"]1570716015[/TD]

[TD="class: xl63, align: right"]239.27[/TD]

[TD="class: xl64, align: right"]10/10/2019 12:59:31[/TD]
[TD="align: right"]1570730371[/TD]

[TD="class: xl63"][/TD]

</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Re: Sum help

Please provide an example of each type of overlapping times.
 
Last edited:
Upvote 0
Re: Sum help

this is from the table above look at the first column the times over lap

[TABLE="class: cms_table"]
<tbody>[TR]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]10/10/2019 17:06:57[/TD]
[TD="align: right"]1570745217[/TD]
[TD]NONE[/TD]
[TD]eagle-inet[/TD]
[TD]Tyreek Hill(W)[/TD]
[TD]UNPAUSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_xl63, align: right"]235.00[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]10/10/2019 21:01:57[/TD]
[TD="align: right"]1570759317[/TD]
[TD]NONE[/TD]
[TD]eagle-inet[/TD]
[TD]Tyreek Hill(W)[/TD]
[TD]PAUSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_xl63"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]10/10/2019 18:02:55[/TD]
[TD="align: right"]1570748575[/TD]
[TD]NONE[/TD]
[TD]inet-comm[/TD]
[TD]Tyreek Hill(W)[/TD]
[TD]UNPAUSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: cms_table_xl63, align: right"]175.98[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64, align: right"]10/10/2019 20:58:54[/TD]
[TD="align: right"]1570759134[/TD]
[TD]NONE[/TD]
[TD]inet-comm[/TD]
[TD]Tyreek Hill(W)[/TD]
[TD]PAUSE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Is there any chance of getting something like:

10/10/2019 17:06:57 1570745217 NONE eagle-inet Tyreek Hill(W) UNPAUSE
10/10/2019 18:02:55 1570748575 NONE inet-comm Tyreek Hill(W) UNPAUSE
10/10/2019 20:58:54 1570759134 NONE inet-comm Tyreek Hill(W) PAUSE
10/10/2019 21:01:57 1570759317 NONE eagle-inet Tyreek Hill(W) PAUSE
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Index Maybe?
Replies
5
Views
1K
Fluff
Fluff
M
  • Question Question
Finding value in a row
Replies
4
Views
1K
MRN227
M
T
  • Question Question
Display sales data in an eye pleasing way
Replies
0
Views
604
TimClayton
T
F
  • Question Question
Time Percentages
Replies
1
Views
909
James006
James006
BenElliott
  • Question Question
Suggestions, please
2
Replies
12
Views
2K
RasGhul
RasGhul

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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