Forum Discussion

Thomasw1964's avatar
Thomasw1964
Copper Contributor
Sep 26, 2023

Calculated formula not working

Trying to create a calculated column,"Phase",  based on number of days client has been in our program. We give clients a "Start Date" the first 30 days they are Phase1, 30 to 365days Phase2, over 365...
SharePoint Online
SvenSieverding's avatar
SvenSieverding
Bronze Contributor
Sep 26, 2023

Hi Thomasw1964 ,

This should work:

 

IF([Start Date]>TODAY()-30, "Phase1",IF([Start Date]>TODAY()-365, "Phase2", "Phase3"))

 

 
But remember: Calculated columns only update themself if the item is updated. They don't recalculate automatically if just a day has passed. If you want that, then you need to apply a custom column formatting.

Best Regards,
Sven

  • Thomasw1964's avatar
    Thomasw1964
    Copper Contributor
    Sep 26, 2023
    Tried that and it actually made the list break, But I will need this to auto update , so ..Thanks for the help
  • Thomasw1964's avatar
    Thomasw1964
    Copper Contributor
    Sep 26, 2023
    sad face , So I'll need to write some json for this?
    • SvenSieverding's avatar
      SvenSieverding
      Bronze Contributor
      Sep 27, 2023

       

      Thomasw1964 wrote:
      sad face , So I'll need to write some json for this?

      Hi Thomasw1964 ,

      You can try this Json formatting:

      {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))>-30,'Phase 1',if(floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))>-365,'Phase 3','Phase 2'))"
}

       
      Best Regards,
      Sven

      • Thomasw1964's avatar
        Thomasw1964
        Copper Contributor
        Sep 27, 2023
        I created a calculated column in the formula I put =[Start Date]

        I added the json you gave me
        {
        "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
        "elmType": "div",
        "txtContent": "=if(floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))>-30,'Phase 1',if(floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))>-365,'Phase 3','Phase 2'))"
        }

        and now everyone is in Phase2

        For my testing data I have within 30 days, between 30 and 365, and over 365 ..

Resources