Hello-
I'm looking to create a summary table that organizes my running data by weeks and months. In a separate log, I record my daily running miles, pace, etc. The summary table I wish to create is organized as illustrated below - 5 (potential) weeks and 12 months.
I'm looking for an equation that calculates the week number (1 - 52) based on the month and week number (in a month). For months that don't have 5 weeks, it should return a blank.
My plan is to use this equation to execute a sumif() function in each cell below that uses the yearly week number as a criteria to select the days to sum. Hopefully that makes sense.
For 2017, the summary table showing how the yearly week numbers are allocated by month looks like this:
<tbody>
</tbody>
For subsequent years, the months with 5 weeks will vary.
I'm looking to create a summary table that organizes my running data by weeks and months. In a separate log, I record my daily running miles, pace, etc. The summary table I wish to create is organized as illustrated below - 5 (potential) weeks and 12 months.
I'm looking for an equation that calculates the week number (1 - 52) based on the month and week number (in a month). For months that don't have 5 weeks, it should return a blank.
My plan is to use this equation to execute a sumif() function in each cell below that uses the yearly week number as a criteria to select the days to sum. Hopefully that makes sense.
For 2017, the summary table showing how the yearly week numbers are allocated by month looks like this:
week | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
1 | 1 | 5 | 9 | 14 | 18 | 23 | 27 | 31 | 36 | 40 | 44 | 49 |
2 | 2 | 6 | 10 | 15 | 19 | 24 | 28 | 32 | 37 | 41 | 45 | 50 |
3 | 3 | 7 | 11 | 16 | 20 | 25 | 29 | 33 | 38 | 42 | 46 | 51 |
4 | 4 | 8 | 12 | 17 | 21 | 26 | 30 | 34 | 39 | 43 | 47 | 52 |
5 | 13 | 22 | 35 | 48 |
<tbody>
</tbody>
For subsequent years, the months with 5 weeks will vary.