Help with a If calculation

[#1]

Hi

I have two datefields and want the diffrens in between them. I do this by Stopdate - StartDate.

But in some fields the StopDate are wrong. So I only want the calculation for the fields were Stopdate are larger then Startdate. I do the caluclation in the script as  Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD') - Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD') as DayDiff

I guess I should use a IF statemeant, can anyone help me?

Thanks!

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

If I understand correctly, you only need to add a conditional so the expression is only calculated when StopDate is greater than StartDate, so the following should do it:

If(StopDate > StartDate,
     Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD') - Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD') 
     , 0) as DayDiff // 0 is the default value when StopDate is not greater than StartDate: change it according to your needs

Hope that helps.

Miguel

[CELAMBARASAN]

Hi,

     Try with this.

     If(Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD')>Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD'),Interval(Date(Date#(StopDate, 'YYYYMMDD'), 'YYYY-MM-DD')-Date(Date#(StartDate, 'YYYYMMDD'), 'YYYY-MM-DD'),'D')) as DayDiff

Hope it helps

Celambarasan

[#4]

Thanks to both of you!