Really irritating custom date issue !

[#1]

HI,

I have a really annoying issue ...

I have a custom calendar in excel which I load i.e. (uk format)

MyDate                          Custom Week

05/12/2011                         45

06/12/2011                         45

What I would like is to create / set a value for a variable that gives me the custom week number based on today's date i.e

MyVariable=if(MyDate=today(2),Custom Week)

I tried using an on open trigger to do the above after the data has loaded but it just doesn't like the format.

Does anyone have any ideas please ?

Thanks

Bob

[swuehl · Accepted solution]

Bob,

I assume that MyDate is not correctly recognized as Date type.

Either set globally the date format using

SET DateFormat='DD/MM/YYYY';

at the top of your script, or if you only want to parse in that field with that format, use date#() function:

MyDate:

LOAD

Date#(MyDate,'DD/MM/YYYY') as MyDate,

[Custom Week]

FROM YourExcel.xls

Then you should be able to call the correct week in any chart object using:

=only(if( MyDate = today(2), [Custom Week]))

Hope this helps,

Stefan

[#3]

Thankyou - works perfectly !