To handle european calender rules a 53 week

  • 1
  • Problem
  • Updated 1 year ago
  • Solved
Week number turns out wrong in european calender since week 53 in 2015 is missing. Week 1 start on 4. January 2016. in Replicon this is week 2. And this is wrong for European calenders.
Photo of Thu, Kari (thu)

Thu, Kari (thu)

  • 1 Post
  • 0 Reply Likes

Posted 3 years ago

  • 1
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1558 Posts
  • 108 Reply Likes
Hi Kari,

Thank you for using Replicon Community!

May I know if you are facing this issue while running a time report? If yes, may we request you to please run the report on Timesheet Hours by Day report template? This template will show 4th March in Week 1.

Thanks,
Aashnee
Photo of Tol, Johan

Tol, Johan

  • 4 Posts
  • 0 Reply Likes
In any report where the "Week (Entry Date)" is used, the week number is 1 too high. Today is 13 July 2016 and Web Timesheet shows it as week number 29. However, in Europe this week is week number 28. I'd like to configure it somewhere in the System Preferences.
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1558 Posts
  • 108 Reply Likes
Hi Johan,

To confirm our work we needed a small information. Can we connect with you through a quick call?

Thanks,
Aashnee Kamboj
Photo of Tol, Johan

Tol, Johan

  • 4 Posts
  • 0 Reply Likes
I'm having holidays right now, so I am not easily reachable. I suggest having contact (after my holidays) in week 34. But what week is week 34 ;-) 

In your e-mail you are referring to a "work week formula", so maybe the following information will do for now: In Europe, the week number formula is in confirmity with the ISO8601 and that is slightly different from the Gregorian calendar. In ISO8601 the first week of the year (week number 1) is the week in which at least 4 days of January fall (this is equal to: the week in which the first Thursday of January falls; this is equal to: the week in which the 4th of January falls). In 2016 the first Thursday is 7 January, so week number 1 is the week starting on Monday 4 January, and hence today, 1 August, is week number 31 in Europe.

See also: https://en.wikipedia.org/wiki/ISO_week_date
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1558 Posts
  • 108 Reply Likes
Hi Johan,

You just gave me the perfect answer. Thanks for the confirmation. I will get back to you with a resolution.

Thanks,
Aashnee 
Photo of Aashnee Kamboj

Aashnee Kamboj, Community Moderator

  • 1558 Posts
  • 108 Reply Likes
Hi Johan,

Please create a custom column in your report as follows:

- Click on the report in question
- Click on Settings
- Scroll Down and Add a custom column
- Name it as per your requirement
- Add the following formula in the Formula section:
IF(MOD(Daydiff(date(1990,1,7),date(Year(EntryDate),1,1)),7)<=4,Ceiling((Daydiff(date(Year(EntryDate),1,1),EntryDate)+MOD(Daydiff(date(1990,1,7),date(Year(EntryDate),1,1)),7))/7),IF(Ceiling((Daydiff(date(Year(EntryDate),1,1),EntryDate)+MOD(Daydiff(date(1990,1,7),date(Year(EntryDate),1,1)),7)-7)/7)=0,53,Ceiling((Daydiff(date(Year(EntryDate),1,1),EntryDate)+MOD(Daydiff(date(1990,1,7),date(Year(EntryDate),1,1)),7)-7)/7)))

- Save and Run the report.

Thanks,
Aashnee
Photo of Tol, Johan

Tol, Johan

  • 4 Posts
  • 0 Reply Likes
Hu Aashnee,

The formula works perfectly. The only drawback is that it is not possible to use the custom column in the Group By function.

Product enhancement suggestion: add a global configuration parameter: System Preferences > Time Zone > Week numbering, with choices { American, European, other? } (or { Gregorian, ISO8601, other? }).

Thank you for your effort!
Johan