Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you try this:
Interval([step_date] - [create_date],'D') as Difference
Can you try this:
Interval([step_date] - [create_date],'D') as Difference
Or just this:
Floor(step_date) - Floor(created_date) as Difference
Why not simply use the date#() function!
date#(Date_Imported,'MM/DD/YYYY')-date#([Date Ordered],'MM/DD/YYYY')
try
date((date#(Date_Imported,'MM/DD/YYY')-date(Date#([Date Ordered],'MM/DD/YYYY')),'DD'))*1
Hi,
all above solutions are correct
else you try below one
SET DateFormat='DD/MM/YYYY';
Thank you,
This returns a dash
Floor(Date_Imported) - Floor([Date Ordered])
Thank you for the reply Sunny, just trying the other option above
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
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
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