OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Date Format Code WW YYYY

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Thu May 12, 2011 1:37 pm    Post subject: Date Format Code WW YYYY Reply with quote

If I have a cell with the date 12/31/2010 and use the date format code "WW YYYY" it displays as "1 2010". Individually, the "1" and the "2010" make sense, but in combination they don't. That date is NOT week one of 2010, but week one of 2011!

Note that the date 1/1/2010 ALSO displays as "1 2010". Seems they can't BOTH be right.

Is there some way to make this work? That is, I'd like both the following:

01/01/2010 ==> 1 2010
12/31/2010 ==> 1 2011
Back to top
View user's profile Send private message AIM Address
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Fri May 13, 2011 5:56 am    Post subject: Reply with quote

https://bugs.launchpad.net/ubuntu/+source/openoffice.org/+bug/488962
http://www.google.com/support/forum/p/Google%20Docs/thread?tid=7014ebf31973a66f&hl=en

If you change your locale at Format>Cells...>Numbers to Italian(Italy) and the format to WW AAAA you get:

31/12/2010 (or 12/31/2010) ==> 52 2010
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri May 13, 2011 6:53 am    Post subject: Reply with quote

Robert Tucker wrote:

If you change your locale at Format>Cells...>Numbers to Italian(Italy) and the format to WW AAAA you get:

31/12/2010 (or 12/31/2010) ==> 52 2010


Yes, but 1/1/2010 now displays as 53 2010.

So, I can have it one way or the other, but either the week or the year comes out wrong near the year-to-year transition. As I said in my original post, either WW or YYYY can be justified as they are currently shown, but the two always mismatch one way or the other. Changing locales just changes the direction of the mismatch. This is because the WW and the YYYY are not "aware" of each other.

I'm pretty sure with some fancy calculations a combination of two cells (one for week and another for year) could be used to get the correct "WW YYYY", but I didn't want to spend any time on that if I simply did not know how to do it the "right" way.

Perhaps the formatting support could be enhanced to recognize when WW and YYYY are used in the same format string and adjust accordingly.

Or there could be an additional format code that ties together week number and year.

This seems like an OOo bug to me or at least missing functionality.

Oh well, unless someone surprises me with an easier way, I'll work on a two-cell solution.
Back to top
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 13, 2011 8:29 am    Post subject: Reply with quote

The number formatting can not solve the problem for obvious reason. YYYY always shows the year of the number and WW always shows the week number according to ISO, even when the last day of the year belongs to the 1st week of the next year and 31/12/2007 should yield "01 2008" rather than "01 2007".

=TEXT(weeknum(A1;1);"00")&" "&(YEAR(A1)+AND(WEEKNUM(A1;1)=1;MONTH(A1)=12))
concatenates the 2-digit weeknumber with a space " " and with the year number, adding 1 to the year number if a weeknumber of 1 meets the month of December.
The ;1 in WEEKNUM treats Sunday as first day of week. ;2 would assume Monday beeing the first day of a week. Apart from the "wrong" year, number format code WW always assumes Monday.

Your example 12/31/2010 ==> 1 2011 is wrong because that day is in calendar week 52 of 2010.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri May 13, 2011 8:59 am    Post subject: Reply with quote

Villeroy,

Thanks!

That should put me on the right path. My actual situation is slightly more complex, but "WW YYYY" demonstrated the difficulty I was having, so your example tells me how to approach this.

Working on my two-cell solution, I already came up with this for the year...

IF(WEEKNUM_ADD(B17;1)>52;YEAR(B17)+1;YEAR(B17))
Back to top
View user's profile Send private message AIM Address
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri May 13, 2011 9:19 am    Post subject: Reply with quote

Well, I forgot the other case where the last weeknum belongs to the previous year:
Code:
=TEXT(weeknum(A1;1);"00")&" "&(YEAR(A1)+AND(WEEKNUM(A1;1)=1;MONTH(A1)=12)-AND(WEEKNUM(A1;1)>51;MONTH(A1)=1))


This should work with ISO compliant WEEKNUM, Excel compliant WEEKNUM_ADD, with weeks starting on Sunday (mode 1) and Monday (mode 2).

It's easier to comprehend in 2 cells:
Code:
B1 =weeknum(A1;1) [or any other flavour of weeknum]
C1 =TEXT(B1;"00")&" "&(YEAR(A1)+AND(B1=1;MONTH(A1)=12)-AND(B1>51;MONTH(A1)=1))

Add 1 to the year when weeknum 1 meets month 12.
Subtract one from the year when a high weeknum meets month 1.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
paulsen
General User
General User


Joined: 12 May 2011
Posts: 15
Location: United States

PostPosted: Fri May 13, 2011 9:37 am    Post subject: Reply with quote

Villeroy,

Thanks again. I'd love to get a modulo divide by 52 or 53 in there somewhere but there always seems to be something that ends up 0!
Back to top
View user's profile Send private message AIM Address
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group