Formula help needed

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have to track various class recertifications.

I have that tracked correctly in a field called [Next Due Date] with the following formula:

If(

[Course - Class Name]="BBP", AdjustYear([Date Last Completed], 1), 

[Course - Class Name]="CHS", AdjustYear([Date Last Completed], 100),  

[Course - Class Name]="CS", AdjustYear([Date Last Completed], 100),  

[Course - Class Name]="EAP", AdjustYear([Date Last Completed], 1), 

[Course - Class Name]="Elec", AdjustYear([Date Last Completed], 100),  

[Course - Class Name]="FrSf", AdjustYear([Date Last Completed], 1), 

[Course - Class Name]="FrkE", AdjustYear([Date Last Completed], 1), 

[Course - Class Name]="FrkT", AdjustYear([Date Last Completed], 100),  

[Course - Class Name]="HazCom", AdjustYear([Date Last Completed], 100),  

[Course - Class Name]="LOTO", AdjustYear([Date Last Completed], 100),  

[Course - Class Name]="Noise", AdjustYear([Date Last Completed], 1), 

[Course - Class Name]="PPE", AdjustYear([Date Last Completed], 100), 

[Course - Class Name]="PSM", AdjustYear([Date Last Completed], 3), 

[Course - Class Name]="RsFT", AdjustYear([Date Last Completed], 1), 

[Course - Class Name]="RsTr", AdjustYear([Date Last Completed], 100)

)

So I have another field to tell me how far past due the given class is, it is called [Past Due] and this is its formula:

If(Today() - [Next Due Date]<Days(365), "Good", "<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>")

This works great except for the one course called "PSM". All the other courses need to be re-upped annually (or I set them to 100 years as they are only needed once.) "PSM" has a 3 year re-up though, I need to reflect that in the [Past Due] field and I don't know how to make the formula reflect that.


Thanks in advance for any help.

Photo of Tony

Tony

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
try this

If(

[Course - Class Name]="PSM" and Today() - [Next Due Date]<Days(365*3), "Good",


Today() - [Next Due Date]<Days(365), "Good",

"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>")

Photo of Tony

Tony

  • 0 Points
Should this line:
Today() - [Next Due Date]<Days(365*3), "Good",
Actually read:
Today() - [Next Due Date]<Days(365), "Good",  
?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
yes.  I fixed my answer.
Photo of Tony

Tony

  • 0 Points
Ok, its working fine for annual classes, but is just returning "Good" for PSM, when the date is well over 3 years.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
I think the whole formula has wrong logic.

Maybe this is what you want.

If(
Today() > [Next Due Date],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"),
Good")
Photo of Tony

Tony

  • 0 Points
I need the formula to calculate for 3 years for "PSM" and 1 year for all annual courses (the 100 year courses are fine as is). So....if its less than 365 days for all courses with 1 year then return "Good", or if its over 365 days for all 1 year courses return the number of days its past due. If its less than 1095 days for "PSM" return "Good", or if its over 1095 days return the number of days its past due.

I think the logic was right on your first attempt, it just didn't work.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
But your formula for Next Due date is already calculating the three years.  I assume that you want to color if the record is past its due date or if the record has no date last completed


How about this version

If(
Today() > [Next Due Date],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"),

IsNull([Date Last Completed],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"),

"Good")
Photo of Tony

Tony

  • 0 Points
Sorry, I guess I'm not explaining it correctly. I really think your first attempt was a correct understanding of what I wanted, it just didn't work for some reason.

What I'm looking for is one formula that does this:

If the course is PSM:
Today's Date - Next Due Date = <1095 then say the word "Good"
OR
Today's Date - Next Due Date = >1095 then show how many days past Next Due Date in red

If the course is not PSM:
Today's Date - Next Due Date =<365 then say the word "Good"
OR
Today's Date - Next Due Date =>365 then show how many days past Next Due Date in red

Did that make more sense?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
OK try this
IF(
[Course - Class Name]="PSM" and Today() - [Next Due Date]<Days(1095), "Good",
[Course - Class Name]="PSM" and Today() - [Next Due Date]>=Days(1095),
   "<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"),

Today() - [Next Due Date]<Days(365), "Good",
Today() - [Next Due Date]>=Days(365),
   "<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"))
Photo of Tony

Tony

  • 0 Points
"Formula syntax error
There are extra characters beyond the end of the formula."

But that totally looks like it should do what I want it too. Sorry, I don't mean to be a pain.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
try just taking out the very last ) so it ends in a single ) and not a double ))
Photo of Tony

Tony

  • 0 Points
It says the same thing:
"Formula syntax error
There are extra characters beyond the end of the formula."
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
OK,
I think I see the problem

IF(
[Course - Class Name]="PSM" and Today() - [Next Due Date]<Days(1095), "Good",
[Course - Class Name]="PSM" and Today() - [Next Due Date]>=Days(1095),
   "<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>",

Today() - [Next Due Date]<Days(365), "Good",
Today() - [Next Due Date]>=Days(365),
   "<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>")
