locked
Handling a #NULL! error when calculating a date field value based on another field which is not populated RRS feed

  • Question

  • Currently using the following formula to calculate the next service Contract renewal date for a measurement system, based on Current Service Contract date and Service Contract Length:

    =DATE(YEAR([Current Serv Contract Start Date]),MONTH([Current Serv Contract Start Date])+[Current Serv Contract length],DAY([Current Serv Contract Start Date]))

    This formula works great, except of course if there IS no service contract on this measurement system, and therefore no "Current Serv Contract Start Date".

    I've tried numerous versions of the following IF ISBLANK function, trying to set the calculated column value to " " if there is no "Current Serv Contract Start Date"

    =IF(ISBLANK([Current Serv Contract Start Date]),” “,DATE(YEAR([Current Serv Contract Start Date]),MONTH([Current Serv Contract Start Date])+[Current Serv Contract length],DAY([Current Serv Contract Start Date]))

    I also tried it from the perspective of handling the error that would be caused in the calculated column, by using the IF ISERROR fucntion

    =IF(ISERROR(DATE(YEAR([Current Serv Contract Start Date]),MONTH([Current Serv Contract Start Date])+[Current Serv Contract length],DAY([Current Serv Contract Start Date]))),” “,DATE(YEAR([Current Serv Contract Start Date]),MONTH([Current Serv Contract Start Date])+[Current Serv Contract length],DAY([Current Serv Contract Start Date]))  Any help would be MOST appreciated.

    Thursday, January 24, 2013 7:36 PM

Answers

  • Hi,

    I understand that you want to set the next service Contract renewal date based on Current Service Contract date and Service Contract Length. I have tested your formula and it works as expected. Here are the detailed steps:

    1. Create the Current Service Contract date as a date only column and Service Contract Length as number column without decimal.
    2. Then create a calculated column with this formula: =IF(ISBLANK([Current Serv Contract Start Date]),"",DATE(YEAR([Current Serv Contract Start Date]),MONTH([Current Serv Contract Start Date])+[Current Serv Contract length],DAY([Current Serv Contract Start Date])))  Set the calculated column return as Date and Time column. The calculated column will be blank when the Current Serv Contract Start Date is blank(Be careful about the ""in the formula).

    Thanks,

    Entan Ming

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contacttnmff@microsoft.com.


    Entan Ming
    TechNet Community Support

    • Marked as answer by DanNatCorning Friday, January 25, 2013 3:06 PM
    Friday, January 25, 2013 6:45 AM
    Moderator

All replies

  • Hi,

    I understand that you want to set the next service Contract renewal date based on Current Service Contract date and Service Contract Length. I have tested your formula and it works as expected. Here are the detailed steps:

    1. Create the Current Service Contract date as a date only column and Service Contract Length as number column without decimal.
    2. Then create a calculated column with this formula: =IF(ISBLANK([Current Serv Contract Start Date]),"",DATE(YEAR([Current Serv Contract Start Date]),MONTH([Current Serv Contract Start Date])+[Current Serv Contract length],DAY([Current Serv Contract Start Date])))  Set the calculated column return as Date and Time column. The calculated column will be blank when the Current Serv Contract Start Date is blank(Be careful about the ""in the formula).

    Thanks,

    Entan Ming

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contacttnmff@microsoft.com.


    Entan Ming
    TechNet Community Support

    • Marked as answer by DanNatCorning Friday, January 25, 2013 3:06 PM
    Friday, January 25, 2013 6:45 AM
    Moderator
  • Entan, OUTSTANDING response!  It looks like the problem I had was with the double quotes for the blank value.  Apparently I was trying to insert a space, rather than a blank field?  Thanks so much for the assist.  Works exactly as I'd hoped.
    Friday, January 25, 2013 3:08 PM