Error Encountered: Value is not valid number: B2 Parameter name: serialDate

Question

I'm trying to create my own calculation in RSA Archer. I'm comparing to Date fields. Sample calculation:

Field name   Field Type
Field 1      Date
Field 2      Date
Field 3      Values List

    IF(DATEDIF([Field 1], [Field 2]) > 0, VALUEOF([Field 3], "Green"),
    IF(DATEDIF([Field 1], [Field 2]) > 1, VALUEOF([Field 3], "Amber"),
    IF(DATEDIF([Field 1], [Field 2]) > 3, VALUEOF([Field 3], "Red"),
    VALUEOF([Field 3],"Not Calculated"))))

But unfortunately, I encountered an Error.

Can anyone help me fix this error message or can someone suggest a better way to manipulate this calculation?

Answer 1

1. The calculation you shared has a missing round bracket ")" at the end. You have 3 "IF" and only two closing brackets. So calculation you shared should fail validation in Archer formula editor.

2. The error you shared indicates an issue with one of the input fields: [Field 1] or [Field 2]. I see two possible issues:
   a). Confirm that [Field 1] and [Field 2] are actually of the Date type. In some cases field time may be Text and calculation can fail.
   b). You need to check in calculation and make sure that both fields are not empty. I would modify the calculation as such:

---

IF( OR(ISEMPTY([Field 1]), ISEMPTY([Field 2])), VALUEOF([Field 3],"Not Calculated"),
IF( DATEDIF([Field 1], [Field 2]) > 0, VALUEOF([Field 3], "Green"),
IF( DATEDIF([Field 1], [Field 2]) > 1, VALUEOF([Field 3], "Amber"),
IF( DATEDIF([Field 1], [Field 2]) > 3, VALUEOF([Field 3], "Red"),
VALUEOF([Field 3],"Not Calculated")
))))