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

Setup Date	Finish Date	Parts Consumption
6/1/2017 9:27	6/1/2017 9:33	334
6/1/2017 9:54	6/1/2017 11:09	1563
6/1/2017 11:31	6/1/2017 12:56	6181
6/1/2017 13:39	6/1/2017 14:55	2347
6/1/2017 15:12	6/1/2017 16:16	2412
6/1/2017 16:30	6/1/2017 17:11	322
6/2/2017 8:57	6/2/2017 10:02	1764
6/2/2017 10:47	6/2/2017 18:21	13560
6/2/2017 18:32	6/2/2017 18:33	10
6/5/2017 8:53	6/5/2017 9:38	494
6/5/2017 9:45	6/5/2017 10:09	105
6/5/2017 10:17	6/5/2017 10:44	250
6/5/2017 11:08	6/5/2017 11:45	585
6/5/2017 11:49	6/5/2017 11:52	124
6/5/2017 12:06	6/5/2017 12:12	192
6/5/2017 12:22	6/5/2017 12:44	101
6/5/2017 13:22	6/5/2017 13:59	683
6/5/2017 14:35	6/5/2017 15:15	1018
6/5/2017 15:34	6/5/2017 16:15	261
6/5/2017 16:34	6/5/2017 16:56	226
6/5/2017 17:12	6/5/2017 18:19	110

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

This is the result that we want..

date	start	end	parts QTY	operation seconds
6/1/2017	2017/6/1 9:27	2017/6/1 17:11	13159	27840
6/2/2017	2017/6/2 8:57	2017/6/2 18:33	15334	34560
6/5/2017	2017/6/5 8:53	2017/6/5 18:19	4149	33960

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

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