Structure - how to create a formula to detect if due date is on date, late and status done?

[proliveira]

Hello.

I'd like to create a formula on structure where:

If Due Date is Late and StatusCategory is Not Done, appers RISK

if due date is on time and statuscategory is not done, it is Normal

if due date is on time and statuscategory is to do, appers not started

if regardless of due date, if statuscategory is DONE, apps Awesome.

I got this formula on the structure page, but something is wrong because one issue is at Risk, but the due date is not late. 

with WORK_TIME_TO_CALENDAR_TIME(time) = (

  with min = 60 * 1000:

  with hour = 60 * min:

  with day = 8 * hour:

  with week = 5 * day:

  with weeks = FLOOR(time / week):

  with r1 = time - weeks * week:

  with days = FLOOR(r1 / day):

  with r2 = r1 - days * day:

  with hours = FLOOR(r2 / hour):

  with r3 = r2 - hours * hour:

  with mins = r3 / min:

  (((weeks * 7 + days) * 24 + hours) * 60 + mins) * min

):

with FORMAT_CAPTION(color, caption) = (

  """:panel[$Caption]{backgroundColor=$color color=white}"""

):

with diff = FLOOR((due_date - (TODAY() + WORK_TIME_TO_CALENDAR_TIME(sum {remaining}))) / 86400000):

IF statuscategory = "done":

  FORMAT_CAPTION("#59B161", ":sunny: **Awesome**")

ELSE IF diff < 0:

  FORMAT_CAPTION("#EF4B59", ":cloud_with_lightning_and_rain: &nbsp;&nbsp;**At Risk&nbsp;&nbsp;&nbsp;**")

ELSE IF diff < 15:

  FORMAT_CAPTION("#FFAF00", ":sun_behind_large_cloud: &nbsp;&nbsp;**Normal**&nbsp;&nbsp;")

ELSE:

  FORMAT_CAPTION("#59B161", ":sunny: **Awesome**")

[Stepan Kholodov _Tempo_ · Accepted solution]

Hello @proliveira 

An issue might meet more than one condition at the same time, and the Formula can use the first match for returning results. The IF diff < 15 and IF diff < 0 conditions overlap: for example,  -10 value will match both of them. Try to distinguish them in more details like this: IF diff < 15 and diff >= 0 and IF diff < 0

I hope this helps. If you need further assistance with the formula or if you have more questions about Structure, please reach out to us directly at our support portal.

Best regards,
Stepan
Tempo (the Structure app vendor)