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...
Forum Discussion
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
sad face , So I'll need to write some json for this?
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- 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 ..