Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

IF and date Function

  1. Outstanding Action Days =IF(AND(Status="C”),TODAY()-Response Date,"")

  2. Outstanding Status=IF(AND(Outstanding Action Days="")," No Action Required",IF(AND(Outstanding Action Days<=21),"Outstanding Actions",IF(AND(Outstanding Action Days>21),"Overdue Actions","Check")))

Please i want calculated coumns based on above statment.

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create columns

    Outstanding Action Days = IF([Status]="C",DATEDIFF([Reponse Date],TODAY(),DAY))

Outstanding Status =
IF (
    [Outstanding Action Days] = BLANK (),
    "No Action Required",
    IF ( [Outstanding Action Days] <= 21, "Outstanding Actions", "Overdue Actions" )
)

    there is no "check" value matched in the condition.

    If you need "check" to show, please share more details(data example/screenshots) for further analysis.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • JirkaZ's avatar
    JirkaZ
    Solution Specialist
    6 years ago

    Anonymous You can use DATEDIFF in your formula to calculate the difference between two dates. Also I would recommend using SWITCH in the Outstanding Status definition rathern than IF.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create columns

    Outstanding Action Days = IF([Status]="C",DATEDIFF([Reponse Date],TODAY(),DAY))

Outstanding Status =
IF (
    [Outstanding Action Days] = BLANK (),
    "No Action Required",
    IF ( [Outstanding Action Days] <= 21, "Outstanding Actions", "Overdue Actions" )
)

    there is no "check" value matched in the condition.

    If you need "check" to show, please share more details(data example/screenshots) for further analysis.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.