Week number and Leap Year

[ErnstNoto]

Hi!

I've had some reports the last couple of years where I report on week numbers. This has been working just fine up until now. 2016 It is a Leap year and 2015 had 53 weeks is. The formula I've used in Crystal wont identify this: DATEPART("ww",{@Regdato}, crmonday)

Anyone who has an idea on how to create the correct number of weeks?



Best Regards
Ernst Noto

 

[Kim296]

I don't understand your question. There is one extra day in Feb; there isn't a whole extra week. Your report should still work. Please explain a little more detail.

 

[SkipVought]

Is it a problem that 2015 has 53 weeks?

What IS "the correct number of weeks?"

Some start the week on Sunday. Others on Saturday or Monday. The constant is that a "week" is ALWAYS 7 days.

It's not at all clear what your issue is???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ErnstNoto]

The problem is that the datepart formula wont give me week nbr 53. When using the datepart formula above, Crystal will not make week nr 53, the year still ends in week nbr 52. And the first week of 2016 is stated as week nbr 2. I Guess the problem really is that week 53 exists both in 2015 and 2016.

31 of desember 2015 is in week 53
01 January is also in week 53
Week nbr 1 in 2016 starts With the 4 of january, but when using datepart the first shown week will be week nbr 2, and the 4 of january will be stated as a date in week 2, which is wrong.

Hope that cleared up my question



Best Regards
Ernst Noto

 

[SkipVought]

Date manipulation is tricky. I have tried to understand the basic arithmetic and have used the following formula to locate the starting DOW (ie in your case MONDAY)

=INT(([TheDate]-rOFF)/7)*7+rOFF

...where the rOFF is 2 for MONDAY in Excel. Not sure what it would be in CR. So then MIN of this for 2016 is 1/4/2016

[pre]
Date Week of DOW
12/31/2015 12/28/2015 Thu
1/1/2016 12/28/2015 Fri
1/2/2016 12/28/2015 Sat
1/3/2016 12/28/2015 Sun
1/4/2016 1/4/2016 Mon
1/5/2016 1/4/2016 Tue
1/6/2016 1/4/2016 Wed
1/7/2016 1/4/2016 Thu
[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

I don't understand the problem at all. I am using CR 11 and get the following results, which as far as I can see is correct:

1 Jan 2016 = Week 1
2 Jan 2016 = Week 1
3 Jan 2016 = Week 1
4 Jan 2016 = Week 2 (because it is a Monday).

Are you saying the results above are wrong, or that your formula gives you different results?

Pete

 

[SkipVought]

@Pete,

The last week of 2015 is week 53
[pre]
Date Week
12/28/2015 53
12/29/2015 53
12/30/2015 53
12/31/2015 53
1/1/2016 53
1/2/2016 53
1/3/2016 53
[/pre]

Therefore 1/4/2016 would be the first day of week 1 for 2016, unless a week can be something other than 7 days. This is one of those tricky/ambiguous date related definitions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pmax9999]

Hey Skip. Yes you are correct in that the formula returns week 53 for 31 Dec 2015, Week 1 for 1 Jan 2016 and Week 2 for 4 Jan 2016.

I haven't personally ever needed to use week numbers in any of the reporting I have ever done. At this stage all I am saying is what my installation of Crystal (v11) is returning using the formula the OP says is being used.

Without ever having needed the function, it is doing what I would have expected in that the first 3 days of Jan 2016 are week 1, changing to week 2 on Monday 4 January 2016. In effect, Week 53 of 2015 and week 1 of 2016 represent the same 7 days.

Not sure what else to say. At this point, and assuming I understand the OP's issue (and I'm not certain I do), it seems the real issue is that the OP's CR installation is returning a different result to mine. That being the case, I would like him to confirm the formula he is using, and to post the code used in the formula {@Regdato}. The only thing I can think of is that there is something in that other formula that is impacting the result.

Cheers
Pete

 

[SkipVought]

So it seems that if you start the first week of the year to include any day in the previous year, you'll end up with a week 53/week 1 overlap. And this is how the DATEPART() function functions. If you start the first week in the year, then most of the time you'll have a week 52/week 1 overlap.

So your organization 1) must be aware of this anomaly and 2) must decide what convention to use to accommodate this. Accounting organizations handle these things in the definition of accounting weeks and accounting month: 4 week months and 5 week months where these "months" do not exactly coincide with the actual months' days.

Its why I use a formula like INT(([TheDate]-rOFF)/7)*7+rOFF to define week groupings and then decide how to handle the anomalies.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ErnstNoto]

Hi all!
thank you all for youre replies.

Skip: You are correct, when using the formula corrctly, it will Return week 53. Not sure what I have been doing wrong. Works as expected now.


Best Regards
Ernst Noto