organizing yearly week number by months

gcmanke2

New Member
Joined
Dec 30, 2016
Messages
2
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:

weekJanFebMarAprMayJunJulAugSepOctNovDec
1159141823273136404449
22610151924283237414550
33711162025293338424651
44812172126303439434752
513223548

<tbody>
</tbody>

For subsequent years, the months with 5 weeks will vary.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,018
Messages
5,856,840
Members
431,836
Latest member
jj0911

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
Top