Need help splitting month totals into week totals

[jolome2001]

Hello-

I'm still new to VBA and do most of my coding with recording and modification so I may need a LOT of help here.

I have a table that lists Part #, forecast values, and dates-in-months. I need to find an easy way to convert the months and values to weeks.
E.g. P0421 - 2018M03 - 30000

and needs to be broken out and written to separate columns as:

P0421 - 201809 - 6000
P0421 - 201810 - 6000
P0421 - 201811 - 6000
P0421 - 201812 - 6000
P0421 - 201813 - 6000

Given a 4,4,5 break per quarter, is there a code that will identify the given month, break it into the correct weeks, and divide the total equally between the weeks? Data would consist of several part# and months at a time (up to 12 months).

Thanks for any and all help.
John

 

[cerfani]

I had a little hard time following what you need to do... Any date can reference the week number 1-53 with Weeknum and the month with Month functions. Months are odd to work with since a month does not have fixed definition in days relative to a year or week. You can normalize things by defining weeks in a year as 365/7 or ~52.14 and a month is 365/12 or 30.41 ... then you can convert things easily

is there a code that will identify the given month

=MONTH()

break it into the correct weeks

=WEEKNUM()

and divide the total equally between the weeks

clarify this part

 

[jolome2001]

Hi cerfani. Thanks for the reply.

To clarify a little, the problem I have is the format used in the company report. The month is posted as 2018M03 (Year=2018 and M03 = March). The "4,4,5" I mentioned assumes the workweek splits are consistent year over year within the quarter (Jan = WW1-4, Feb = 5-8, Mar = 9-13).

I would like a VBA code that will isolate the Month (2018M03 = March 2018) and change it to workweeks (201809, 201810, 201811, 201812, & 201813) then take the total for March (30,000 in my example) and divide it equally in the new workweeks (201809 = 6000, 201810 = 6000, etc).

Does that help explain a little better?