Calculate "this time last year" values

Ironman

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi and a Happy New Year to you!

The first extract from my sheet cells D4:D5 SHOULD show cumulative distance and hours cycled on my indoor bike as at the same date last year. However they in fact cover the period from when I started the sheet in 2018.

The second extract shows the relevant data input, which starts from Row 11 (I have omitted rows 11:66). The correct values in D4 and D5 should be 4 and 58 respectively. I would be very grateful if an amendment or replacement formula could be provided so that the accumulated data for the corresponding date in the last calendar year is shown in these 2 cells instead.

Many thanks!

Book1
ABCD
19 JAN 2020MILES/TIME THIS WK (WK 2)252hr 00m
2DAYS GONE THIS YEARLAST WK/YR/ROLL 36500hr 00m
39YEAR TO DATE/YEARS2020 TO DATESAME DATE 2019
4MAX INDOOR BIKE DAYS LEFTCUMULATIVE MILES25752
5357CUMULATIVE HOURS249
Indoor Bike
Cell Formulas
RangeFormula
A1A1=UPPER(TEXT(TODAY(),"d mmm yyyy"))
B1B1="MILES/TIME THIS WK " & "(WK " & INT((A1-DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3)+WEEKDAY(DATE(YEAR(A1-WEEKDAY(A1-1)+4),1,3))+5)/7) & ")"
C1C1=SUMIF(All_Bike_Dates,">"&A6,All_Bike_Miles)
D1D1=SUMIF(All_Bike_Dates,">"&A6,All_Bike_Times)
C2C2=SUMIF(All_Bike_Dates,">"&B6,All_Bike_Miles)-C1
D2D2=SUMIF(All_Bike_Dates,">"&B6,All_Bike_Times)-D1
C3C3=YEAR(NOW()) & " TO DATE"
D3D3="SAME DATE " & YEAR(NOW())-1
C4C4=SUM(LogYear_Bike_Miles)
D4D4=SUMIFS($D$11:$D$4998,$A$11:$A$4998,">="&MIN($A$11:$A$4998),$A$11:$A$4998,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
C5C5=SUM(LogYear_Bike_Times)
D5D5=ROUND(SUMIFS($B$11:$B$4998,$A$11:$A$4998,">="&MIN($A$11:$A$4998),$A$11:$A$4998,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))*24,2)
A3A3=VBADaysGone
A5A5=VBADaysLeft
Named Ranges
NameRefers ToCells
All_Bike_Dates='Indoor Bike'!$A$11:INDEX('Indoor Bike'!$A$11:$A$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))C1:D2, C4:D5
All_Bike_Miles='Indoor Bike'!$D$11:INDEX('Indoor Bike'!$D$11:$D$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))C1:C2, C4:D4
All_Bike_Times='Indoor Bike'!$B$11:INDEX('Indoor Bike'!$B$11:$B$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))D1:D2, C5:D5
Last365_Bike_Miles=INDEX(All_Bike_Miles,BikeLast365TopIndex):INDEX(All_Bike_Miles,BikeLast365BottomIndex)C1:C2, C4:D4
Last365_Bike_Times=INDEX(All_Bike_Times,BikeLast365TopIndex):INDEX(All_Bike_Times,BikeLast365BottomIndex)D1:D2, C5:D5
LastYTD_Bike_Miles=INDEX(All_Bike_Miles,BikeLastYearTopIndex):INDEX(All_Bike_Miles,BikeLastYTDBottomIndex)C1:C2, C4:D4
LastYTD_Bike_Times=INDEX(All_Bike_Times,BikeLastYearTopIndex):INDEX(All_Bike_Times,BikeLastYTDBottomIndex)D1:D2, C5:D5
LogYear_Bike_Dates=INDEX(All_Bike_Dates,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Dates,ROWS(All_Bike_Dates))C1:D2, C4:D5
LogYear_Bike_Miles=INDEX(All_Bike_Miles,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Miles,ROWS(All_Bike_Dates))C1:C2, C4:D4
LogYear_Bike_Times=INDEX(All_Bike_Times,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Times,ROWS(All_Bike_Dates))D1:D2, C5:D5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2Cell Value>$K$4textNO
C4Cell Value>=1000textYES
Cells with Data Validation
CellAllowCriteria
C4Whole numberbetween 67 and 69
C5Whole numberbetween 67 and 69
A1Text length=UPPER(TEXT(TODAY(),"dd mmm yyyy"))
B1Whole numberbetween 67 and 69
C1Whole number=54748458
A2,A4Whole numberbetween 67 and 69
B2Whole number=67
C2Whole number=54748458
A3Text length=TODAY()-DATE(YEAR(NOW())-1,12,31)
B3,B7,J81Any value
C3Whole numberbetween 67 and 69
A5Text length=DATEDIF(TODAY(),DATE(YEAR(TODAY()),12,31),"d")
D1Whole number=54748458
D2Whole number=54748458
D3Whole numberbetween 67 and 69
D4:D5Whole number=47548458



