Custom Functions

In the IDEA googlegroups forum someone posted that they were looking for a formula in IDEA that performed the following Excel function:

=IF((DAY(A45)=1),EOMONTH(DATE( YEAR(A45)+60,MONTH(A45),DAY( A45)),-1),EOMONTH(DATE(YEAR( A45)+60,MONTH(A45),DAY(A45)), 0

This function will return a 1 if the IBAN adheres to the validation else it will return 0 if it is false.

The International Bank Account Number (IBAN) is an internationally agreed means of identifying bank accounts across national borders with a reduced risk of propagating transcription errors.

An IBAN is validated by converting it into an integer and performing a basic mod-97 operation (as described in ISO 7064) on it. If the IBAN is valid, the remainder equals 1.

This function will return a 1 if the identification number adhere to the ISBN Mod 11 Check else it will return 0 if it is false. 

You can use this function to validate ISBN-10 numbers.  I validated this routine with several ISBN-10 numbers taken from Amazon.com and it seem to work properly.

As with the LUND formula if anyone can try this out and let me know what it works for that would be great.  I can confirm that it worked for my Mastercard, my Canadian debit card and my American Express card.

This function will return a 1 if the identification number adhere to the IBMCheck else it will return 0 if it is false.

I created this custom function based on a comment for the IDEA group on Linkedin, it based on the explanation posted below.  I have only been able to test it on Canadian Social Insurance Numbers so if anyone else has number that use this algorithm I would appreciate if you could test it and post where it work.

I needed to figure out the number of days from the beginning of the year in a vitual field.  One way to do this is using the @age function, but it can be a pain if your dates happen on different years, so I created a custom function that will calculate the number of days since the beginning of the year.  To use it #day_num(date) and it returns a numeric value with the number of days.

This function is the opposite of the TrimStart custom function.  It will remove an ending character from a field.  The first parameter is the string or character field to remove the charcters from, the second parameter is the character to remove.  You would call the custom function in the equation editor using #TrimEnd(Character Field, Character to remove). 

Download

This is based on a discussion in the ideascripting forum.  This custom function will trim a certain character from a given field.  I have made it a bit more generic so you can decide what character to trim from the beginning.  I will attach the Custom Function tonight for use but this is the code:

Option Explicit
Function TrimStart(p1 As String,p2 As String) As String

Date fields can come in many different varieties.  Visual Basic has a function called CDate that is able to figure out the majority of the formats and translate them into a common format.  The following is the code for a custom function to do the change.

Option Explicit
Function CtoDate(p1 As String) As Date