Hi,

This is a fairly common issue (see: http://forums.visokio.com/discussion/808). The first week in January begins on Monday 29th December 2008, which is why you see this value.

It does not show when you try and use the format "yyyyww" because Omniscope uses strict date checking. Because the year of the input date (2009) and the year of the output date (2008) are different the date is rejected.

We may add in a "lenient" option to the field organiser in a future Omniscope release to get around this issue, but for now there is a simple solution: Simply create a formula field with the following formula. In the TEXTTODATE function you can set "lenient" to true, as follows:

TEXTTODATE([DateWeek], "yyyyww", true)

Hi Chris,

When I used your formula and set leniency to true. It comes up with the following error


"Error with result for record 1: Cannot store value in a date field: 29-Dec-2008 00:00:00 Date not legal for date format"

Please see attached file

Unfortunately you can't use the date format "yyyyww". This is because applying this date format will result in a date of "200801" which is inconsistent with the original date "200901". This is what the strict date checking does and is why you can encounter problems when working with dates in this format.

To resolve this problem you must use a date format such as "dd/MM/yyyy" on the formula field. You might also want to consider creating a separate "year" and "week of the year" field.

See also:
http://forums.visokio.com/discussion/comment/5961/#Comment_5961