Formula to calculate last month's total hours and current month to date total hours

Ironman

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a fitness log as below and I'm looking for 2 formulas that calculate the total time shown in column D:

1) for the current month to date and

2) for the previous month.

Please note that the below is an excerpt showing only the last 2 months - the log has some 3,000 rows and begins on row A12.

If it's of assistance in the solution, the current date is shown in cell A2 (not shown in the range below).

A solution would be greatly appreciated.

Many thanks.

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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also be useful if you posted the sample data again with the expected results included so that we can confirm those results and see how/where you want them presented.
 
Upvote 0
Sure, done.

Cells H2:I3 as below - I2 and I3 are the expected results cells. Posting the entire worksheet is impractical as there are over 7,700 rows.

TOTAL NUMBER OF HOURS EXERCISED AUGUST TO DATE
TOTAL NUMBER OF HOURS EXERCISED IN JULY


Hope this is what's needed?

Many thanks again.
 
Upvote 0
Edits to your example
- column A are real dates formatted
- column B shows the category
- rows deleted to provide simple example

You could use an Excel Table.
Results are custom formatted [h]:mm:ss

N.B. You can download this example; click the icon below the f(x) in the header.

T202107a.xlsm
ABCDEFGHI
1DateCategoryDetails
2Tue 01-Jun-21RESTREST1-Jun-21Exer11:03:35
3Wed 02-Jun-21OTHEROTHER1-Jul-21Exer4:35:00
4Wed 02-Jun-21RESTREST
5Sun 13-Jun-21OTHEROTHER
6Mon 14-Jun-21ExerHallas 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.402:40:15
7Mon 14-Jun-21OTHEROTHER
8Tue 15-Jun-21RESTREST
9Tue 15-Jun-21OTHEROTHER
10Wed 16-Jun-21ExerCullingworth 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.802:07:10
11Wed 16-Jun-21OTHEROTHER
12Thu 17-Jun-21RESTREST
13Thu 17-Jun-21OTHEROTHER
14Fri 18-Jun-21ExerHallas 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.103:41:10
15Tue 29-Jun-21OTHEROTHER
16Wed 30-Jun-21ExerCullingworth 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.602:35:00
17Wed 30-Jun-21OTHEROTHER
18Thu 01-Jul-21RESTREST
19Thu 01-Jul-21OTHEROTHER
20Wed 21-Jul-21Exer02:35:00
21Sat 31-Jul-21Exer02:00:00
22
8b
Cell Formulas
RangeFormula
I2:I3I2=SUMIFS($E$2:$E$57,$A$2:$A$57,">="&G2,$A$2:$A$57,"<="&EOMONTH(G2,0),$B$2:$B$57,H2)
 
Last edited:
Upvote 0
Many thanks for your trouble Dave - Unless I've misunderstood, the formulas cover a fixed period i.e. they assume the log started on 1 June and ended on 31 July, but the log is ongoing, so the ranges will change every month. I therefore need the ranges to update each month.
 
Upvote 0
Please review the suggestion; the results are by month.

T202107a.xlsm
ABCDEFGHI
2Tue 01-Jun-21RESTREST1-Jun-21Exer11:03:35
3Wed 02-Jun-21OTHEROTHER1-Jul-21Exer4:35:00
4Wed 02-Jun-21RESTREST1-Aug-21Exer0:00:00
8b
Cell Formulas
RangeFormula
I2:I4I2=SUMIFS($E$2:$E$57,$A$2:$A$57,">="&G2,$A$2:$A$57,"<="&EOMONTH(G2,0),$B$2:$B$57,H2)
 
Upvote 0
Many thanks again Dave. Unfortunately I have a limited understanding of the formula you've kindly provided.

What is the significance of H2 in the formula please? I can't change the columns in the sheet, so if I apply this formula to my worksheet, I don't know what the equivalent of H2 should be.

Thanks again.
 
Upvote 0
From post
Edits to your example
- column A are real dates formatted
- column B shows the category Insert a column and specify the relevant category
- rows deleted to provide simple example

You could use an Excel Table.
Results are custom formatted [h]:mm:ss

N.B. You can download this example; click the icon below the f(x) in the header.
Click on the icon and paste the information into a new empty sheet.

Review the structure and the formula.

I believe you want to recap information based on month (real dates) and category for example (exer).
Check Excel's help for the functions used.
 
Upvote 0
Ironman said:
I2 and I3 are the expected results cells.
Click to expand...
Unfortunately they are blank. ;)

See if this does what you want. Note that the date here is now 1 August.