Photo of Tony

Tony

  • 0 Points
I dunno. It doesn't seem to be doing the math properly.

For example:
If I enter the date for a PSM course as 06-08-2010, it correctly returns 06-08-13, but the [Past Due] formula, says "Good" instead of showing the days past due it is.
BUT
if I put in 06-08-2009, it correctly returns 06-08-12, but the [Past Due] formula, says 1096.

It should be 1155 days not 1096 for 6-8-09 to 6-8-12 and it should be 790 days not "Good" for 6-8-10 to 6-8-13
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
You need to do the math and follow the logic of your formula. The logic if the formula is wrong, because what you asked for is also wrong.

I think you are getting yourself confused by [Last Date Completed] and [Next Due Date] and as a result are double applying the 1 or 3 year delay.

You need to rotate in works the result you want.

In a post way way above  above I posted a formula that was very short with the caption "But your formula for Next Due Date is already is already calculating three years"

I suggest that you try some math by hand using that formula. Or just make a new field with that formula.
Photo of Tony

Tony

  • 0 Points
I honestly don't think that's the problem.

Here's all I want:

           (I input this)      (Top formula does this)        (Formula I'm trying to get right should do this)
           Last Date         Next Due Date                     Past Due
BBP   :  8-10-12          8-10-13   (1 year diff)          728              (today is 728 days past the Next Due Date)
EAP   :  10-11-15        10-11-16 (1 year diff)          Good           (today is less than 1 year from Next Due Date)
PSM  :  10-11-15        10-10-18 (3 year diff)          Good           (today is less than 3 years from Next Due Date)
PSM   :  6-10-12          6-10-15  (3 year diff)          59                (today is 59 days past the Next Due Date)


I am not confusing my fields. Nor am I double factoring anything. The last formula you gave me is close, there is just something off as the Past Due days (for the PSM 3 year class only) are not being factored correctly.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Can you try making a new field with the formula I posted earlier and give me an example of an incorrect result?






If(
Today() > [Next Due Date],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"),

IsNull([Date Last Completed],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>"),

Good")
Photo of Tony

Tony

  • 0 Points
I tried it, but there was a syntax error:
Formula syntax error

There are extra characters beyond the end of the formula.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
If(
Today() > [Next Due Date],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>",

IsNull([Date Last Completed],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due Date])) & "</div>",

Good")
Photo of Tony

Tony

  • 0 Points
New syntax error:
The argument list of the function Good must begin with a left parenthesis.

If I do what it says, it comes back with:
Please check the syntax of your entire formula. Possible culprits are a mismatched set of parentheses, missing quotes, or an extra bracket.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
OK,
I put the fields into an app to be sure it passed a syntax test.

If(
Today() > [Next Due date],
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due date])) & "</div>",

IsNull([Date Last Completed]),
"<div style='color: #bb0000; font-weight: bold;'>" & ToText(ToDays(Today() - [Next Due date])) & "</div>",
"Good")


The formula I would recommend though is below as it deal with the situation of the one or both the date fields being null.

If(
Today() > [Next Due Date],  "<b><font color=red>" & ToText(ToDays(Today() - [Next Due Date])),
IsNull([Date Last Completed]) ,"<b><font color=red>No Past Certification Date",
IsNull([Next Due Date]), "<b><font color=red>No Next Due Date",
"Good")


Why don't you contact me via the information in my profile to discuss sources of information on how to write and debug formulas.  I know that this was an awfully long thread just to get a formula debugged, but normally on this forum I don't actually recreate the formuals in order to exactly check the syntax,  and on the weekends I tend to be on an iPhone or iPad.  It will be less frustrating for you to have a better understanding of the syntax of formuals to be able to fix missing commas or brackets quickly on your own.
Photo of Tony

Tony

  • 0 Points
I can contact you tomorrow after 5pm est.

But the above kinda works. The total days past due shown for annual classes calculates correctly. But when it calculates the past due date for the 3 year class it just shows the wrong amount of days. Its off by 60 days exactly.

This is for the 3 year class only. These are the results I got.

Date Last Completed             Next Due Date           Past Due
03-05-2009                         03-05-2012           1193  (Should be 1253 days according to       
                                                                                             http://keisan.casio.com/exec/system/1247118517
06-08-2009                         06-08-2012           1098  (Should be 1158 days according to       
                                                                                             http://keisan.casio.com/exec/system/1247118517
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
QuickBase native Dates are on the format MM-DD-YYYY.  The dates you put in that calculator were entered  as interpreting the dates as DD-MM-YYYY.   I personally prefer to edit the field properties on the date fields to show the date month in words like MAR-05-2012.  If you want to talk on Monday please email me to the email shown in my Profile.