Count consecutive days and gaps between days

[Vintage79]

Hello all,

I have an input sheet as follows:

Work Sheet.xlsx
	B	C	D
2	Day	Date	Working
3	Mon	05-Jan-16	W
4	Tue	06-Jan-16	W
5	Wed	07-Jan-16	W
6	Thu	08-Jan-16	W
7	Fri	09-Jan-16
8	Sat	10-Jan-16
9	Sun	11-Jan-16
10	Mon	12-Jan-16
11	Tue	13-Jan-16	W
12	Wed	14-Jan-16	W
13	Thu	15-Jan-16	W
14	Fri	16-Jan-16	W
15	Sat	17-Jan-16	W
16	Sun	18-Jan-16	W
17	Mon	19-Jan-16	W
18	Tue	20-Jan-16
19	Wed	21-Jan-16
20	Thu	22-Jan-16
21	Fri	23-Jan-16	W
22	Sat	24-Jan-16	W
23	Sun	25-Jan-16
24	Mon	26-Jan-16
25	Tue	27-Jan-16
26	Wed	28-Jan-16
27	Thu	29-Jan-16
28	Fri	30-Jan-16	W
29	Sat	31-Jan-16	W
30	Sun	01-Feb-16	W
31	Mon	02-Feb-16	W
32	Tue	03-Feb-16	W
33	Wed	04-Feb-16
34	Thu	05-Feb-16
35	Fri	06-Feb-16
36	Sat	07-Feb-16
37	Sun	08-Feb-16
Sheet1

This allows me to put a 'W' by any day that I worked. Then I have a summary sheet:

Work Sheet.xlsx
	B	C	D	E	F	G	H
2	Work streaks				Time between streaks
3	Start	Finish	Duration		Start	Finish	Duration
4	05-Jan-16	08-Jan-16	4		09-Jan-16	12-Jan-16	4
5	13-Jan-16	19-Jan-16	7		20-Jan-16	22-Jan-16	3
6	23-Jan-16	24-Jan-16	2		25-Jan-16	29-Jan-16	5
7	30-Jan-16	03-Feb-16	5		04-Feb-16
Summary

Cell Formulas
Range	Formula
F4:F7	F4	=IF(C4="","",C4+1)
G4:G7	G4	=IF(B5="","",B5-1)
H4:H7	H4	=IF(G4="","",((DAYS(G4,F4)+1)))
D4:D7	D4	=IF(B4="","",((DAYS(C4,B4)+1)))

At the moment it is set up so that I have to manually enter the dates I started/stopped working, and then the time between work is automated. But is it possible, without using VBA, to use a formula that can enter the start/end dates on the summary page?

Thank you for any help!

 

[Eric W]

Try:

Book1
	B	C	D	E	F	G
2	Work streaks			Time between streaks
3	Start	Finish	Duration	Start	Finish	Duration
4	5-Jan-16	8-Jan-16	4	9-Jan-16	12-Jan-16	4
5	13-Jan-16	19-Jan-16	7	20-Jan-16	22-Jan-16	3
6	23-Jan-16	24-Jan-16	2	25-Jan-16	29-Jan-16	5
7	30-Jan-16	3-Feb-16	5	4-Feb-16
8
Summary

Cell Formulas
Range	Formula
B4:B8	B4	=IFERROR(AGGREGATE(15,6,Sheet1!$C$3:$C$100/(Sheet1!$D$2:$D$99<>"W")/(Sheet1!$D$3:$D$100="W"),ROWS(B$4:B4)),"")
C4:C8	C4	=IF(B4="","",MINIFS(Sheet1!$C$3:$C$100,Sheet1!$C$3:$C$100,">"&B4,Sheet1!$D$3:$D$100,"<>W")-1)
D4:D8	D4	=IF(B4="","",C4-B4+1)
E4:E8	E4	=IFERROR(AGGREGATE(15,6,Sheet1!$C$3:$C$100/(Sheet1!$D$2:$D$99="W")/(Sheet1!$D$3:$D$100<>"W"),ROWS(E$4:E4)),"")
F4:F8	F4	=IFERROR(IF(E4="","",1/(1/MINIFS(Sheet1!$C$3:$C$100,Sheet1!$C$3:$C$100,">"&E4,Sheet1!$D$3:$D$100,"W"))-1),"")
G4:G8	G4	=IF(F4="","",F4-E4+1)

 

[Vintage79]

This is amazing!!! Thank you so much Eric - I really appreciate it.