Forum Discussion
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 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- Tried that and it actually made the list break, But I will need this to auto update , so ..Thanks for the help
- 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
- This puts everyone in Phase3
=IF([Start Date]+30 <=30, "Phase1", IF(AND([Start Date]+31 >30, [Start Date]+364 <=364), "Phase2", IF([Start Date]+365 >365, "Phase3", "")))- This puts Everyone in Phase1
=IF([Start Date]<=TODAY()+30, "Phase1", IF(AND([Start Date]>TODAY()+30, [Start Date]<=TODAY()+365), "Phase2", IF([Start Date]>TODAY()+365, "Phase3", "")))
