Auto-update formula at the start of each year

Ironman

Ironman

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

Below is a small extract showing columns A and B of my exercise log.

Book1
AB
8656Wed, 1 Sep 2021OTHER
8657Thu, 2 Sep 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)
8658Fri, 3 Sep 2021OTHER
8659Sat, 4 Sep 2021REST
8660Sun, 5 Sep 2021OTHER
8661Mon, 6 Sep 2021Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Down LLC to Shipley (Over Dock Ln to post) Back up to 5-Rise Locks Down to 3-Rise & over Br/Brown Cow/Main Rd all the way back home
8662Tue, 7 Sep 2021OTHER
Training Log


The formula in J7 below counts the number of cells in Column B since Jan 1 2021 that do NOT contain the word REST.

Book1
J
790
Training Log
Cell Formulas
RangeFormula
J7J7= ((COUNTIFS(A:A,">01/01/2021",A:A,"<="& TODAY(),B:B,"<>REST")))-1
Named Ranges
NameRefers ToCells
'Training Log'!_FilterDatabase='Training Log'!$A$11:$I$11J7
'Training Log'!All_Log_Dates=OFFSET('Training Log'!$A$12,0,0,'Training Log'!$B$10)J7
Last90Dates=OFFSET(LastLogDate,0,0,-90)J7
LastLogDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)J7
LastRunDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)J7
Log_LastDate=INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))J7
LogYear_Log_Dates=INDEX('Training Log'!All_Log_Dates,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))J7
SaveTime='Training Log'!$A$1J7
VBADaysGone='Training Log'!$A$4J7
VBADaysLeft='Training Log'!$A$6J7
x=OFFSET(LastLogDate,0,0,-90)J7
'Training Log'!YTD_Dates=OFFSET('Training Log'!$A$5491,0,0,366)J7
'Training Log'!Z_CF802DC6_E5EE_4A61_9969_9ED76BC490FF_.wvu.FilterData='Training Log'!$A$11:$J$7744J7
Cells with Data Validation
CellAllowCriteria
I1Whole number=5756886689
J1Whole numberbetween 999999999999999000 and 9999999999999990000
I2:I8Whole number=5756886689
J2Whole numberbetween 999999999999999000 and 9999999999999990000
J3Whole number=1234567
J4Whole numberbetween 999999999999999000 and 9999999999999990000
J5:J6Whole number=1234567
J7Whole number=224536587
J8Whole number=753484868664
I9Whole number=5756886689
J9Whole number=5588544595


The formula in J8 below contains the same as J7 but expressed as a percentage of 2021 to date. A4 below it contains the number of days elapsed year to date.

Book1
J
836%
Training Log
Cell Formulas
RangeFormula
J8J8=((COUNTIFS(A:A, ">01/01/2021", A:A, "<=" & TODAY(),B:B, "<>REST")))/$A$4
Named Ranges
NameRefers ToCells
'Training Log'!_FilterDatabase='Training Log'!$A$11:$I$11J8
'Training Log'!All_Log_Dates=OFFSET('Training Log'!$A$12,0,0,'Training Log'!$B$10)J8
Last90Dates=OFFSET(LastLogDate,0,0,-90)J8
LastLogDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)J8
LastRunDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)J8
Log_LastDate=INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))J8
LogYear_Log_Dates=INDEX('Training Log'!All_Log_Dates,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))J8
SaveTime='Training Log'!$A$1J8
VBADaysGone='Training Log'!$A$4J8
VBADaysLeft='Training Log'!$A$6J8
x=OFFSET(LastLogDate,0,0,-90)J8
'Training Log'!YTD_Dates=OFFSET('Training Log'!$A$5491,0,0,366)J8
'Training Log'!Z_CF802DC6_E5EE_4A61_9969_9ED76BC490FF_.wvu.FilterData='Training Log'!$A$11:$J$7744J8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J8Cell Valuebetween 0.5 and 0.666textNO
J8Cell Valuebetween 0.66666 and 0.7995textNO
J8Cell Valuebetween 0.8 and 1textNO
Cells with Data Validation
CellAllowCriteria
I1Whole number=5756886689
J1Whole numberbetween 999999999999999000 and 9999999999999990000
I2:I8Whole number=5756886689
J2Whole numberbetween 999999999999999000 and 9999999999999990000
J3Whole number=1234567
J4Whole numberbetween 999999999999999000 and 9999999999999990000
J5:J6Whole number=1234567
J7Whole number=224536587
J8Whole number=753484868664
I9Whole number=5756886689
J9Whole number=5588544595


Book1
A
4251
Training Log
Cell Formulas
RangeFormula
A4A4=-(DATE(YEAR(TODAY()),1,1)-LOOKUP(10^35,A:A))+1
Named Ranges
NameRefers ToCells
'Training Log'!_FilterDatabase='Training Log'!$A$11:$I$11A4
'Training Log'!All_Log_Dates=OFFSET('Training Log'!$A$12,0,0,'Training Log'!$B$10)A4
Last90Dates=OFFSET(LastLogDate,0,0,-90)A4
LastLogDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)A4
LastRunDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)A4
Log_LastDate=INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))A4
LogYear_Log_Dates=INDEX('Training Log'!All_Log_Dates,MATCH(DATE(LogYear-1,12,31),'Training Log'!All_Log_Dates,1)+1):INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))A4
SaveTime='Training Log'!$A$1A4
VBADaysLeft='Training Log'!$A$6A4
x=OFFSET(LastLogDate,0,0,-90)A4
'Training Log'!YTD_Dates=OFFSET('Training Log'!$A$5491,0,0,366)A4
'Training Log'!Z_CF802DC6_E5EE_4A61_9969_9ED76BC490FF_.wvu.FilterData='Training Log'!$A$11:$J$7744A4


Apologies if the above is too much information, as all I need is an amendment to the 2 formulas in J7 and J8, replacing the hard date of 01/01/2021 with Jan 1 of whatever the current year is, to avoid having to manually update these formulas at the start of each year.

Many thanks!
 
Last edited:

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
No worries, I've just managed to resolve J7 using this formula
Excel Formula: 
=((COUNTIFS(A:A,">"&DATE(YEAR(TODAY()),1,1),A:A,"<="& TODAY(),B:B,"<>REST")))-1

and J8 using this formula
Excel Formula: 
=((COUNTIFS(A:A, ">"&DATE(YEAR(TODAY()),1,1), A:A, "<=" & TODAY(),B:B, "<>REST")))/$A$4
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

Ironman
  • Solved
Change text in cell when formula result changes
Replies
1
Views
571
Ironman
Ironman
Ironman
  • Solved
Message box every 650 units from today's value
Replies
6
Views
440
Ironman
Ironman
Ironman
  • Question Question
Help with small code addition
Replies
8
Views
409
Ironman
Ironman
Ironman
  • Question Question
Click cell Col i Sheet 1 to create hyperlink to Col J Sheet 2 where dates in sheets match
2
Replies
16
Views
1K
Ironman
Ironman
Ironman
  • Question Question
Two complex formulas returning incorrect values
2
Replies
11
Views
3K
Ironman
Ironman

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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