locked
Power Pivot question: Calculated field to determine difference between two dates RRS feed

  • Question

  • I want to see the number of days that have passed between two dates. I created a calculated column but am getting no where with this. My formula:  =IF(ISBLANK(Activity[COMPLETED]),"COMPLETED",(TODAY()-Activity[CREATED]))

    The data type for Activity[COMPLETED] = Date

    The data type for Activity[CREATED] = Date

    What can I do to fix this?

    The error I receive:  The second and third arguments of function IF have different data types. This is not supported.

    Thursday, March 15, 2012 7:07 PM

Answers

  • Hi,

    The IF function attempts to return a single data type in a column.  Therefore, if the values returned by value_if_true and value_if_false are of different data types, the IF function will implicitly convert data types to accommodate both values in the column.

     In your if statement "completed" is a string and (today()-activity[created])) is a date. You can't accomodate two data types in a single column. Power pivot is not able to convert them to a single data type (either date to string or string to date). So You just need to replace the string "cpmpleted" with a date or convert date to a string. Check this link for more details.

    http://technet.microsoft.com/en-us/library/ee634824.aspx

    ======================

    Please mark as answer if helpful

    • Marked as answer by Terry Waldo Friday, March 16, 2012 1:31 PM
    Friday, March 16, 2012 8:14 AM

All replies

  • Hi,

    The IF function attempts to return a single data type in a column.  Therefore, if the values returned by value_if_true and value_if_false are of different data types, the IF function will implicitly convert data types to accommodate both values in the column.

     In your if statement "completed" is a string and (today()-activity[created])) is a date. You can't accomodate two data types in a single column. Power pivot is not able to convert them to a single data type (either date to string or string to date). So You just need to replace the string "cpmpleted" with a date or convert date to a string. Check this link for more details.

    http://technet.microsoft.com/en-us/library/ee634824.aspx

    ======================

    Please mark as answer if helpful

    • Marked as answer by Terry Waldo Friday, March 16, 2012 1:31 PM
    Friday, March 16, 2012 8:14 AM
  • Works like a charm!  Thanks for the quick response.

    Terry Waldo

    Friday, March 16, 2012 1:32 PM