Why is this formula not working as expected?

Hi Monday community,

I have constructed a formula to help in portfolio reporting, it is a flag field that seems to only work for the first 2 conditions only:

Condition* 1 - There is a positive schedule variance between the baseline end date and forecast end date (the project is running late)

  • Condition 2 - There is a positive coast variance between the baseline cost and forecast cost (the project is running over budget)
  • Condition 3 - The project end date is before todays date (the Active projects forecasted end date is in the past)

If either of the 3 conditions are True, then the formula returns the text value of “Change control needed” otherwise it says “In Control”

The formula I used is below, structurally the formula is sound, but the board seems to be struggling with the forecast end data and the today() function.

Formula - IF(OR({Schedule Variance (Days)}>0,{Cost Variance (£)}>0,{Forecast End Date}<TODAY()),“Change Control Needed”,“In Control”)

Hi Andrew,

Try

IF(OR({Schedule Variance (Days)}>0,{Cost Variance (£)}>0,{Forecast End Date}<FORMAT_DATE(TODAY(),"YYYY-MM-DD")),"Change Control Needed","In Control")

If you want to take your monday formulas to the next level, check out the Advanced Formula Booster at https://mdboosters.com.

1 Like

Oh you absolute legend!

it now works perfectly, thankyou very much Gilles

1 Like