Week Commencing Dates

[Fishermanc86]

Hi, I have data sent to me that is in week commencing dates which i append to a table. I need to append this detail to another table where the data is grouped into months. For example the data is for weeks 31/12/2012, 07/01/2013, 14/01/2013, 21/01/2013, 28/01/2013 etc.

Is there a formula to combine these weeks so that the above is grouped as January etc. All the ways I have tried so far have failed.

Thanks

 

[CJ_London]

I presume the problem is needing to treat 31/12/2012 as January?

If not then you can simply group by grouping on month(YourDate)

 

[Fishermanc86]

That is the exact problem, I have tried the Month(date) and it will not pull in the data from wc 31/12.

 

[CJ_London]

OK, so I understand the problem fully, I can see that w/c 31/12/2012 is mostly in Jan 2013 but at what point do you consider it to be in december.

Any of these w/c dates finish in January - which ones would you consider don't?

26/12
27/12
28/12
29/12
30/12
31/12

Probably the best way for you to respond is with a 'rule' along the following lines:

'include in the following month if at least 3 days of the week are in the following month'

or

'include in the following month if at least 1 day of the working week is in the following month (working week is Monday to Friday inc)'

 

[Fishermanc86]

A working week for us is Monday to Sunday, so the 31/12 would be the first week of January the other weeks you listed would be in December.

So I would want WC 31/12, 7/1, 14/1, 21/1 & 28/1 as January
Then WC 4/2, 11/2, 18/2 & 25/2 as Febuary and so on.

 

[Brianwarnock]

But the 31/12/2012 is a Monday in December 2012, so why is it January.
You have not answered CJ_LONDON's question, how much of a week has to be in a month for it to qualify for that month?. Read his post again.

Brian

 

[CJ_London]

Thanks Brian - I was going to say the same thing!

In your example 28/1 counts as January and 25/2 counts as February so why is 31/12 treated differently? and what do you do when you get to 30/12/2013, 29/12/2014 etc

 

[spikepl]

It has to do with iso-8601 for calculation of week numbers : http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php

In some European countries week numbers are a popular tool, and there you'd refer to week 1 or week 37, and not to the start date of the week. And as it happens, week 1 in 2013 begins on the 31/12/2012.

 

[Brianwarnock]

Knowing that how does it help one decide which month a week belongs in, is it a simple case that all weeks except the last in December belong in the month that the first day of the week is in, and indecember if the date is after the 24th it belongs in the following year?

Brian

 

[CJ_London]

I am familiar with this week structure and have provided a solution in the past. However it is not normally then converted into months on a week commencing basis but worked on a 4/4/5 basis.

You can create an algorithm using some base point to calculate a week number but by far the easiest is to create a lookup table. particularly since you have a relatively simple requirement.

You need to create a table as follows:

FromDate (primary key, date)
MonthNo integer

Then use this function - change the names to suit your requirements

PS - the link you provided provides all the data you need to create an algorythm - but rather you than me!

Function GetEpochMonth(WCDate as Date) as integer
Dim MyDb as Database
Dim Rst as Recordset
 
    Set Mydb=CurrentDb
    Set Rst=Mydb.OpenRecordset("SELECT MonthNo FROM EpochTbl WHERE FromDate=#" & Format(WCDate,'mmddyyyy') & "#")
    GetEpochMonth=Rst.Fields(0)
    Set Rst=nothing
    Set Mydb=Nothing
 
End Function

You'll need to top it up for future years but it's easy to maintain.

 

[Fishermanc86]

Thank for all your help CJ. I will give this a go.