+ Reply to Thread
Results 1 to 5 of 5

Sorting Data Presented Monthly into Weekly "Buckets"

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Sorting Data Presented Monthly into Weekly "Buckets"

    Months to Weeks Calculator

    I was handed this problem by a co-worker and decided to extract the logic into a more generic (and nonproprietary) solution.

    Sometimes production forecasts or budgeting is done on a weekly basis, but the data is only available on a monthly basis.

    The way to divide the data is to figure out the daily average for each day in the month and then apply that to the number of days in the week that falls within the month.

    The main issue is determining how many days within a week fall into a particular month when the week spans over a month end.

    This tool provides a method to do that.

    The green shaded cells are where data entry occurs.
    • In Cell C7, enter the year. This cell is used to calculate the month start and month end dates.
    • On Row 7, enter the Monthly Metrics.
    • In Cell C14 enter the first day of the business year. The Week Begin and Week End dates are calculated from this entry.

    The rest of the sheet is calculated from these entries.
    • Row 9 contains the number of days in a month.
    • Rows 16 and 17 show the month in which the week begins and ends respectively.
    • Rows 19 and 20 calculate the number of days in the week that fall into the month associated with the week start and the month associated with the week end.
    • The Weekly metric is calculated by applying the following logic: Number of days in week start month * start month daily metric + Number of days in week end month * end month daily metric.

    Most of the rows can be hidden.

    In this example, I made one modification. In week 53, some of the days overlapped into 2018. I zeroed these out manually since we did not want to include this part of the data.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Sorting Data Presented Monthly into Weekly "Buckets"

    Hi dflak,

    How do you deal with leap years, where Feb has 29 days?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Sorting Data Presented Monthly into Weekly "Buckets"

    It's automatically calculated by the formula.

    Suppose Cell A1 has a date (like Feb 1, 2016) then =Date(YEAR(A1),MONTH(A1)+1,0) will give the last day of the month in Cell A1.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,338

    Re: Sorting Data Presented Monthly into Weekly "Buckets"

    Can't you use the EOMONTH function for that?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Sorting Data Presented Monthly into Weekly "Buckets"

    Indeed you can! I went with something I learned a long, long time ago. I use the DATE() function so much, that it's first on my mind.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Excel Smart Price Buckets "Pivot"
    By adler212 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-04-2017, 12:59 PM
  3. [SOLVED] Transforming data from Monthly to Weekly buckets
    By tomjoe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2017, 10:53 AM
  4. Table about commission (daily, monthly, weekly"
    By economistegypt2010 in forum Excel General
    Replies: 9
    Last Post: 01-03-2015, 08:48 PM
  5. If "6343" in cell A3 matches "monthly" in B3 show a "Y" in C3
    By laaxy in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-05-2014, 07:38 PM
  6. Showing a different weekly "score" from a monthly chart
    By ldurham in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2013, 09:54 AM
  7. [SOLVED] Need Macro to compile data from "Weekly" sheets and sum up into "Monthly" summary sheet
    By mo4391 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-21-2013, 07:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1