# Formula help needed

• 0
• Question
• Updated 4 years ago

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.

• 0 Points

Posted 4 years ago

• 0
• 72,478 Points
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>")

• 0 Points
Should this line:
Today() - [Next Due Date]<Days(365*3), "Good",
Today() - [Next Due Date]<Days(365), "Good",
?
• 72,478 Points
• 0 Points
Ok, its working fine for annual classes, but is just returning "Good" for PSM, when the date is well over 3 years.
• 72,478 Points
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")
• 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.
• 72,478 Points
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

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")
• 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?
• 72,478 Points
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>"))
• 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.
• 72,478 Points
try just taking out the very last ) so it ends in a single ) and not a double ))
• 0 Points
It says the same thing:
"Formula syntax error
There are extra characters beyond the end of the formula."
• 72,478 Points
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>")
• 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
• 72,478 Points
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.
• 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.
• 72,478 Points
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")
• 0 Points
I tried it, but there was a syntax error:
Formula syntax error

There are extra characters beyond the end of the formula.
• 72,478 Points
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")
• 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.
• 72,478 Points
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.
• 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
• 72,478 Points
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.