If statement in Interval count

[#1]

Hi,

How can I add if statement when calculating interval between two dates?

I want to calculate number of day from OrderDate to DeliveryDate.

I am using the following simple expression as both fields are timestamps.

= Date(DeliveryDate) - Date(OrderDate)

but if Order was placed after 12PM then I want to - 1 from the Total.

Thanks

[Kushal_Chawda]

may be this will be helpful

Working hours only

[HirisH_V7]

Hi,

Check this,

From a sample data,

Data:
LOAD *,
Interval([Delivery Date]-[Order Date],'DD') as Days,
Interval(IF(TimeStamp#(TimeStamp([Order Date],'h:mm:ss[.fff] TT'),'h:mm:ss[.fff] TT')>='12:00:00 PM',
  (Interval([Delivery Date]-[Order Date],'DD'))-1,
  Interval([Delivery Date]-[Order Date],'DD')) ,'DD')as DaysWithCondition
INLINE [
Delivery Date,Order Date
02/04/2015 11:23:07 AM,30/03/2015 11:23:09 AM
02/04/2015 11:23:07 AM,30/03/2015 12:00:00 PM
02/04/2015 11:23:07 AM,30/03/2015 01:00:00 PM
];

By using the if Condition For time >='12:00:00 PM'  and Interval Function calculating the number of days Difference,

Here is the Difference at output,

IF we use timestamp means we can get the accurate time lapse between two dates, For now its only Days Difference,

Hope this helps,

PFA,

Hirish

[#4]

Thank Harish,

Is it possible to exclude weekend and public holidays?

I have a separate table listing all the public holiday dates. would that help ?

[HirisH_V7]

Please provide me a sample data

[sunny_talwar]

If you want to exclude weekends and public holidays from the difference of order date and delivery date, you can use NetWorkDays() function.

Find Net Working Days

[#7]

Thank Sunny.. I did post a similar post couple of days ago but no one replied. Do you think the link you suggested will help me solve the question I posted here.. how to calculate days between two timestamps Thanks