Week Numbers Query? (1 Viewer)

namliam · Jan 4, 2016

Pretty easy to fix this inline without using a (custom) function...

Simply base your week calculation always of the Monday of that week, like so:

Code:

format(YourDate - (weekday(YourDate, 2) - 1), "YYYY/WW",2,2)

Edit: LOL, I gave this solution already on top of this page

Some 11 ! years ago... Hey I didn't get my 10 year anniversary cake 3 years ago!!!

JHB · Jan 4, 2016

What do you get if you put in the date: 31-12-2007 - 06-01-2008.
I get 2007/53 which is wrong.

JHB · Jan 5, 2016

namliam, could you please show what you get if you run the solution you gave on the dates I show in post #22!

namliam · Jan 8, 2016

Very odd indeed, format(Yourdate, "YYYY/WW", vbMonday, vbFirstFourDays)
seems to return week 53 for the date of 31/12/2007

I never knew format or datepart was broken this way?

JHB · Jan 8, 2016

namliam said:
Very odd indeed, format(Yourdate, "YYYY/WW", vbMonday, vbFirstFourDays)
seems to return week 53 for the date of 31/12/2007

I never knew format or datepart was broken this way?

So the code I posted had really a legitimacy, it returns the correct week number and year for all conditions.
Thank you to George Simms!

plog · Jan 8, 2016

Not sure if I completely follow what the aim is, but I love this topic (trying to overlay weeks onto years).

Does 1/1/2000 fail? JHB, when I run your code I get 52-1999. Again, not sure if I follow the aim, so I'm not certain if that's an unexpected result. Just throwing out test cases.

What about 12/31/2001? I get 01-2002.

Minty · Jan 8, 2016

According to Wiki

The Gregorian leap cycle, which has 97 leap days spread across 400 years, contains a whole number of weeks (20871). In every cycle there are 71 years with an additional 53rd week. An average year is exactly 52.1775 weeks long; months average at 4.348125 weeks.

An ISO week-numbering year (also called ISO year informally) has 52 or 53 full weeks. That is 364 or 371 days instead of the usual 365 or 366 days. The extra week is referred to here as a leap week, although ISO 8601 does not use this term. Weeks start with Monday. The first week of a year is the week that contains the first Thursday of the year (and, hence, always contains 4 January). ISO week year numbering therefore slightly deviates from the Gregorian for some days close to 1 January.

I'm sure this won't help.

JHB · Jan 9, 2016

plog said:
Does 1/1/2000 fail? JHB, when I run your code I get 52-1999.

Yes and that is correct!

plog said:
What about 12/31/2001? I get 01-2002.

Yes and that is correct too!

Here is a link where you can control it, for all further dates you can come up with.

http://www.timeanddate.com/calendar/?year=2000&country=2

A@Ron · Jan 11, 2016

namliam said:
Pretty easy to fix this inline without using a (custom) function...

Simply base your week calculation always of the Monday of that week, like so:

Code:

format(YourDate - (weekday(YourDate, 2) - 1), "YYYY/WW",2,2)

Edit: LOL, I gave this solution already on top of this page
Some 11 ! years ago... Hey I didn't get my 10 year anniversary cake 3 years ago!!!

Thank you! This seems to do the trick for me. Only 1 thing: My company is treating the week 12/28 - 1/3 as week 1 of 2016. I tried:

Year/Week: Format([Actual Finish]-(Weekday([Actual Finish],2)-1),"yyyy/ww",2,0)

&

Year/Week: Format([Actual Finish]-(Weekday([Actual Finish],2)-1),"yyyy/ww",2,1)

My week 2 becomes correct but I always seem to miss week 1 and include that data as week 53??? So I have Week 53 & 2 but would like to have Week 1 & 2.

A@Ron · Jan 11, 2016

plog said:
Not sure if I completely follow what the aim is, but I love this topic (trying to overlay weeks onto years).

Does 1/1/2000 fail? JHB, when I run your code I get 52-1999. Again, not sure if I follow the aim, so I'm not certain if that's an unexpected result. Just throwing out test cases.

What about 12/31/2001? I get 01-2002.

In my application, I'm tracking breakdowns on manufacturing machines. Each occurrence has many data points (what is broken, when, who fixed, parts used, etc). We use yyyy/ww to show us trends (increasing or decreasing) to know if actions are making the machine performance more reliable. We prefer to review weekly so this measurement works very well for us.

Week Numbers Query? (1 Viewer)

namliam

The Mailman - AWF VIP

JHB

Have been here a while

JHB

Have been here a while

namliam

The Mailman - AWF VIP

JHB

Have been here a while

plog

Banishment Pending

Minty

AWF VIP

JHB

Have been here a while

A@Ron

Registered User.

A@Ron

Registered User.

Similar threads

Users who are viewing this thread