13 Replies Latest reply: Jul 11, 2016 5:38 AM by David Nichols RSS

    Difference between two dates

    David Nichols

      Hi,

      I am struggling in my current doc (import from excel) to fin the difference between two dates. i have this working fine in 1 doc

      date((date("step_date",'MM/DD/YYY')-date(create_date,'MM/DD/YYYY')),'DD')*1

      However, when i transpose this to another doc and change fields it returns null -

      here is the transposed code:

      date((date(Date_Imported,'MM/DD/YYY')-date([Date Ordered],'MM/DD/YYYY')),'DD')*1

       

      The format of the date for both fields being imported from excel is like so   7/6/2016 12:00:00 AM

       

      I am really struggling to see the difference and why this is not working:

      here is the load SET

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='£#,##0.00;-£#,##0.00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='MM/DD/YYYY';

      SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

       

      and the sheet it is being loaded from

       

      LOAD TEST_Order_No as [Order Reference], 

           Supplier_Code,

           //[Suplier Name] as [Supplier Name],

           Web_Order_No,

        Date_Imported,

           Date_Order as [Date Ordered],

           order_status,

           Tracking_Status,

           locationCode as [TEST Location],

           [Item No.] as [TEST ITem Ref],

           [Item Type]

      FROM

      K:\....................

       

      Thank you so much, i really hope you can help with this.....So annoying as i had it working before.

      Rgds

      Daniel