How to get Start and end date of the transacted datas of the day

[Stacy Rueda]

Hi, Good day to all of you.

Can someone help me with my excel problem. I've tried pivot table but it doesn't make sense, the result is not really what I wanted. If someone who has a good heart that can help, I would appreciate it from the bottom of my heart.

So, here it goes..

My data is look like this..

Date/ Date/ Quantity
hh:mm:ss hh:mm:ss


[TABLE="class: grid, width: 334"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Setup Date[/TD]
[TD]Finish Date[/TD]
[TD]Parts Consumption[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2017 9:27[/TD]
[TD="align: right"]6/1/2017 9:33[/TD]
[TD="align: right"]334[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2017 9:54[/TD]
[TD="align: right"]6/1/2017 11:09[/TD]
[TD="align: right"]1563[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2017 11:31[/TD]
[TD="align: right"]6/1/2017 12:56[/TD]
[TD="align: right"]6181[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2017 13:39[/TD]
[TD="align: right"]6/1/2017 14:55[/TD]
[TD="align: right"]2347[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2017 15:12[/TD]
[TD="align: right"]6/1/2017 16:16[/TD]
[TD="align: right"]2412[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2017 16:30[/TD]
[TD="align: right"]6/1/2017 17:11[/TD]
[TD="align: right"]322[/TD]
[/TR]
[TR]
[TD="align: right"]6/2/2017 8:57[/TD]
[TD="align: right"]6/2/2017 10:02[/TD]
[TD="align: right"]1764[/TD]
[/TR]
[TR]
[TD="align: right"]6/2/2017 10:47[/TD]
[TD="align: right"]6/2/2017 18:21[/TD]
[TD="align: right"]13560[/TD]
[/TR]
[TR]
[TD="align: right"]6/2/2017 18:32[/TD]
[TD="align: right"]6/2/2017 18:33[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 8:53[/TD]
[TD="align: right"]6/5/2017 9:38[/TD]
[TD="align: right"]494[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 9:45[/TD]
[TD="align: right"]6/5/2017 10:09[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 10:17[/TD]
[TD="align: right"]6/5/2017 10:44[/TD]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 11:08[/TD]
[TD="align: right"]6/5/2017 11:45[/TD]
[TD="align: right"]585[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 11:49[/TD]
[TD="align: right"]6/5/2017 11:52[/TD]
[TD="align: right"]124[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 12:06[/TD]
[TD="align: right"]6/5/2017 12:12[/TD]
[TD="align: right"]192[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 12:22[/TD]
[TD="align: right"]6/5/2017 12:44[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 13:22[/TD]
[TD="align: right"]6/5/2017 13:59[/TD]
[TD="align: right"]683[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 14:35[/TD]
[TD="align: right"]6/5/2017 15:15[/TD]
[TD="align: right"]1018[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 15:34[/TD]
[TD="align: right"]6/5/2017 16:15[/TD]
[TD="align: right"]261[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 16:34[/TD]
[TD="align: right"]6/5/2017 16:56[/TD]
[TD="align: right"]226[/TD]
[/TR]
[TR]
[TD="align: right"]6/5/2017 17:12[/TD]
[TD="align: right"]6/5/2017 18:19[/TD]
[TD="align: right"]110[/TD]
[/TR]
</tbody>[/TABLE]


This is the result that we want..

[TABLE="class: grid, width: 519"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]parts QTY[/TD]
[TD]operation seconds[/TD]
[/TR]
[TR]
[TD]6/1/2017[/TD]
[TD]2017/6/1 9:27[/TD]
[TD]2017/6/1 17:11[/TD]
[TD]13159[/TD]
[TD]27840[/TD]
[/TR]
[TR]
[TD]6/2/2017[/TD]
[TD]2017/6/2 8:57[/TD]
[TD]2017/6/2 18:33[/TD]
[TD]15334[/TD]
[TD]34560[/TD]
[/TR]
[TR]
[TD]6/5/2017[/TD]
[TD]2017/6/5 8:53[/TD]
[TD]2017/6/5 18:19[/TD]
[TD]4149[/TD]
[TD]33960[/TD]
[/TR]
</tbody>[/TABLE]

If you notice, those in bold font will get the start and end per day, but the quantity will sum the whole quantity per day.

I hope someone will help me please, please..thank you in advance

 

[James006]

Hello,

Once your Pivot Table is inserted ... have you tried the option Group by Days ... ? :wink:

 

[Stacy Rueda]

hi Mr.James006, using pivot table is not good, by using that, time doesn't display which is important in calculating seconds (operating seconds). do you have any idea how can i get the start and end time of the day, btw, calculating of seconds is ok for me, i used formula =ABS(B2-C2)*86400

 

[Stacy Rueda]

I hope someone could help me with this please Thank you in advance.

 

[James006]

Hello,

Have just emailed a sample workbook ...

Hope this will help

 

[Peter_SSs]

Have just emailed a sample workbook ...

Not sure how that was instigated, but it shouldn't be happening. Please refer to #4 of the Forum Rules