21 08 01.xlsm
ABCDHI
1
2Current0:00:00
3Previous17:15:10
11
12Tuesday, 1 June 2021REST
13Wednesday, 2 June 2021OTHER
14Thursday, 3 June 2021REST
15Friday, 4 June 2021REST
16Saturday, 5 June 2021OTHER
17Sunday, 6 June 2021REST
18Monday, 7 June 2021OTHER
19Tuesday, 8 June 2021REST
20Wednesday, 9 June 2021Skendleby, N Lincs (Cottage behind Blacksmith's Arms)20:30:40
21Thursday, 10 June 2021Skendleby, N Lincs (Cottage behind Blacksmith's Arms)3.60:56:44
22Friday, 11 June 2021Skendleby, N Lincs (Cottage behind Blacksmith's Arms)20:32:19
23Saturday, 12 June 2021REST
24Sunday, 13 June 2021OTHER
25Monday, 14 June 2021Cull. Road/Viaduct (up and down)/Halifax Road/ Greenside Lane (08/11/2018)2.20:33:59
26Tuesday, 15 June 2021OTHER
27Wednesday, 16 June 2021REST
28Thursday, 17 June 2021OTHER
29Friday, 18 June 2021Cullingworth Rd/Viaduct/ Ling Bob roundabout/ Wilsden/Bents Lane/ Hallas Bridge (09/12/2018)4.20:55:39
30Saturday, 19 June 2021OTHER
31Sunday, 20 June 2021REST
32Monday, 21 June 2021OTHER
33Tuesday, 22 June 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
34Wednesday, 23 June 20212x OTHER
35Thursday, 24 June 2021REST
36Friday, 25 June 2021OTHER
37Saturday, 26 June 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
38Sunday, 27 June 2021OTHER
39Monday, 28 June 2021REST
40Tuesday, 29 June 2021OTHER
41Wednesday, 30 June 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
42Thursday, 1 July 2021OTHER
43Friday, 2 July 2021REST
44Saturday, 3 July 2021OTHER
45Sunday, 4 July 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
46Monday, 5 July 2021OTHER
47Tuesday, 6 July 2021REST
48Wednesday, 7 July 2021OTHER
49Thursday, 8 July 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
50Friday, 9 July 2021OTHER
51Saturday, 10 July 2021REST
52Sunday, 11 July 2021OTHER
53Monday, 12 July 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
54Tuesday, 13 July 2021OTHER
55Wednesday, 14 July 2021REST
56Thursday, 15 July 2021OTHER
57Friday, 16 July 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
58Saturday, 17 July 2021OTHER
59Sunday, 18 July 2021REST
60Monday, 19 July 2021OTHER
61Tuesday, 20 July 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
62Wednesday, 21 July 2021OTHER
63Thursday, 22 July 2021REST
64Friday, 23 July 2021OTHER
65Saturday, 24 July 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
66Sunday, 25 July 2021OTHER
67Monday, 26 July 2021REST
68Tuesday, 27 July 2021OTHER
69Wednesday, 28 July 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
70Thursday, 29 July 2021OTHER
71Friday, 30 July 2021REST
72Saturday, 31 July 2021OTHER
73
Time
Cell Formulas
RangeFormula
I2I2=SUMIFS(D12:D10000,A12:A10000,"<="&EOMONTH(TODAY(),0),A12:A10000,">"&EOMONTH(TODAY(),-1))
I3I3=SUMIFS(D12:D10000,A12:A10000,"<="&EOMONTH(TODAY(),-1),A12:A10000,">"&EOMONTH(TODAY(),-2))
 
Upvote 0
Solution
T202107a.xlsm
ABCDEFG
1DateDetailsMonthTotal
2Fri 21-May-2104:35:00May7:10:00
3Sun 23-May-2102:35:00June2:35:00
4Tue 25-May-21July4:35:00
5Thu 27-May-21August4:00:00
6Wed 30-Jun-21Cullingworth 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.602:35:00
7Wed 30-Jun-21OTHER
8Thu 01-Jul-21REST
9Thu 01-Jul-21OTHER
10Wed 21-Jul-2102:35:00
11Sat 31-Jul-2102:00:00
12Sun 01-Aug-2104:00:00
13
8b
Cell Formulas
RangeFormula
G2G2=SUMIFS($D$2:$D$5000,$A$2:$A$5000,">="&F2,$A$2:$A$5000,"<="&EOMONTH(F2,0))
G3:G5G3=SUMIFS($D$6:$D$4990,$A$6:$A$4990,">="&F3,$A$6:$A$4990,"<="&EOMONTH(F3,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ironman
  • Question Question
Msgbox when value for this year to date greater than this time last year
2
Replies
11
Views
2K
Ironman
Ironman
Ironman
  • Solved
Calculate number of days since last type of entry
Replies
4
Views
768
Eric W
Eric W
Ironman
  • Solved
Small amendment to formula that calculates number of cells since certain word appears in cell
Replies
3
Views
473
Peter_SSs
Peter_SSs
Ironman
  • Solved
Code needed instead of existing formula to match fill colour of cell
2 3
Replies
23
Views
2K
Peter_SSs
Peter_SSs
Ironman
  • Solved
Calculate largest value in column year to date?
2
Replies
12
Views
857
Ironman
Ironman

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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