Hi Louise,

What value would you need it to say instead? If you hardcoded it to show it as week 1 then you the first week would have more than 7 days, which would also be confusing.

Hi Chris,

Surely it should be 52 2010, as in the 52nd week of the previous year?

Hi Louise,

I see your problem now, however I think you meant that '01.01.2011' is coming up as week '52.2011'.

Unfortunately this is correct. The date format that you specify does not change depending on which fields you choose. So if you select 'yyyy' then the year will always display as '2011' regardless of what else you have selected. Likewise week is also correct, as it is still part of the previous year. However if you combine these values together the result is misleading, as you have demonstrated.

Instead, it might be better to show the date in its original format, eg 'dd.MM.yyyy' and then have a separate field for 'Week/Year'. You could use a formula to ensure that if the week was >52 and the month was Jan then set the year to year-1, for example:

DATEUNIT([ColA], "week") + " " +
TEXTVALUE(
IF(
AND(DATEUNIT([ColA], "week") > 50 ,DATEUNIT([ColA], "month")=1),
DATEUNIT([ColA], "year") - 1,
DATEUNIT([ColA], "year")
), "####"
)

Where ColA is your date field. This new field would be a text field, so it would only be useful for display purposes.

Yes sorry, I didn't realise I'd typed the wrong year!

I'll have a go with your code (I already did the sep week field) and see howthat looks.

Thanks a mil
Lou

Update:

This is a known problem with week-of-year modelling. In Europe, the ISO week numbering system is commonly used, and for example week 1 2013 actually begins on the last day of 2012.

The symbol "yyyy" identifies the year of the first day of the week - rather than the logical "year of week 1".

As such when you use a format such as "ww-yyyy", you get "01-2012" for week 1 2013.

Omniscope 2.8, available shortly to alpha partners, includes a fix for this problem in the form of an additional date format option "Y", courtesy of an update to the Java VM it uses (Java 7).

For example, the format "ww-YYYY" resolves this issue and results in "01-2013" for week 1 2013.

If, once 2.8 is available, you choose to use this, be aware that your file will not work correctly in Omniscope 2.7 and earlier; it will display "Invalid date format pattern" on open and the dates will have the wrong format. If you expect to publish to older versions, you will need to use a workaround, such as using a "yyyy" with a formula field to correct the issue and result in an integer "year" field.