How do I write this IF Statement for Days Late?

[#1]

Hello there,

I am trying to write an IF statement for number of days a Job is late.

This is my sample data below:

This is the rule I am trying to write:

The Days Late should be calculated in numbers.

If Resolved_Date is greater than Requested_Date calculate the difference.

If Requested Date is blank, calculate Days_Late using Date Due

If both are blank insert 'Unresolved'.

Can anyone share an example of this? Sorry I am new to writing if statement with dates

[#2]

Try this

If(isnull(Requested_Date)=0,Resolved_Date-Requested_Date,If(isnull(Due_Date)=0,Resolved_Date-Due_Date,'UnResolved'))

This  expression will provide a negative number if the Resolved date is before the requested date.

[#3]

Hi Aushik,

Thank you for your reply

Does the =0 mean when it finds a null it place a 0 on true for null?

[maxgro]

you can use

if(len(trim(field)) > 0, ..., ....)

to check if field is null, empty

date1 - date2

to calc the difference between date

if(date1 > date2, ..., ....)

to compare dates

[#5]

Hi,

No, here isnull(dimension) returns true  or false. So if the column is null it returns -1 and if value is not null it returns 0.

you may also use simply

if(isnull(field), dim1, dim2)...this will be the reverse of checking 'isnull(field)=0'

[#6]

Thanks Aushik,

I forgot to add one more criteria, if both Date_Requested and Due_Date are blank then it should calculate the difference between Resolve_Date and Created_Date.

Any idea how I can add this in?

[#7]

Hi,

You may do that by including another if condition nested inside the above example.

However the most optimal way would be to define a new field in the reload script as below

If(isnull(Requested_Date),If(isnull(Due_Date),Created_Date,Due_Date),Requested_Date) as Target_Date

Then in the UI you can simply use Resolved_Date-Target_Date

[#8]

Hi Aushik,

Does the Target Date search for values in the sequential order you have in the If Statement?

[#9]

Yes...first it checks the Requested_Date. If Requested_Date is null it checks the 'Due_Date' If both of these dates are null, it uses  the created date.