Discussions

Expand all | Collapse all

What is wrong with my formula?

  • 1.  What is wrong with my formula?

    Posted 04-20-2018 16:40
    This is my formula, I copied it from another app and I think I tweaked it correctly but I must have missed something.  The work orders are entered and I want the buttons to say how many days past due if the work order is open or gray and completed if status =closed.  Status = Closed (gray button) works but all the others are RED and showing the number of days overdue from the day they are entered.  My intention was for the button to be green if the work order was less than 5 days old yellow if 6 or 7 days old and anything older than 7 days to have a red button.

    Help pls.   formula below



    var number days = ToDays(Today()-[Date]);
    var date Estimate = [Date];

    var text red = "<a style=\" text-decoration: none; background: #ea4335; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToDays(Today()-$Estimate)&" Days Overdue</a>";

    var text green = "<a style=\" text-decoration: none; background: #34a853; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&$days&" Days Due</a>";

    var text yellow = "<a style=\" text-decoration: none; background: #fbbc05; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToDays(Today()-$Estimate)&" Days Due</a>";

    var text gray = "<a style=\" text-decoration: none; background: #c2c2c2; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Completed</a>";

    If([Status]="Closed",$gray,
    (If(ToDays([Date]-Today())>5,$green,
    (If(ToDays([Date]-Today())>0,$yellow,
    (If(ToDays([Date]-Today())<=0,$red)))))))


  • 2.  RE: What is wrong with my formula?

    Posted 04-20-2018 17:02

    If [Date] is prior to Today() then the result will be negative. Why red is showing most of the time.



  • 3.  RE: What is wrong with my formula?

    Posted 04-20-2018 18:28
    Thank you!  I changed it and it worked.


  • 4.  RE: What is wrong with my formula?

    Posted 04-20-2018 18:29
    New Formula in case anyone needs it:

    var number days = ToDays(Today()-[Date]);
    var date Estimate = [Date];

    var text red = "<a style=\" text-decoration: none; background: #ea4335; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToDays(Today()-$Estimate)&" Days Overdue</a>";

    var text green = "<a style=\" text-decoration: none; background: #34a853; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&$days&" Days Due</a>";

    var text yellow = "<a style=\" text-decoration: none; background: #fbbc05; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">"&ToDays(Today()-$Estimate)&" Days Due</a>";

    var text gray = "<a style=\" text-decoration: none; background: #c2c2c2; border-radius: 5px; color: #fff; display: inline-block; width: 160px; text-align: center; padding: 8px 20px; font: normal 700 14px/1 \"Calibri\", sans-serif; text-shadow: none; \">Completed</a>";

    If([Status]="Closed",$gray,
    (If(ToDays(Today()-[Date])>5,$red,
    (If(ToDays(Today()-[Date])>0,$yellow,
    (If(ToDays(Today()-[Date])<=0,$green)))))))