Creating my own Week Number

[Sarkman22]

Hello all thanks for viewing. I have a calendar table that lists one row entry per date and it will be dynamic as I want to run queries down the road and "look back 1 year from today". What I would like to do is create my own week number based on the dates displayed. Simply I want to take the first 7 sequential days (oldest to newest) and have a column that says "1". Then I want to take the 2nd set of 7 days and have it say "2" and continue on from there. I have no idea how to do this any help would be greatly appreciated! Any questions or if this is unclear please let me know -
thanks so much!

-Andy

 

[plog]

First, 365 is not divisible by 7. So you are going to have to be real specific in what you mean when you compare a week in one year to one in another year. Some years can span 54 weeks.

With that said, if you know what the date is for Week 1, day 1, then you can achieve what you want in terms of numbering what week each date falls in.

You would use the DateDiff function (https://www.techonthenet.com/access/functions/date/datediff.php) to see how many days away a date is from your starting date. Divide that by 7 and you have your week number.

 

[Sarkman22]

Thanks for your reply- I appreciate it. I think you might be missing my intent just a bit. I don't care about really care about weekdays, weekends year, etc and won't be comparing year to year. I don't believe the DateDiff function will work as it seems to be built off traditional calendar logic (there is always a "first day of the week" even if it might be Tuesday). While you are correct 365 isn't divisible by 7 I can alter that and it's fine. So I can take a 364 day set and regardless of what day of the week the first date is I want the first set of of 7 sequential days to say "1", the second set of 7 days to say "2" all the way up to 52.. So at the end I want a clean set of 52 sets of 7 numbered 1-2-3, etc. Hope that makes sense....

EDIT: One more thing that might help clarify. This doesn't even really apply to dates necessarily- it could be anything. I could have a list of 364 names and I want the first 7 to have a column next to it that says "1" , the second 7 names would say "2", etc. It just happens to be dates......Thanks!

 

[MarkK]

Hi, I think you can calculate this number with the formula...

WeeksFromDayOneOfTheYear = (DatePart("y", [COLOR="Blue"]< date to test >[/COLOR]) - 1) \ 7 + 1

This works by using the DatePart() function to return the day of the year, which of course always starts at 1. Then we divide the 'day of the year' by seven, offset it, truncate it, un-offset it, and we have our custom "weeks from day 1 of the year" week number.

Best way to use this is create a calculated field in a query, or I guess a calculated field in the newer versions of Access that allow it at the table level.

hth
Mark

 

[Sarkman22]

Thanks Mark!

I played around with your code / comments for awhile and couldn't quite get what I needed (I'll spare you the details unless you really want them).

However - I figured out exactly what I needed by essentially locking a "start date" (min of date data set) and then counting number of days from that date, dividing by 7, truncating, and offsetting by 1. You definitely sent me on the right path so your time and comments are very much appreciated! Thanks again.

Hi, I think you can calculate this number with the formula...

WeeksFromDayOneOfTheYear = (DatePart("y", [COLOR="Blue"]< date to test >[/COLOR]) - 1) \ 7 + 1

This works by using the DatePart() function to return the day of the year, which of course always starts at 1. Then we divide the 'day of the year' by seven, offset it, truncate it, un-offset it, and we have our custom "weeks from day 1 of the year" week number.

Best way to use this is create a calculated field in a query, or I guess a calculated field in the newer versions of Access that allow it at the table level.

hth
Mark

 

[MarkK]

Glad to help!

 

[The_Doc_Man]

Am I being dense or did everyone forget that with DatePart and Format, you can TELL those functions which day is the first day of the week? The optional third argument lets you start the first week of the year on a specific day of the week and the optional fourth argument tells you things like which day of the year counts as the the start for that starting week. (It could even be the first day of the week that contained Jan 1st even if that day wasn't the 1st day of the year.)

Using forced integer division via "" will certain give you the week number so MarkK's comments were not off center in any way. However, there are many ways to reach a solution. I am glad you have one. I just wonder if some "over-thinking" may have occurred. One real suggestion - be sure to document any code segments carefully so you will remember WHY you chose a particular method.

 

[MarkK]

Doc Man, the way I understood the problem is that Sarkman wanted complete seven day weeks starting on day one of the period, and DatePart("ww", <date>) doesn't work that way. If we do this in the immediate pane...

? DatePart("ww", #1/3/16#)
 2

...we see that the third day of the year in 2016 was already in the second week, but even the seventh day of 2017 ...

? DatePart("ww", #1/7/17#)
 1

... is still in week 1. The "Week 1" rendered by DatePart("ww", ...) does not necessarily have seven days in it.

To compare apples to apples in this case, we need to calculate weeks as a function of days since the start of the period, and not as periods starting/ending on a particular weekday.

 

[The_Doc_Man]

MarkK: Screen capture from my Immediate Window.

debug.Print DatePart("w", #1/1/16#)
6

The number six is equal to vbFriday. So...

debug.Print datepart("ww", #1/3/16#, vbFriday )
1


Your other example:

debug.Print DatePart("w", #1/7/17#)
7


Now 7 is vbSaturday. If the first day of the year was a Sunday then the following Saturday WOULD be counted in the first week. AND... Sunday is the default "first day of the week" so the stars aligned to make it come out that way.

The only catch here is that if this is a sliding window algorithm that doesn't necessarily start so that all the days are in the same calendar year, the calculation would be more complex. But the OP said he "won't be comparing year to year" (in post #3). Which is why I stepped into the fracas.