Difference between two dates

[davyqliks]

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

[boorgura]

Is this not working?

date#(Date_Imported,'MM/DD/YYYY')-date#([Date Ordered],'MM/DD/YYYY')

or num((date#(Date_Imported,'MM/DD/YYYY')-date#([Date Ordered],'MM/DD/YYYY')))

[davyqliks]

HI,

Thank you all for the replies. I'm sure they all worked but the actual problem stopping me from seeing the corrct result is in the load time stamp. This

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

Was missing the TT from the time format and so date i suppose was not recognised as date.

I have now used the Sunny solution.

Thank you again guys, your help helped me to get there on my own

[sunny_talwar]

Awesome, I am glad you were finally able to figure it out.

Please close the thread down by marking correct and helpful responses if you think you have got what you needed.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[davyqliks]

Of course, Thank you so much for your assistance Suny