1

I'm trying to calculate a date column based on what data exists in two other date columns. Is that possible?

Basically, in 95% of cases, the Planned Date is always 5 days from the Submission Date. I need the option to override that for the remaining 5% of cases.

if(subDate = null), planDate = ""
elseIf (subDate != null & overDate = null), planDate = subDate+5
elseIf (subDate != null & overDate != null), planDate = overDate

Here is what I have so far... planDate =

IF(AND(ISBLANK([overDate]),[subDate]>1/1/1),DATE(YEAR([subDate])+0,MONTH([subDate])+0,DAY([subDate])+35),[overDate])

The problem is this misses a use case so it populates planDate with "12/30/1899" when both are null. I guess 12/30/1899 is the default.

Am I missing something obvious here? Any help greatly appreciated

2 Answers 2

0

Based on the conditions you mentioned in your question, use formula like below:

=IF(ISBLANK([subDate]),"",IF(ISBLANK([overDate]),[subDate]+5,[overDate]))

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.

Official Documentations:

  1. Calculated Field Formulas
0

Try adding another nested IF within the ELSE, and also and extra ISBLANK check for subdate before the other conditions

IF(
    ISBLANK([subDate])
    ,""
    ,IF(AND(ISBLANK([overDate]), [subDate]>1/1/1)
        , DATE(YEAR([subDate])+0,MONTH([subDate])+0,DAY([subDate])+5)
        , IF(AND([overDate]>1/1/1, [subDate]>1/1/1)
            , [overDate]
            , ""
        )       
    )
 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.