DateDiff Help

[Anonymous]

trying to work on some lead times through by obtaining the difference between 2 dates

Committed/Done Diff = IF([DATE1] > [DATE2],"",DATEDIFF([DATE1],[DATE2],DAY))

what i am trying to achieve is have if the date 1 is greater than date 2, then leave field as blank

if date 1 is not greater than date 2, give me the date difference between the 2 dates

the error i get is

"expressions that yield variant data-type cannot be used to define calculated columns"

not sure what i am doing wrong

any help would be appreciated

[ankitpatira · Accepted solution]

@Anonymous Reason you're getting this error is because from your IF statement if condition is false then you're returning blank which is of data type text and if true you're returning date. You can't have different data type returns from single IF statement. Therefore if you do conditional column as below it will work.

Committed/Done Diff = IF([DATE1] > [DATE2], today(), DATEDIFF([DATE1],[DATE2],DAY))

[Anonymous]

thanks for the reply

that helped but i changed it so i didnt get todays value

Committed/Done Diff = IF([DATE1] > [DATE2], Blank(), DATEDIFF([DATE1],[DATE2],DAY))