Sum number of cells that contain time => 2 hours YTD

[Ironman]

Hi

The below extract is from rows A8413:D8629 for this year's training log and a new row is added every day, down to row 20000. The table starts in Row A12. (Columns B and C are irrelevant).

A2 contains today's date.

I need 2 dynamic formulas to sum the number of times I have run for 2 hours or longer in the current year to date and at the same date last year (whichever years they are) from the times in Column D. The result should be 11 for this year.

A solution would be greatly appreciated.

Thank you!

Fri, 1 Jan 2021	REST
Sat, 2 Jan 2021	REST
Sun, 3 Jan 2021	REST
Mon, 4 Jan 2021	REST
Tue, 5 Jan 2021	REST
Wed, 6 Jan 2021	REST
Thu, 7 Jan 2021	REST
Fri, 8 Jan 2021	REST
Sat, 9 Jan 2021	REST
Sun, 10 Jan 2021	REST
Mon, 11 Jan 2021	REST
Tue, 12 Jan 2021	REST
Wed, 13 Jan 2021	REST
Thu, 14 Jan 2021	REST
Fri, 15 Jan 2021	REST
Sat, 16 Jan 2021	REST
Sun, 17 Jan 2021	REST
Mon, 18 Jan 2021	REST
Tue, 19 Jan 2021	REST
Wed, 20 Jan 2021	REST
Thu, 21 Jan 2021	REST
Fri, 22 Jan 2021	REST
Sat, 23 Jan 2021	REST
Sun, 24 Jan 2021	REST
Mon, 25 Jan 2021	REST
Tue, 26 Jan 2021	REST
Wed, 27 Jan 2021	REST
Thu, 28 Jan 2021	REST
Fri, 29 Jan 2021	REST
Sat, 30 Jan 2021	REST
Sun, 31 Jan 2021	REST
Mon, 1 Feb 2021	REST
Tue, 2 Feb 2021	REST
Wed, 3 Feb 2021	REST
Thu, 4 Feb 2021	REST
Fri, 5 Feb 2021	REST
Sat, 6 Feb 2021	REST
Sun, 7 Feb 2021	REST
Mon, 8 Feb 2021	REST
Tue, 9 Feb 2021	REST
Wed, 10 Feb 2021	REST
Thu, 11 Feb 2021	REST
Fri, 12 Feb 2021	REST
Sat, 13 Feb 2021	REST
Sun, 14 Feb 2021	REST
Mon, 15 Feb 2021	REST
Tue, 16 Feb 2021	REST
Wed, 17 Feb 2021	REST
Thu, 18 Feb 2021	REST
Fri, 19 Feb 2021	REST
Sat, 20 Feb 2021	REST
Sun, 21 Feb 2021	REST
Mon, 22 Feb 2021	REST
Tue, 23 Feb 2021	REST
Wed, 24 Feb 2021	REST
Thu, 25 Feb 2021	REST
Fri, 26 Feb 2021	REST
Sat, 27 Feb 2021	REST
Sun, 28 Feb 2021	REST
Mon, 1 Mar 2021	REST
Tue, 2 Mar 2021	REST
Wed, 3 Mar 2021	REST
Thu, 4 Mar 2021	REST
Fri, 5 Mar 2021	REST
Sat, 6 Mar 2021	REST
Sun, 7 Mar 2021	REST
Mon, 8 Mar 2021	REST
Tue, 9 Mar 2021	REST
Wed, 10 Mar 2021	REST
Thu, 11 Mar 2021	REST
Fri, 12 Mar 2021	REST
Sat, 13 Mar 2021	REST
Sun, 14 Mar 2021	REST
Mon, 15 Mar 2021	REST
Tue, 16 Mar 2021	REST
Wed, 17 Mar 2021	REST
Thu, 18 Mar 2021	REST
Fri, 19 Mar 2021	REST
Sat, 20 Mar 2021	REST
Sun, 21 Mar 2021	REST
Mon, 22 Mar 2021	REST
Tue, 23 Mar 2021	REST
Wed, 24 Mar 2021	REST
Thu, 25 Mar 2021	REST
Fri, 26 Mar 2021	REST
Sat, 27 Mar 2021	REST
Sun, 28 Mar 2021	REST
Mon, 29 Mar 2021	REST
Tue, 30 Mar 2021	REST
Wed, 31 Mar 2021	REST
Thu, 1 Apr 2021	REST
Fri, 2 Apr 2021	REST
Sat, 3 Apr 2021	REST
Sun, 4 Apr 2021	REST
Mon, 5 Apr 2021	REST
Tue, 6 Apr 2021	REST
Wed, 7 Apr 2021	REST
Thu, 8 Apr 2021	REST
Fri, 9 Apr 2021	REST
Sat, 10 Apr 2021	REST
Sun, 11 Apr 2021	REST
Mon, 12 Apr 2021	REST
Tue, 13 Apr 2021	REST
Wed, 14 Apr 2021	REST
Thu, 15 Apr 2021	REST
Fri, 16 Apr 2021	REST
Sat, 17 Apr 2021	REST
Sun, 18 Apr 2021	REST
Mon, 19 Apr 2021	REST
Tue, 20 Apr 2021	REST
Wed, 21 Apr 2021	OTHER
Thu, 22 Apr 2021	REST
Fri, 23 Apr 2021	REST
Sat, 24 Apr 2021	REST
Sun, 25 Apr 2021	OTHER
Mon, 26 Apr 2021	REST
Tue, 27 Apr 2021	OTHER
Wed, 28 Apr 2021	REST
Thu, 29 Apr 2021	OTHER
Fri, 30 Apr 2021	REST
Sat, 1 May 2021	OTHER
Sun, 2 May 2021	REST
Mon, 3 May 2021	OTHER
Tue, 4 May 2021	REST
Wed, 5 May 2021	OTHER
Thu, 6 May 2021	REST
Fri, 7 May 2021	REST
Sat, 8 May 2021	REST
Sun, 9 May 2021	OTHER
Mon, 10 May 2021	REST
Tue, 11 May 2021	OTHER
Wed, 12 May 2021	REST
Thu, 13 May 2021	OTHER
Fri, 14 May 2021	REST
Sat, 15 May 2021	OTHER
Sun, 16 May 2021	REST
Mon, 17 May 2021	OTHER
Tue, 18 May 2021	REST
Wed, 19 May 2021	REST
Thu, 20 May 2021	REST
Fri, 21 May 2021	OTHER
Sat, 22 May 2021	REST
Sun, 23 May 2021	OTHER
Mon, 24 May 2021	REST
Tue, 25 May 2021	OTHER
Wed, 26 May 2021	REST
Thu, 27 May 2021	OTHER
Fri, 28 May 2021	REST
Sat, 29 May 2021	OTHER
Sun, 30 May 2021	REST
Mon, 31 May 2021	OTHER
Tue, 1 Jun 2021	REST
Wed, 2 Jun 2021	OTHER
Thu, 3 Jun 2021	REST
Fri, 4 Jun 2021	REST
Sat, 5 Jun 2021	OTHER
Sun, 6 Jun 2021	REST
Mon, 7 Jun 2021	OTHER
Tue, 8 Jun 2021	REST
Wed, 9 Jun 2021	Skendleby, N Lincs (Cottage behind Blacksmith's Arms)	2.0	0:30:40
Thu, 10 Jun 2021	Skendleby, N Lincs (Cottage behind Blacksmith's Arms)	3.6	0:56:44
Fri, 11 Jun 2021	Skendleby, N Lincs (Cottage behind Blacksmith's Arms)	2.0	0:32:19
Sat, 12 Jun 2021	REST
Sun, 13 Jun 2021	OTHER
Mon, 14 Jun 2021	Cull. Road/Viaduct (up and down)/Halifax Road/ Greenside Lane (08/11/2018)	2.2	0:33:59
Tue, 15 Jun 2021	OTHER
Wed, 16 Jun 2021	REST
Thu, 17 Jun 2021	OTHER
Fri, 18 Jun 2021	Cullingworth Rd/Viaduct/ Ling Bob roundabout/ Wilsden/Bents Lane/ Hallas Bridge (09/12/2018)	4.2	0:55:39
Sat, 19 Jun 2021	OTHER
Sun, 20 Jun 2021	REST
Mon, 21 Jun 2021	OTHER
Tue, 22 Jun 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	2:12:20
Wed, 23 Jun 2021	2x OTHER
Thu, 24 Jun 2021	REST
Fri, 25 Jun 2021	OTHER
Sat, 26 Jun 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to 5-Rise Locks/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (25/08/2005)	10.4	2:40:15
Sun, 27 Jun 2021	OTHER
Mon, 28 Jun 2021	REST
Tue, 29 Jun 2021	OTHER
Wed, 30 Jun 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	2:07:10
Thu, 1 Jul 2021	OTHER
Fri, 2 Jul 2021	REST
Sat, 3 Jul 2021	OTHER
Sun, 4 Jul 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC/5-Rise Locks/ 2 Bridges (Swine Lane)/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (04/07/2021)	14.1	3:41:10
Mon, 5 Jul 2021	OTHER
Tue, 6 Jul 2021	REST
Wed, 7 Jul 2021	OTHER
Thu, 8 Jul 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Rd/ Back Lane/Shay Lane/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (23/02/2020)	6.2	1:38:10
Fri, 9 Jul 2021	OTHER
Sat, 10 Jul 2021	REST
Sun, 11 Jul 2021	OTHER
Mon, 12 Jul 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	2:15:04
Tue, 13 Jul 2021	OTHER
Wed, 14 Jul 2021	REST
Thu, 15 Jul 2021	OTHER
Fri, 16 Jul 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	2:14:27
Sat, 17 Jul 2021	OTHER
Sun, 18 Jul 2021	REST
Mon, 19 Jul 2021	OTHER
Tue, 20 Jul 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to 5-Rise Locks/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (25/08/2005)	10.4	2:44:52
Wed, 21 Jul 2021	OTHER
Thu, 22 Jul 2021	REST
Fri, 23 Jul 2021	OTHER
Sat, 24 Jul 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	2:06:27
Sun, 25 Jul 2021	OTHER
Mon, 26 Jul 2021	REST
Tue, 27 Jul 2021	OTHER
Wed, 28 Jul 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)	10.6	2:35:00
Thu, 29 Jul 2021	OTHER
Fri, 30 Jul 2021	REST
Sat, 31 Jul 2021	OTHER
Sun, 1 Aug 2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)	10.6	2:21:55
Mon, 2 Aug 2021	OTHER
Tue, 3 Aug 2021	REST
Wed, 4 Aug 2021	OTHER
Thu, 5 Aug 2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to cannon monument/Back to 3-Rise Locks & over Br/ Brown Cow/Main Road all the way back home (22/04/2007)	15.4	3:48:05

 

[Fluff]

How about

=COUNTIFS(D:D,">="&TIME(2,0,0),A:A,">="&DATE(YEAR(TODAY()),1,1))

and

=COUNTIFS(D:D,">="&TIME(2,0,0),A:A,">="&DATE(YEAR(TODAY())-1,1,1),A:A,"<="&EDATE(TODAY(),-12))

 

[Ironman]

Wow, that's brilliant, thanks ever so much Fluff.

The formula for last year works perfectly!

For some reason, the formula for this year returns 12 but you will see from the cells filled in salmon and gold that it should be 11.

Would you be kind enough to take another look at that please?

Thank you!

 

[Dave Patton]

.try either or both

T202108a.xlsm
	A	B	C	D	E	F	G	H
1
2	7-Aug-21			02:00	11
3	44197	REST			11		1-Jan-21	31-Dec-21
4	44198	REST
1f

Cell Formulas
Range	Formula
D2	D2	=TIME(2,0,0)
E2	E2	=SUMPRODUCT(--(YEAR(A3:A500)=YEAR(A2)),--(D3:D500>=D2))
E3	E3	=COUNTIFS(A3:A500,">="&G3,A3:A500,"<="&H3,D3:D500,">="&D2)
G3	G3	=DATE(YEAR(A2),1,1)
H3	H3	=DATE(YEAR(A2),12,31)

.

 

[Ironman]

Thanks Dave. I've just tested them both and this year's formula works perfectly.

Re last year to date formula, when I removed 1 relevant entry from this year to test it, it unexpectedly reduced the number for last year by 1 as well.

 

[Fluff]

For some reason, the formula for this year returns 12

With the data you posted it returns 11 for me.

+Fluff 1.xlsm
	A	B	C	D	E	F
11	a	b	c	d
12	01/01/2021	REST				11
13	07/08/2020	REST		02:15		1
14	03/01/2021	REST
15	04/01/2021	REST
16	05/01/2021	REST
17	06/01/2021	REST
18	07/01/2021	REST
19	08/01/2021	REST
20	09/01/2021	REST
21	10/01/2021	REST
22	11/01/2021	REST
23	12/01/2021	REST
24	13/01/2021	REST
25	14/01/2021	REST
26	15/01/2021	REST
27	16/01/2021	REST
28	17/01/2021	REST
29	18/01/2021	REST
30	19/01/2021	REST
31	20/01/2021	REST
32	21/01/2021	REST
33	22/01/2021	REST
34	23/01/2021	REST
35	24/01/2021	REST
36	25/01/2021	REST
37	26/01/2021	REST
38	27/01/2021	REST
39	28/01/2021	REST
40	29/01/2021	REST
41	30/01/2021	REST
42	31/01/2021	REST
43	01/02/2021	REST
44	02/02/2021	REST
45	03/02/2021	REST
46	04/02/2021	REST
47	05/02/2021	REST
48	06/02/2021	REST
49	07/02/2021	REST
50	08/02/2021	REST
51	09/02/2021	REST
52	10/02/2021	REST
53	11/02/2021	REST
54	12/02/2021	REST
55	13/02/2021	REST
56	14/02/2021	REST
57	15/02/2021	REST
58	16/02/2021	REST
59	17/02/2021	REST
60	18/02/2021	REST
61	19/02/2021	REST
62	20/02/2021	REST
63	21/02/2021	REST
64	22/02/2021	REST
65	23/02/2021	REST
66	24/02/2021	REST
67	25/02/2021	REST
68	26/02/2021	REST
69	27/02/2021	REST
70	28/02/2021	REST
71	01/03/2021	REST
72	02/03/2021	REST
73	03/03/2021	REST
74	04/03/2021	REST
75	05/03/2021	REST
76	06/03/2021	REST
77	07/03/2021	REST
78	08/03/2021	REST
79	09/03/2021	REST
80	10/03/2021	REST
81	11/03/2021	REST
82	12/03/2021	REST
83	13/03/2021	REST
84	14/03/2021	REST
85	15/03/2021	REST
86	16/03/2021	REST
87	17/03/2021	REST
88	18/03/2021	REST
89	19/03/2021	REST
90	20/03/2021	REST
91	21/03/2021	REST
92	22/03/2021	REST
93	23/03/2021	REST
94	24/03/2021	REST
95	25/03/2021	REST
96	26/03/2021	REST
97	27/03/2021	REST
98	28/03/2021	REST
99	29/03/2021	REST
100	30/03/2021	REST
101	31/03/2021	REST
102	01/04/2021	REST
103	02/04/2021	REST
104	03/04/2021	REST
105	04/04/2021	REST
106	05/04/2021	REST
107	06/04/2021	REST
108	07/04/2021	REST
109	08/04/2021	REST
110	09/04/2021	REST
111	10/04/2021	REST
112	11/04/2021	REST
113	12/04/2021	REST
114	13/04/2021	REST
115	14/04/2021	REST
116	15/04/2021	REST
117	16/04/2021	REST
118	17/04/2021	REST
119	18/04/2021	REST
120	19/04/2021	REST
121	20/04/2021	REST
122	21/04/2021	OTHER
123	22/04/2021	REST
124	23/04/2021	REST
125	24/04/2021	REST
126	25/04/2021	OTHER
127	26/04/2021	REST
128	27/04/2021	OTHER
129	28/04/2021	REST
130	29/04/2021	OTHER
131	30/04/2021	REST
132	01/05/2021	OTHER
133	02/05/2021	REST
134	03/05/2021	OTHER
135	04/05/2021	REST
136	05/05/2021	OTHER
137	06/05/2021	REST
138	07/05/2021	REST
139	08/05/2021	REST
140	09/05/2021	OTHER
141	10/05/2021	REST
142	11/05/2021	OTHER
143	12/05/2021	REST
144	13/05/2021	OTHER
145	14/05/2021	REST
146	15/05/2021	OTHER
147	16/05/2021	REST
148	17/05/2021	OTHER
149	18/05/2021	REST
150	19/05/2021	REST
151	20/05/2021	REST
152	21/05/2021	OTHER
153	22/05/2021	REST
154	23/05/2021	OTHER
155	24/05/2021	REST
156	25/05/2021	OTHER
157	26/05/2021	REST
158	27/05/2021	OTHER
159	28/05/2021	REST
160	29/05/2021	OTHER
161	30/05/2021	REST
162	31/05/2021	OTHER
163	01/06/2021	REST
164	02/06/2021	OTHER
165	03/06/2021	REST
166	04/06/2021	REST
167	05/06/2021	OTHER
168	06/06/2021	REST
169	07/06/2021	OTHER
170	08/06/2021	REST
171	09/06/2021	Skendleby, N Lincs (Cottage behind Blacksmith's Arms)	2	00:30:40
172	10/06/2021	Skendleby, N Lincs (Cottage behind Blacksmith's Arms)	3.6	00:56:44
173	11/06/2021	Skendleby, N Lincs (Cottage behind Blacksmith's Arms)	2	00:32:19
174	12/06/2021	REST
175	13/06/2021	OTHER
176	14/06/2021	Cull. Road/Viaduct (up and down)/Halifax Road/ Greenside Lane (08/11/2018)	2.2	00:33:59
177	15/06/2021	OTHER
178	16/06/2021	REST
179	17/06/2021	OTHER
180	18/06/2021	Cullingworth Rd/Viaduct/ Ling Bob roundabout/ Wilsden/Bents Lane/ Hallas Bridge (09/12/2018)	4.2	00:55:39
181	19/06/2021	OTHER
182	20/06/2021	REST
183	21/06/2021	OTHER
184	22/06/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	02:12:20
185	23/06/2021	2x OTHER
186	24/06/2021	REST
187	25/06/2021	OTHER
188	26/06/2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to 5-Rise Locks/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (25/08/2005)	10.4	02:40:15
189	27/06/2021	OTHER
190	28/06/2021	REST
191	29/06/2021	OTHER
192	30/06/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	02:07:10
193	01/07/2021	OTHER
194	02/07/2021	REST
195	03/07/2021	OTHER
196	04/07/2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC/5-Rise Locks/ 2 Bridges (Swine Lane)/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (04/07/2021)	14.1	03:41:10
197	05/07/2021	OTHER
198	06/07/2021	REST
199	07/07/2021	OTHER
200	08/07/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Rd/ Back Lane/Shay Lane/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (23/02/2020)	6.2	01:38:10
201	09/07/2021	OTHER
202	10/07/2021	REST
203	11/07/2021	OTHER
204	12/07/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	02:15:04
205	13/07/2021	OTHER
206	14/07/2021	REST
207	15/07/2021	OTHER
208	16/07/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	02:14:27
209	17/07/2021	OTHER
210	18/07/2021	REST
211	19/07/2021	OTHER
212	20/07/2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to 5-Rise Locks/ Back to 3-Rise Locks & over Bridge/Brown Cow/ Main Road all the way back home (25/08/2005)	10.4	02:44:52
213	21/07/2021	OTHER
214	22/07/2021	REST
215	23/07/2021	OTHER
216	24/07/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (22/06/2021)	8.8	02:06:27
217	25/07/2021	OTHER
218	26/07/2021	REST
219	27/07/2021	OTHER
220	28/07/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)	10.6	02:35:00
221	29/07/2021	OTHER
222	30/07/2021	REST
223	31/07/2021	OTHER
224	01/08/2021	Cullingworth Rd/Viaduct/ Stn Road/Old Allen Road Back Lane/Wilsden Rd/ Cottingley Rd/Lee Lane/ Cross Lane/Coplowe Ln/ Crack Lane/Main Street/ Harden Ln/Mill Hill Top/ Wilsden Rd/Mad Mile/ Greenside Lane (21/02/2020)	10.6	02:21:55
225	02/08/2021	OTHER
226	03/08/2021	REST
227	04/08/2021	OTHER
228	05/08/2021	Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Up LLC to cannon monument/Back to 3-Rise Locks & over Br/ Brown Cow/Main Road all the way back home (22/04/2007)	15.4	03:48:05
List

Cell Formulas
Range	Formula
F12	F12	=COUNTIFS(D:D,">="&TIME(2,0,0),A:A,">="&DATE(YEAR(TODAY()),1,1))
F13	F13	=COUNTIFS(D:D,">="&TIME(2,0,0),A:A,">="&DATE(YEAR(TODAY())-1,1,1),A:A,"<="&EDATE(TODAY(),-12))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=List!$A$11:$D$228	F12:F13

 

[Fluff]

Dave did not post a formula for year to date last year.

 

[Dave Patton]

"Dave did not post a formula for year to date last year. "

correct

The formulas that I presented show information by calendar year.
Ironman could use SumProduct or Countifs to secure the information that he wants

My apologizes for trying to help.

 

[Ironman]

@Dave - thanks for your time anyway.

@Fluff - Hmm, I just can't see where the discrepancy is between my sheet and what I posted. Could it be because my table starts in row 12, not 1? Cells D2 and D3 contain time-related formulas with results that exceed 2 hours.

 

[Fluff]

Cells D2 and D3 contain time-related formulas with results that exceed 2 hours.

That would be why. Just change the ranges in the formula to suit.