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

[sunny_talwar · Accepted solution]

Can you try this:

Interval([step_date] - [create_date],'D') as Difference

[sunny_talwar]

Or just this:

Floor(step_date) - Floor(created_date) as Difference

[boorgura]

Why not simply use the date#() function!

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

[Chanty4u]

try

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

[krishna20]

Hi,

all above solutions are correct

else you try below one

SET DateFormat='DD/MM/YYYY';

[davyqliks]

Thank you,

This returns a dash

Floor(Date_Imported) - Floor([Date Ordered])

Thank you for the reply Sunny, just trying the other option above

[davyqliks]

THank you,

I have just tried this and it returns a dash

Interval(Date_Imported - [Date Ordered],'D')

Thank you for the reply..... Any idea what i am doing wrong here?

Thank you

Daniel

[davyqliks]

Thank you so much for the reply but this returns error in expression :

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

')' expected

Rgds

Daniel

[davyqliks]

Hi,

Thank you for the reply. Please can you explain what this is achieving

SET DateFormat='DD/MM/YYYY';

I have added to the load script

Thank you

Daniel