Skip to main content

I am trying to create the following formula but keep getting an error that there was a problem saving this field. Invalid formula. 

The intent was if the project is complete or released to customer, then do the following

If the date diff is negative or NAN, make the result =0 otherwise just do the date diff. However, the NAN is still being displayed. How do I represent NAN value? 

IF(
  OR({🚩Workflow Status} = "Complete",
     {🚩Workflow Status}= "Released to Customer"),

  IF( 
     OR(DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days') < 0, 
         DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days') = ""), 0,
     DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')))

IF(
     OR({🚩Workflow Status} = "Complete",
            {🚩Workflow Status}= "Released to Customer"),
IF(
     OR(
           IS_BEFORE({🚩Actual Eng. Delivery Date}, {🚩Original Planned P50 Date}),
           BLANK() = DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')
       ),
       0,
       DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')
   )
)

IF(
     OR({🚩Workflow Status} = "Complete",
            {🚩Workflow Status}= "Released to Customer"),
IF(
     OR(
           IS_BEFORE({🚩Actual Eng. Delivery Date}, {🚩Original Planned P50 Date}),
           BLANK() = DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')
       ),
       0,
       DATETIME_DIFF({🚩Actual Eng. Delivery Date},{🚩Original Planned P50 Date},'days')
   )
)


Thank you for getting to me understand how to remove the NAN in the results. 

Any good videos I should listen to get better educated

 

Reply


Terms & Conditions