Book1
ABCD
10DATETIME (h:mm)DIST. (km)DIST. (mls)
Indoor Bike

Book1
ABCD
6708/01/20191:0023.014.3
6810/01/20191:0026.716.6
6912/01/20191:0023.514.6
7014/01/20191:0025.015.5
7116/01/20191:0024.615.3
7218/01/20191:0024.415.2
7320/01/20191:0024.915.5
7422/01/20191:0024.815.4
7524/01/20191:0022.313.8
7601/02/20191:0026.116.2
7703/02/20191:0024.215.0
7805/02/20191:0024.315.1
7907/02/20191:0026.616.5
8009/02/20191:0027.917.3
8111/02/20191:0026.916.7
8213/02/20191:0022.914.2
8321/02/20191:0025.816.0
8423/02/20191:0021.613.4
8525/02/20191:0025.115.6
8627/02/20191:0026.216.3
8701/03/20191:0022.113.7
8803/03/20191:0023.314.5
8905/03/20191:0026.316.3
9007/03/20191:0022.814.2
9109/03/20191:0022.514.0
9211/03/20191:0023.714.7
9313/03/20191:0023.714.7
9415/03/20191:0021.013.0
9516/03/20191:0024.915.5
9618/03/20191:0022.413.9
9720/03/20191:0024.014.9
9822/03/20191:0026.316.3
9924/03/20191:0023.314.5
10026/03/20191:0025.215.6
10128/03/20191:0019.412.0
10201/04/20191:0021.313.2
10303/04/20191:0024.415.2
10408/04/20191:0025.415.8
10510/04/20191:0024.315.1
10612/04/20191:0022.013.7
10714/04/20191:0026.016.1
10816/04/20191:0020.512.7
10918/04/20191:0023.114.3
11020/04/20191:0020.412.7
11122/04/20191:0023.614.7
11224/04/20191:0024.014.9
11326/04/20191:0020.612.8
11428/04/20191:0024.815.4
11530/04/20191:0021.213.2
11602/05/20191:0022.514.0
11703/05/20191:0024.815.4
11811/05/20191:0024.014.9
11913/05/20191:0024.915.5
12015/05/20191:0022.413.9
12117/05/20191:0020.512.7
12219/05/20191:0022.814.2
12321/05/20191:0024.315.1
12423/05/20191:0021.113.1
12525/05/20191:0021.513.4
12627/05/20191:0021.713.5
12729/05/20191:0022.213.8
12830/05/20191:0024.815.4
12901/06/20191:0024.915.5
13003/06/20191:0022.514.0
13105/06/20191:0023.314.5
13207/06/20191:0022.313.8
13309/06/20191:0024.815.4
13411/06/20191:0021.613.4
13513/06/20191:0023.914.8
13617/06/20191:0024.014.9
13719/06/20191:0021.713.5
13820/06/20191:0024.815.4
13922/06/20191:0023.014.3
14024/06/20191:0023.614.7
14126/06/20191:0021.213.2
14228/06/20191:0021.713.5
14303/07/20191:0020.012.4
14405/07/20191:0022.013.7
14507/07/20191:0021.613.4
14609/07/20191:0022.213.8
14711/07/20191:0021.013.0
14816/07/20191:0019.412.0
14918/07/20191:0023.114.3
15020/07/20191:0021.413.3
15122/07/20191:0021.513.4
15224/07/20191:0023.314.5
15326/07/20191:0021.013.0
15428/07/20191:0022.313.8
15530/07/20191:0020.512.7
15601/08/20191:0022.614.0
15703/08/20191:0021.313.2
15805/08/20191:0019.312.0
15907/08/20191:0023.514.6
16009/08/20191:0021.113.1
16111/08/20191:0023.014.3
16213/08/20191:0020.712.9
16315/08/20191:0023.514.6
16417/08/20191:0019.412.0
16519/08/20191:0020.012.4
16621/08/20191:0022.714.1
16723/08/20191:0020.612.8
16825/08/20191:0022.213.8
16928/08/20191:0020.012.4
17030/08/20191:0021.113.1
17101/09/20191:0020.612.8
17209/09/20191:0021.813.5
17311/09/20191:0021.013.0
17417/09/20191:0020.512.7
17519/09/20191:0023.314.5
17623/09/20191:0019.512.1
17725/09/20191:0022.714.1
17827/09/20191:0020.012.4
17929/09/20191:0019.912.4
18001/10/20191:0020.312.6
18103/10/20191:0020.112.5
18219/10/20191:0020.712.9
18306/11/20191:0020.212.5
18410/12/20191:0020.112.5
18507/01/20201:0021.213.2
18609/01/20201:0019.312.0
Indoor Bike
Cell Formulas
RangeFormula
D67:D186D67=IF(B67>0,C67*0.621,"")
Named Ranges
NameRefers ToCells
All_Bike_Times='Indoor Bike'!$B$11:INDEX('Indoor Bike'!$B$11:$B$9995,COUNTA('Indoor Bike'!$A$11:$A$9995))D67:D186
Last365_Bike_Times=INDEX(All_Bike_Times,BikeLast365TopIndex):INDEX(All_Bike_Times,BikeLast365BottomIndex)D68:D186
LastYTD_Bike_Times=INDEX(All_Bike_Times,BikeLastYearTopIndex):INDEX(All_Bike_Times,BikeLastYTDBottomIndex)D67:D184
LogYear_Bike_Times=INDEX(All_Bike_Times,IFERROR(MATCH(DATE(LogYear-1,12,31),All_Bike_Dates,1),0)+1):INDEX(All_Bike_Times,ROWS(All_Bike_Dates))D185:D186
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D68:D230Cell Valuetop 10% valuestextNO
Cells with Data Validation
CellAllowCriteria
D67:D93Any value
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
D4, shouldn't it be >= Year-1?

=SUMIFS($D$11:$D$4998,$A$11:$A$4998,">="&MIN($A$11:$A$4998),$A$11:$A$4998,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
 
Upvote 0
Not quite sure what that min is there to do? Anyway you need a start date and an end date. Taking today you need to start at 1/1/2019 and end 10/1/2019 (or 1/10/2019 if you are american). So try:

=SUMIFS($D$11:$D$4998,$A$11:$A$4998,">="&DATE(YEAR($A$1)-1,1,1),$A$11:$A$4998,"<="&DATE(YEAR($A$1)-1,MONTH($A$1),DAY($A$1)))

You may as well take advantage of the fact you already have todays date in A1.
 
Upvote 0
Many thanks for your time guys.

Steve - your formula works perfectly (with $D$ substituted for $B$ for D5).
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
486
Asbestos_Jen
Asbestos_Jen
K
  • Solved
Evaluate date range and return the corresponding month and year
Replies
1
Views
146
Scott Huish
Scott Huish
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
S
  • Solved
Count of active employees between dates.
Replies
3
Views
667
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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