Skip to main content

Custom Functions


Post any examples of custom functions in this forum

My first custom function--the haversine formula

my first post and a chance to say how useful i have found this site and the linked videos 
I have recently been tasked with testing how far customers laddresses are from the business. I found a data base which had the longitude and latitude of every UK postcode,  armed with that information i came up with this custom function
Option ExplicitFunction getDistance(latitude1 As Double,longitude1 As Double,latitude2 As Double,longitude2 As Double) As DoubleDim earth_radius As DoubleDim Pi As DoubleDim deg2rad As Double

Function to generate identical numbering for each document number

Hello Everyone,
I'm trying to create an equation in order generate an identical number for each document number included in a database. At the same time for next document number the numbering have also to be ascending. I try to better explain the situation in the attachment.
Many thanks to those who will help me.

Function to generate restarting sequential number for each document number

Hello Everyone,
It's my first time in this wonderful IDEA forum.
I'm blocked in my project since I cannot found a function (or other way) to create a sequential number that restart every time the document number changes. In attachment, a screenshot to better understanding.
So,I want that for document number "1310000000" in the column "POSTING_NUMBER" there will be "1" for all related lines; for document number "13100000001" in the column "POSTING_NUMBER" there will be "2" for all lines; and so in an ascending way for next document numbers.


I have a string like this:
AR 2016 INTREO Office roll-out - Preliminary Study
and I need to match the characters only having this result
AR INTREO Office roll out Preliminary Study
I have tried with
@RegExpr(CLIENT,"([^0-9-]+$") returns INTREO Office roll-out - Preliminary Study
@RegExpr(CLIENT,"([^0-9-]+") returns AR
I have also to combine the other expression with "OR" operator but did not work.

Apply formula cell by cell

I am trying to standardise a file like this:
I need to all the cell filled for further grouping.
I have tried with this formula:
 Unfortunately it works for the first 2 rows only of Column AAA.
How I can get it work as if IDEA creates a virtual value to look up to give fill the blank cells?

point comma

I'm looking for a formula what changes a character field (which includes digits with point "." for decimal number) to a numeric field with a comma "," (french presentation of numbers). I've tested @curval but I didn't suceed to have two digits after the comma. I don't understand why. Do you know a solution ?
Thanks in advance !

Rounding date forward

I am looking to add a number of days to a given date to calculate an eligible date then round it to the first of the following month to get an entry date.
DOH: 3/5/2001
Eligible Date: 6/3/2001
Entry Date: 7/1/2001
The eligible date does not need to be an output, just the entry date.
I currently have:
this is giving me the correct elibile date, but I cannot figure out how to round it to the first of the next month.


I am attempting to create two "dateParm"s without having to actually type both in an input box.  DateParm will be an inputbox (example, i type in "20181127")
DateParm1 should always be 4 days prior to "dateParm" .  How can i create a custom formula to make DateParm1 = dateParm - 4 days. 
Sub Main