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 365days Phase3
=IF([Start Date]<=TODAY()+30, "Phase1", IF(AND([Start Date]>TODAY()+30, [Start Date]<=TODAY()+365), "Phase2", IF([Start Date]>TODAY()+365, "Phase3", "")))
When I attempt to save this it fails, can anyone help me out with what I am doing wrong?
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 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