# Calculated formula not working

Copper Contributor

# 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?

7 Replies

# Re: Calculated formula not working

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", "")))

# Re: Calculated formula not working

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", "")))

# Re: Calculated formula not working

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

# Re: Calculated formula not working

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

# Re: Calculated formula not working

Tried that and it actually made the list break, But I will need this to auto update , so ..Thanks for the help

# Re: Calculated formula not working

@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

# Re: Calculated formula not working

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 ..