Time Blocking and Count? (Formula help)

[yessir]

I am curious if there is formula that can help me get a different view of my data. Is there a way to measure the shifts time times from one date to another? For this

A	B	C	D
Location	Time	7/1/2015	6/1/2015
AAA	10:10	1
AAA	10:16		1
AAA	13:53	1
AAA	15:37	1
AAA	16:25		1
AAA	17:35	1
AAA	21:35		1
AAA	21:44	1
BBB	14:03		1
BBB	14:13	1

To then look like something like this?
It would look at the location in Column A and keep the time block if there was a value in the old date area and a value in the new date area and return that during an hour block starting at 10:00 they kept the 10:00.
And for example they lost their 16:00 one hour block. Hopefully that wasn't too confusing...

AAA	Kept 10:00
AAA	Kept 13:00
AAA	Kept 15:00
AAA	Lost 16:00
AAA	Gained 17:00
AAA	Kept 21:00
BBB	Kept 14:00

I'm unsure about the format of it as well? But this is my best visualization. Anything helps! Thanks!

 

[braindiesel]

Trying to understand the logic here to help out...
If the hour (minutes irrelevant) occurs on both days, Kept Hour
If the hour was on previous day but not current day Lost Hour
If Hour is new, Gained Hour?
Wouldn't it be Gained 15:00 not Kept 15:00?

 

[yessir]

You're absolutely right. An oversight on my part.

AAA	Kept 10:00
AAA	Kept 13:00
AAA	Gained 15:00
AAA	Lost 16:00
AAA	Gained 17:00
AAA	Kept 21:00
BBB	Kept 14:00

 

[yessir]

Does that help @braindiesel ? If you have another question just let me know! Anything is appreciated.

 

[braindiesel]

Does that help @braindiesel ? If you have another question just let me know! Anything is appreciated.

No that helped... it just turns out this is a major undertaking... I have been working this for quite a while

 

[braindiesel]

Ever have one of those days where you make something more complicated than necessary...
Here is the simpler version.

Columns F:H can be hidden but they are necessary feeders.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2	Location	Time	7/1/2015	6/1/2015
3	AAA	10:10	1			10	0	1		AAA	Kept 10:00
4	AAA	10:16		1		10				AAA	Gained 13:00
5	AAA	13:53	1			13	1	2		AAA	Gained 15:00
6	AAA	15:37	1			15	1	3		AAA	Lost 16:00
7	AAA	16:25		1		16	-1	4		AAA	Gained 17:00
8	AAA	17:35	1			17	1	5		AAA	Kept 21:00
9	AAA	21:35		1		21	0	6		BBB	Kept 14:00
10	AAA	21:44	1			21
11	BBB	14:03		1		14	0	7
12	BBB	14:13	1			14
Sheet1

Cell Formulas
Range	Formula
F3:F12	F3	=HOUR(B3)
G3:G12	G3	=IF(COUNTIFS($A$3:A3,A3,($F$3:F3),F3)=1,SUMIFS($C$3:$C$12,$A$3:$A$12,A3,$F$3:$F$12,F3)-SUMIFS($D$3:$D$12,$A$3:$A$12,A3,$F$3:$F$12,F3),"")
H3:H12	H3	=IF(G3<>"",1+MAX($H$2:H2),"")
J3:J12	J3	=IF(ROWS($J$3:J3)<=MAX(H:H),INDEX($A$3:$A$12,MATCH(ROWS($J$3:J3),$H$3:$H$12,0)),"")
K3:K12	K3	=IF(J3<>"",CHOOSE(INDEX($G$3:$G$12,MATCH(ROWS($J$3:J3),$H$3:$H$12,0))+2,"Lost ","Kept ","Gained ")&INDEX($F$3:$F$12,MATCH(ROWS($J$3:J3),$H$3:$H$12,0))&":00","")

 

[yessir]

Thank you so much for your help @braindiesel I really do appreciate it. You solved my problem

 

[braindiesel]

Glad to hear it. Thank you for letting me know