Calculated column for weeks with condition

[harshagraj]

Hi all please help me to create a calculated column to achieve this with color.

If Finish Date < 5-10 weeks than Milestone then "Green"

If Finish Date < 2-5 weeks than Milestone then "Yellow"

If Finish Date >= than Milestone then "Red"

"

PS: Both are date columns with hierarchy.

[az38 · Accepted solution]

Hi @harshagraj 

your conditions is completely unclear and not consistent, but i can suggest smth like this

Column = 
var _datediff = DATEDIFF([Finish Date],[Milestone], WEEK)
RETURN
SWITCH(TRUE(),
_datediff > 5 && _datediff < 10, "Green", 
_datediff > 2 && _datediff < 5, "Yellow", 
_datediff <= 0, "Red",
BLANK()
)

[harshagraj]

Hi @az38  thank you so much. This is working perfectly.