Nested if statement issue

[bobbydave]

Can anyone see my issue? I don't know if it is down to brackets.

  if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') = 0.0, '0',
       if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >= 0.1 and Interval(Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <= 3.9, 'Best Effort',
            if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >=4.0 and  Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <=6.9, 'Less than 90 days',
                 if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >= 7.0 and  Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <=8.9, 'Less than 30 days',
                        if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >=9.0 and  Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <=10.0, 'Less than 14 days',

                               'Over 90 Days')))))
                                   as [Upper TIme Limit (days)],

I can get two results. 0 and   'Over 90 Days' but cant get the results from the nested if statements within the statement.

There should be multiple Less Than 30 days, Less than 14 days etc.

Please help

[jobsonkjoseph]

Hi,

Is this the complete script or part of it, i'm trying to replicate it but not happening.

[bobbydave]

This is part of my script.

Nothing complicated with Q and P. These are dates.

[andrey_krylov]

Hi David. Looks like syntax is fine and even checked. Make sure Q and P have a date format.

[andrey_krylov · Accepted solution]

The only thing I did not get is

= 0,       '0',

< 4,      'Best Effort',

< 7,      'Less than 90 days'

< 9,      'Less than 30 days'

< 10,    'Less than 14 days'

and I would simplify

if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 10, 'Over 90 Days',

if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 9 ,  'Less than 14 days',

if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 7 ,  'Less than 30 days',

if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 4 ,  'Less than 90 days',

if( Interval  (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 0 ,  'Best Effort'

, '0'))))) as [Upper TIme Limit (days)],

[PrashantSangle]

Interval() is formatting function.

Try with num#() before interval()

like if(num#(interval(date1-date2,'D'))=0,'0',

if(num#(interval(date1-date2,'D'))>0.1,'0',

)

One small question, How date difference will come in decimal format, If you are presenting it in only days?

Regards,