Forum Discussion
DateDiff Help
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
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))
2 Replies
- ankitpatira
Community Champion
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))