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.
Bookmarks