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

Ironman

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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 2021REST
Sat, 2 Jan 2021REST
Sun, 3 Jan 2021REST
Mon, 4 Jan 2021REST
Tue, 5 Jan 2021REST
Wed, 6 Jan 2021REST
Thu, 7 Jan 2021REST
Fri, 8 Jan 2021REST
Sat, 9 Jan 2021REST
Sun, 10 Jan 2021REST
Mon, 11 Jan 2021REST
Tue, 12 Jan 2021REST
Wed, 13 Jan 2021REST
Thu, 14 Jan 2021REST
Fri, 15 Jan 2021REST
Sat, 16 Jan 2021REST
Sun, 17 Jan 2021REST
Mon, 18 Jan 2021REST
Tue, 19 Jan 2021REST
Wed, 20 Jan 2021REST
Thu, 21 Jan 2021REST
Fri, 22 Jan 2021REST
Sat, 23 Jan 2021REST
Sun, 24 Jan 2021REST
Mon, 25 Jan 2021REST
Tue, 26 Jan 2021REST
Wed, 27 Jan 2021REST
Thu, 28 Jan 2021REST
Fri, 29 Jan 2021REST
Sat, 30 Jan 2021REST
Sun, 31 Jan 2021REST
Mon, 1 Feb 2021REST
Tue, 2 Feb 2021REST
Wed, 3 Feb 2021REST
Thu, 4 Feb 2021REST
Fri, 5 Feb 2021REST
Sat, 6 Feb 2021REST
Sun, 7 Feb 2021REST
Mon, 8 Feb 2021REST
Tue, 9 Feb 2021REST
Wed, 10 Feb 2021REST
Thu, 11 Feb 2021REST
Fri, 12 Feb 2021REST
Sat, 13 Feb 2021REST
Sun, 14 Feb 2021REST
Mon, 15 Feb 2021REST
Tue, 16 Feb 2021REST
Wed, 17 Feb 2021REST
Thu, 18 Feb 2021REST
Fri, 19 Feb 2021REST
Sat, 20 Feb 2021REST
Sun, 21 Feb 2021REST
Mon, 22 Feb 2021REST
Tue, 23 Feb 2021REST
Wed, 24 Feb 2021REST
Thu, 25 Feb 2021REST
Fri, 26 Feb 2021REST
Sat, 27 Feb 2021REST
Sun, 28 Feb 2021REST
Mon, 1 Mar 2021REST
Tue, 2 Mar 2021REST
Wed, 3 Mar 2021REST
Thu, 4 Mar 2021REST
Fri, 5 Mar 2021REST
Sat, 6 Mar 2021REST
Sun, 7 Mar 2021REST
Mon, 8 Mar 2021REST
Tue, 9 Mar 2021REST
Wed, 10 Mar 2021REST
Thu, 11 Mar 2021REST
Fri, 12 Mar 2021REST
Sat, 13 Mar 2021REST
Sun, 14 Mar 2021REST
Mon, 15 Mar 2021REST
Tue, 16 Mar 2021REST
Wed, 17 Mar 2021REST
Thu, 18 Mar 2021REST
Fri, 19 Mar 2021REST
Sat, 20 Mar 2021REST
Sun, 21 Mar 2021REST
Mon, 22 Mar 2021REST
Tue, 23 Mar 2021REST
Wed, 24 Mar 2021REST
Thu, 25 Mar 2021REST
Fri, 26 Mar 2021REST
Sat, 27 Mar 2021REST
Sun, 28 Mar 2021REST
Mon, 29 Mar 2021REST
Tue, 30 Mar 2021REST
Wed, 31 Mar 2021REST
Thu, 1 Apr 2021REST
Fri, 2 Apr 2021REST
Sat, 3 Apr 2021REST
Sun, 4 Apr 2021REST
Mon, 5 Apr 2021REST
Tue, 6 Apr 2021REST
Wed, 7 Apr 2021REST
Thu, 8 Apr 2021REST
Fri, 9 Apr 2021REST
Sat, 10 Apr 2021REST
Sun, 11 Apr 2021REST
Mon, 12 Apr 2021REST
Tue, 13 Apr 2021REST
Wed, 14 Apr 2021REST
Thu, 15 Apr 2021REST
Fri, 16 Apr 2021REST
Sat, 17 Apr 2021REST
Sun, 18 Apr 2021REST
Mon, 19 Apr 2021REST
Tue, 20 Apr 2021REST
Wed, 21 Apr 2021OTHER
Thu, 22 Apr 2021REST
Fri, 23 Apr 2021REST
Sat, 24 Apr 2021REST
Sun, 25 Apr 2021OTHER
Mon, 26 Apr 2021REST
Tue, 27 Apr 2021OTHER
Wed, 28 Apr 2021REST
Thu, 29 Apr 2021OTHER
Fri, 30 Apr 2021REST
Sat, 1 May 2021OTHER
Sun, 2 May 2021REST
Mon, 3 May 2021OTHER
Tue, 4 May 2021REST
Wed, 5 May 2021OTHER
Thu, 6 May 2021REST
Fri, 7 May 2021REST
Sat, 8 May 2021REST
Sun, 9 May 2021OTHER
Mon, 10 May 2021REST
Tue, 11 May 2021OTHER
Wed, 12 May 2021REST
Thu, 13 May 2021OTHER
Fri, 14 May 2021REST
Sat, 15 May 2021OTHER
Sun, 16 May 2021REST
Mon, 17 May 2021OTHER
Tue, 18 May 2021REST
Wed, 19 May 2021REST
Thu, 20 May 2021REST
Fri, 21 May 2021OTHER
Sat, 22 May 2021REST
Sun, 23 May 2021OTHER
Mon, 24 May 2021REST
Tue, 25 May 2021OTHER
Wed, 26 May 2021REST
Thu, 27 May 2021OTHER
Fri, 28 May 2021REST
Sat, 29 May 2021OTHER
Sun, 30 May 2021REST
Mon, 31 May 2021OTHER
Tue, 1 Jun 2021REST
Wed, 2 Jun 2021OTHER
Thu, 3 Jun 2021REST
Fri, 4 Jun 2021REST
Sat, 5 Jun 2021OTHER
Sun, 6 Jun 2021REST
Mon, 7 Jun 2021OTHER
Tue, 8 Jun 2021REST
Wed, 9 Jun 2021Skendleby, N Lincs (Cottage behind Blacksmith's Arms)2.00:30:40
Thu, 10 Jun 2021Skendleby, N Lincs (Cottage behind Blacksmith's Arms)3.60:56:44
Fri, 11 Jun 2021Skendleby, N Lincs (Cottage behind Blacksmith's Arms)2.00:32:19
Sat, 12 Jun 2021REST
Sun, 13 Jun 2021OTHER
Mon, 14 Jun 2021Cull. Road/Viaduct (up and down)/Halifax Road/ Greenside Lane (08/11/2018)2.20:33:59
Tue, 15 Jun 2021OTHER
Wed, 16 Jun 2021REST
Thu, 17 Jun 2021OTHER
Fri, 18 Jun 2021Cullingworth Rd/Viaduct/ Ling Bob roundabout/ Wilsden/Bents Lane/ Hallas Bridge (09/12/2018)4.20:55:39
Sat, 19 Jun 2021OTHER
Sun, 20 Jun 2021REST
Mon, 21 Jun 2021OTHER
Tue, 22 Jun 2021Cullingworth 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.82:12:20
Wed, 23 Jun 20212x OTHER
Thu, 24 Jun 2021REST
Fri, 25 Jun 2021OTHER
Sat, 26 Jun 2021Hallas 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.42:40:15
Sun, 27 Jun 2021OTHER
Mon, 28 Jun 2021REST
Tue, 29 Jun 2021OTHER
Wed, 30 Jun 2021Cullingworth 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.82:07:10
Thu, 1 Jul 2021OTHER
Fri, 2 Jul 2021REST
Sat, 3 Jul 2021OTHER
Sun, 4 Jul 2021Hallas 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.13:41:10
Mon, 5 Jul 2021OTHER
Tue, 6 Jul 2021REST
Wed, 7 Jul 2021OTHER
Thu, 8 Jul 2021Cullingworth Rd/Viaduct/ Stn Road/Old Allen Rd/ Back Lane/Shay Lane/ Crack Lane/Main Street/ Bents Lane/Hallas Bridge (23/02/2020)6.21:38:10
Fri, 9 Jul 2021OTHER
Sat, 10 Jul 2021REST
Sun, 11 Jul 2021OTHER
Mon, 12 Jul 2021Cullingworth 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.82:15:04
Tue, 13 Jul 2021OTHER
Wed, 14 Jul 2021REST
Thu, 15 Jul 2021OTHER
Fri, 16 Jul 2021Cullingworth 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.82:14:27
Sat, 17 Jul 2021OTHER
Sun, 18 Jul 2021REST
Mon, 19 Jul 2021OTHER
Tue, 20 Jul 2021Hallas 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.42:44:52
Wed, 21 Jul 2021OTHER
Thu, 22 Jul 2021REST
Fri, 23 Jul 2021OTHER
Sat, 24 Jul 2021Cullingworth 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.82:06:27
Sun, 25 Jul 2021OTHER
Mon, 26 Jul 2021REST
Tue, 27 Jul 2021OTHER
Wed, 28 Jul 2021Cullingworth 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.62:35:00
Thu, 29 Jul 2021OTHER
Fri, 30 Jul 2021REST
Sat, 31 Jul 2021OTHER
Sun, 1 Aug 2021Cullingworth 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.62:21:55
Mon, 2 Aug 2021OTHER
Tue, 3 Aug 2021REST
Wed, 4 Aug 2021OTHER
Thu, 5 Aug 2021Hallas 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.43:48:05
 
Last edited:
Sort by date Sort by votes

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
It works perfectly now, thank you so much! I've now realised the meaning of A:A & D:D thank you.

Can you tell me what the relevance of the TIME(2,0,0) is please? When I entered this from Dave's formula it returned time of day - I'd like to understand how that's relevant as opposed to just the date?

Thanks again!

Edit - I've just realised it's not time of day, it's length of time :)
 
Last edited:
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Formula to find the first and last cell containing data in a row
Replies
9
Views
319
Fluff
Fluff
Ironman
  • Question
Msgbox when value for this year to date greater than this time last year
2
Replies
11
Views
1K
Ironman
Ironman
Ironman
  • Solved
Calculate number of days since last type of entry
Replies
4
Views
632
Eric W
Eric W
Ironman
  • Solved
Formula to calculate last month's total hours and current month to date total hours
2
Replies
11
Views
896
Peter_SSs
Peter_SSs
Ironman
  • Solved
Small amendment to formula that calculates number of cells since certain word appears in cell
Replies
3
Views
370
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top