6 Replies Latest reply: Dec 18, 2012 5:34 AM by 918947 RSS

    Problem with reading English UK date format in Excel

    918947
      When I build and debug the rulebase I get the following error: Error in rulebase: "Cannot perform comparison expression on right expression with type number".

      The actual problem is that it does not read the date correctly in Excel. I have got the following date comparison >=31-09-2011 in excel. Instead of reading 31st September 2011 it tries to reed as 9th of the 31month 2011...


      I have set all language and location, region settings to English United Kingdom (dd/MM/yyyy), United Kingdom but it seems it does not help. In Excel I have set English UK as well.

      OPM version: 10.1.0.27
      Windows 7
      MS Office 7

      I had this problem previously but I resolved by changing language or location setting I think, or language for non-Unicode programs. I cannot remember exactly. But this time it seems it does not work. Reinstalling does not work either.

      Can anybody help with this?
      What settings OPM uses to read the date?

      Thank you!
        • 1. Re: Problem with reading English UK date format in Excel
          Jasmine Lee-Oracle
          When authoring rules you should always use the international date format, e.g. 2011-09-30.

          You can configure input date formats for runtime (e.g. acceptable date formats for entering in Web Determinations), but in the rules you have to use the international format.

          Cheers,
          Jasmine
          • 2. Re: Problem with reading English UK date format in Excel
            Jasmine Lee-Oracle
            BTW, September only has 30 days, so attempting to use 31 Sept is likely to cause problems regardless of the date format...
            • 3. Re: Problem with reading English UK date format in Excel
              918947
              The date format used (31-09-2011) works fine on my colleagues laptops and it was working fine on mine machine as well before re-installing Windows (same Windows)...
              • 4. Re: Problem with reading English UK date format in Excel
                Jasmine Lee-Oracle
                Are you sure you're not thinking about the date format you used when typing in dates at runtime? The OPM Help specifies that you must use the format yyy-MM-dd for rule authoring, so I'm surprised if the rules worked using any other format in the rule authoring.

                See this article: Formatting of attribute values http://docs.oracle.com/html/E38269_01/Content/Reference/Formatting_of_attribute_values.htm (section: Unformatted attribute values): "Unformatted values are used... for any date, date/time or time values used when writing rules in Word or Excel (eg constants, values used in comparisons)". Then in the table in that section it says for date attributes the form is yyyy-MM-dd and gives the example 2007-10-25.
                • 5. Re: Problem with reading English UK date format in Excel
                  918947
                  Jasmine, you are right 31-09-2011 is a wrong date, I just typed any date as an example and didn't realize that it is an impossible date . We use many dates in Excel in a format dd-MM-yyyy (we do not type in date in runtime).

                  In the link you have sent it says that "Formatted attribute values based on rulebase region are used".
                  Date:
                  " if your region was set to the United States you would see the following: MM-dd-yy"
                  "Region set to Australia, which includes the date format dd/MM/yy

                  Region in our project is set to United Kingdom so the date format should be dd-MM-yyyy.

                  I have checked that OPM Project Properties Language is set to English (UK) so it should be fine.


                  The strange thing is that it works fine on other laptops - the date 03-09-2011 is red as 3 September 2011, but on my laptop it is red as 9 March 2011.
                  When it comes to read the date 30-09-2011, on other laptops is red as 30 September 2011, but on mine cannot make a date as there is no month number 30 so it throws the error "Cannot perform comparison expression on right expression with type number"... What actually happens is that on my laptop OPM reads the dates from Excel in American format.

                  As I wrote earlier, It was working fine at my laptop as well before reinstalling Windows. I had this problem long time ago again but I was able to resolve it by changing language,region settings in Windows I think.
                  Now it seems like I have got the same language,region settings in Windows as I had before, my colleagues got the same language, region settings on their machines as well.

                  Iit seems like dd/MM/yyyy is red fine (UK format), but the format dd-MM-yyyy is red in American format...

                  What settings should be set in Windows or Excel or maybe somewhere else OPM to read the date from Excel in English UK format dd-MM-yyyy?
                  • 6. Re: Problem with reading English UK date format in Excel
                    918947
                    I did it!!! :) although it took me a while...

                    After I changed Language for non-Unicode programs to English (United Kingdom) I did not delete the .xgen files before building the rulebase.

                    The solution: delete .xgen files and recompile.


                    Thanks Jasmine for trying to help me!