Ironman
Well-known Member
- Joined
- Jan 31, 2004
- Messages
- 1,069
- Office Version
- 365
- Platform
- Windows
Hi
Below is a small extract showing columns A and B of my exercise 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.
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.
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!
Below is a small extract showing columns A and B of my exercise log.
| Book1 | ||||
|---|---|---|---|---|
| A | B | |||
| 8656 | Wed, 1 Sep 2021 | OTHER | ||
| 8657 | Thu, 2 Sep 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) | ||
| 8658 | Fri, 3 Sep 2021 | OTHER | ||
| 8659 | Sat, 4 Sep 2021 | REST | ||
| 8660 | Sun, 5 Sep 2021 | OTHER | ||
| 8661 | Mon, 6 Sep 2021 | Hallas 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 | ||
| 8662 | Tue, 7 Sep 2021 | OTHER | ||
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 | |||
| 7 | 90 | ||
Training Log | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J7 | J7 | = ((COUNTIFS(A:A,">01/01/2021",A:A,"<="& TODAY(),B:B,"<>REST")))-1 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Training Log'!_FilterDatabase | ='Training Log'!$A$11:$I$11 | J7 |
| '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$1 | J7 |
| VBADaysGone | ='Training Log'!$A$4 | J7 |
| VBADaysLeft | ='Training Log'!$A$6 | J7 |
| 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$7744 | J7 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| I1 | Whole number | =5756886689 |
| J1 | Whole number | between 999999999999999000 and 9999999999999990000 |
| I2:I8 | Whole number | =5756886689 |
| J2 | Whole number | between 999999999999999000 and 9999999999999990000 |
| J3 | Whole number | =1234567 |
| J4 | Whole number | between 999999999999999000 and 9999999999999990000 |
| J5:J6 | Whole number | =1234567 |
| J7 | Whole number | =224536587 |
| J8 | Whole number | =753484868664 |
| I9 | Whole number | =5756886689 |
| J9 | Whole 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 | |||
| 8 | 36% | ||
Training Log | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J8 | J8 | =((COUNTIFS(A:A, ">01/01/2021", A:A, "<=" & TODAY(),B:B, "<>REST")))/$A$4 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Training Log'!_FilterDatabase | ='Training Log'!$A$11:$I$11 | J8 |
| '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$1 | J8 |
| VBADaysGone | ='Training Log'!$A$4 | J8 |
| VBADaysLeft | ='Training Log'!$A$6 | J8 |
| 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$7744 | J8 |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| J8 | Cell Value | between 0.5 and 0.666 | text | NO |
| J8 | Cell Value | between 0.66666 and 0.7995 | text | NO |
| J8 | Cell Value | between 0.8 and 1 | text | NO |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| I1 | Whole number | =5756886689 |
| J1 | Whole number | between 999999999999999000 and 9999999999999990000 |
| I2:I8 | Whole number | =5756886689 |
| J2 | Whole number | between 999999999999999000 and 9999999999999990000 |
| J3 | Whole number | =1234567 |
| J4 | Whole number | between 999999999999999000 and 9999999999999990000 |
| J5:J6 | Whole number | =1234567 |
| J7 | Whole number | =224536587 |
| J8 | Whole number | =753484868664 |
| I9 | Whole number | =5756886689 |
| J9 | Whole number | =5588544595 |
| Book1 | |||
|---|---|---|---|
| A | |||
| 4 | 251 | ||
Training Log | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A4 | A4 | =-(DATE(YEAR(TODAY()),1,1)-LOOKUP(10^35,A:A))+1 |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Training Log'!_FilterDatabase | ='Training Log'!$A$11:$I$11 | A4 |
| '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$1 | A4 |
| VBADaysLeft | ='Training Log'!$A$6 | A4 |
| 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$7744 | A4 |
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:
