Hi,
I am attempting to create a Status column based on the date or the completion of a date based on the results from multiple columns. I have attempted to use a Formula column (which I tested a formula I devised in Excel than translated in Monday format) and using AI utilizing a Text Column. Please note the default Status column only allows status to change based on one column (so discarded this option).
The attached screen print contains a column called “Draw Status” - depending on which columns are completed and if and when the date arrives will depend on the status. The columns it refers to are “Start Date”, “Stop Date” and “Scoring Complete”
When I successfully replicated this in Excel, I took this formula and translated it into Monday syntax:
=IF({Scoring Complete}<>“”,IF({Scoring Complete}<=today(),“Scored”,“Error”),IF(AND({Start Date}<>””,{Stop Date} <>“”),IF({Stop Date}<=TODAY(),“Draw Closed”,IF({Start Date}<=TODAY(),“Draw Open”,“Pre-Draw”)),“Pre-Draw”))
When I requested AI to do the following use the following text:
This formula checks if ‘Scoring Complete’ is less than or equal to today’s date. If true, it returns ‘Scored’, otherwise If ‘Scoring Complete’ is blank, it checks if both ‘Start Date’ and ‘Stop Date’ are not blank. If both are not blank, it checks if ‘Stop Date’ is less than or equal to today’s date. If true, it returns ‘Draw Closed’. If ‘Stop Date’ is greater than today’s date, it checks if ‘Start Date’ is less than or equal to today’s date. If true, it returns ‘Draw Open’, otherwise ‘Pre-Draw’. If either ‘Start Date’ or ‘Stop Date’ is blank, it returns ‘Pre-Draw’.
I understand this may be too complicated for Monday.com but I can achieve this in an Excel spreadsheet and would like Monday to be able to achieve a similar result.
Some Feedback would be appreciated. Thanks Troy