Convert Weeks to Months 1

[PurpleUnicorn]

I have a table containing fields wk1 through wk52. I need to convert this data to mon1 - mon12. wk1 will always be the first week of the current month. I was thinking that I would need to convert weeks to days first, then somehow populate my months fields, but I'm not exactly sure how to go about this.

Any help will be greatly appreciated!

 

[TheAceMan1]

How are ya PurpleUnicorn . . .

How do you intend to handle those [blue]weeks (quite a few) that crossover into the next month[/blue] . . . wether at the beginning, middle, or end of the week?

See Ya! . . . . . .

 

[SkipVought]

Hi,

Week 1 is with respect to a REFERENCE DATE, for instance Jan 2, 2005.

So week n can be "converted" to a date...

MyDate = (n - 1) * 7 + RefDate

Format to suite.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[PurpleUnicorn]

Thanks for the replys - I don’t think I explained my problem with enough detail.

Here is what I have:

Week1: 500
Week2: 1200
Week3: 200
Week4: 10
Week5: 2200 and so on...

I need to know the totals by month. So I need to end up with:
Month1(in this instance – December): total for December.
Month2: total for Jan and so on...

I will always be working with 12 months (or 52 weeks) of data starting with week1 as the first week of the current month. My calculations do not need to be exact – so I can ignore leap year.

My thoughts are that I could store the data in an array – for each day.
So divide each week total by 7 – store that value in the corresponding 7 spots in the array

Then I thought I could get my month values (not sure how to do this part) by traversing the array for the correct number of days for each month.

So for december -
I would end up with all of weeks 1 - 4 (28 days) = 1910
then week 5 divided by 7 is 314 (I rounded) * 3 = 942.
I would have a total for month1 of 2852.

Any ideas how this can be done?

 

[SkipVought]

Seems like you have the algorithm down pat.

Just "convert" each week to a REAL DATE and you'll be able to figgure out the remainder for each month.

Tip: to get days per month (takes care of leap year nicely)

y= year
m = month
DPM = Day(DateSerial(y, m+1, 0))

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[PurpleUnicorn]

Thanks so much for your help (and the affirmation).
So far, I have the array loading - hopefully the rest will go smoothly!

Thanks again!