List of User-defined Functions

Display_Name(First,Last[,Middle [,Title[,Suffix]]])

Character

Character

Returns the full displayable name given the individual name pieces: <1>first name</1>, <2>last name</2>, <3>middle name or middle initial</3>, <4>title (e.g., "Mr")</4>, <5>suffix (e.g., "Jr")</5>

Value_XL(text)

Character

Numeric

Converts a <1>text</1> string that represents a number to a number. This is an Excel function name for the intrinsic function Val.

Char_XL(number)

Numeric

Character

Returns the character specified by the code <1>number</1> from the character set for your computer. This is an Excel function name for the intrinsic function CHR.

Code_XL(text)

Character

Numeric

Returns a numeric code for the first character in a <1>text string</1>, in the character set used by your computer. This is an Excel function name for the intrinsic function ASC.

Search_XL(find_text,within_text,start_num)

Character, Character, Numeric

Numeric

Returns the number of the character at which a specific <1>character or text string</1> is first found, reading left to right (not case sensitive). This is an Excel function using the intrinsic functions Instr and Substr.

Mid_XL(text,start_num,num_chars)

Character, Numeric, Numeric

Character

Returns the characters from the middle of a <1>text string</1>, given a <2>starting position</2> and <3>length</3>. This is an Excel function name for the intrinsic function Substr.

Replace_XL(old_text,start_num, num_chars,new_text)

Character, Numeric, Numeric, Character

Character

Replaces part of an <1>existing text string</1> with a <4>new text string</4>, using a <3>specified number of characters</3>, starting at a <2>specified position</2>. This is an Excel function name for the intrinsic function Stuff.  Note there is an existing intrinsic function called Replace. See also Substitute.

Substitute_XL(text,old_text,new_text)

Character, Character, Character

Character

Replaces <2>existing text</2> with <3>new text</3> in a <1>text string</1>. This is an Excel function name for the intrinsic function Replace.

Trunc_XL(number)

Numeric

Numeric

Truncates a <1>number</1> by removing the decimal, or fractional, part of the number. This is an Excel function name for the intrinsic function Int.

Parse_Path(filespec,section)

Character, Character

Character

Retrieves the specified <1>section</1> of a <2>path</2>. Valid sections are Drive,Folder,Filename,Filename_Only (Filename without extension), Path (Full path without the filename) and Extension_Only.

Even_XL(number)

Numeric

Numeric

Rounds a positive number up and negative number down to the nearest even integer. This is an Excel function using the intrinsic functions Mod and Ceiling.

Odd_XL(number)

Numeric

Numeric

Rounds a positive number up and negative number down to the nearest odd integer. This is an Excel function using the intrinsic functions Mod and Ceiling.

Sign_XL(number)

Numeric

Numeric

Returns the sign of a number: 1 if the number is positive, zero if the number is zero, or -1 if the number is negative. his is an Excel function using the intrinsic function If.

Concatenate_XL()

Character (limited to 12 strings)

Character

Joins several text strings into one text string. Limited to 12 strings. This is similar to the function in Excel.  You can also join strings by using the + operator.

MonthName_Eng(date)

Date/Time

Character

Returns the English Month name for a specified <1>date</1>. E.g. 01/01/2007 would return January.

WeekdayName_Eng(date)

Date/Time

Character

Returns the English name of the day for a specified <1>date</1>.

Check_SSN(ssn)

Character

Numeric

Performs very simple US Social Security Number verification on a character string representing the SSN.  Returns 1 if the number is valid, otherwise returns 0.  NOTE, this should only be used as a mechanism for assisting in trapping simple data entry errors, not as a validation technique to avoid fraud.

Check_NINumber(NINumber)

Character

Numeric

This is a simple verification check for UK National Insurance Numbers. Returns 1 if the number is valid, otherwise returns 0.  NOTE, this should only be used as a mechanism for assisting in trapping simple data entry errors, not as a validation technique to avoid fraud.

Check_NHSNumber(NHSNumber)

Numeric

Numeric

Checks a UK NHS Number using the rules in http://www.govtalk.gov.uk/gdsc/html/noframes/NHSnumber-2-0-Release.htm.  Returns 1 if the number is valid, otherwise returns 0. NOTE, this should only be used as a mechanism for assisting in trapping simple data entry errors, not as a validation technique to avoid fraud

Check_CC(CCNumber)

Character

Numeric

Checks a <1>string representing a credit card number</1> against the Luhn or MOD 10 formula.  Note this only works for 13, 14, 15 and 16 digit numbers.  Returns 1 if the number is valid, otherwise returns 0. NOTE, this should only be used as a mechanism for assisting in trapping simple data entry errors, not as a validation technique to avoid fraud

JulianCalendarDate_To_Date(juliandate)

Numeric

Date/Time

Converts a <1>Julian Calendar Date</1>, e.g. 2450000 to a date. Note that this does not deal with fractions of a Julian Calendar date, i.e. times.

Pi()

Numeric

Returns the value of Pi to 8 decimal places

Euro_To_Legacy_Currency(Euro_Amount, currency)

Numeric, Character

Numeric

Converts a <1>Euro amount</1> to a legacy currency amount that is now using the Euro.  Valid <2>currency identifiers</2> are ATS, BEF, DEM, ESP, FIM, FRF, GRD, IEP, ITL, LUF, NLG, PTE, and VAL.  If an unknown currency is entered, the function returns null.

Legacy_Currency_To_Euro(Legacy_Amount, currency)

Numeric, Character

Numeric

Legacy_Currency_To_Euro(Legacy_Amount,currency) - Converts a <1>legacy currency amount</1> that is now using the Euro to a Euro amount. Valid <2>currency identifiers</2> are ATS, BEF, DEM, ESP, FIM, FRF, GRD, IEP, ITL, LUF, NLG, PTE, and VAL.  If an unknown currency is entered, the function returns null.

Format_SSN(ssn,format_type)

Character, Character

Character

Formats a US <1>Social Security Number</1>.  To remove hyphens from a SSN, specify a <2>format type</2> of remove_hyphens, to add hyphens, specify a <2>format type</2> of add_hyphens.

End_Of_Previous_Month([input_date])

Date/Time

Date/Time

Returns the date of the last day of the preceding month for an optional specified <1>date</1>. If no input date is specified, the date is assumed to be todays date.

Start_Of_Current_Month([input_date])

Date/Time

Date/Time

Returns the date of the first day of the month for an optional specified <1>date</1>. If no date is specified, the date is assumed to be todays date.

Tomorrow()

Date/Time

Returns tomorrow's date.

Yesterday()

Date/Time

Returns yesterday's date.

Start_Of_Working_Week(date)

Date/Time

Date/Time

Returns the date of the first day of this working week, assuming Monday is the start of the week. If no input date is specified, the date is assumed to be todays date.

End_Of_Working_Week(date)

Date/Time

Date/Time

Returns the date of the last day of a working week of a specified date, assuming Monday is the start of the working week.  If a date falls on a weekend, the last day is assumed to be the day 5 or 6 days later.  If no date is specified, todays date is used.

End_Of_Current_Month([input_date])

Date/Time

Date/Time

Returns the date of the last day of the current month as a date for the specified <1>date</1>.  If no date is specified, then the current date is assumed.

DateSerial1900(date)

Date/Time

Numeric

Returns an Excel style date serial number for a given <1>date</1> based on the 1900 date system.

DateSerial1904(date)

Date/Time

Numeric

Returns an Excel style date serial number for a given <1>date</1> based on the 1904 date system.

_WorkingDays_ComputeDaysOffInRange (startdate,enddate)

Date/Time, Date/Time

Numeric

Returns the number of days off in the range of dates from <1>startdate</1> to <2>enddate</2>. Days off are defined as days that would ordinarily be working days, but are not worked because they are holidays or vacation days. This must be edited before using the Working_Days user defined function.

_WorkingDays_ComputeNormalWorkingDays

InRange(startdate,enddate)

Date/Time, Date/Time

Numeric

Returns the number of normal working days in the range of dates from <1>startdate</1> to <2>enddate</2>. Does not account for holidays or vacation days.

_WorkingDays_ComputeWorkingDays

InRange(startdate,enddate)

Date/Time, Date/Time

Numeric

Returns the number of working days in the range of dates from <1>startdate</1> to <2>enddate</2>. This calculation takes into account holidays that fall on dates that what would otherwise be working days.

CountOccurrencesOfWeekdayInRange

(weekday,startdate,enddate)

Numeric, Date/Time, Date/Time

Numeric

Returns the number of occurrences of the given <1>weekday</1> (1=Sunday, 2=Monday, etc.) that fall in the range of dates from <2>startdate</2> to <3>enddate</3>.

DateIsInRange(date,startdate,enddate)

Date/Time, Date/Time, Date/Time

Numeric

Returns 1 if the given <1>date</1> is in the range of dates starting with and including <2>startdate</2> and ending with but not including <3>enddate</3>. Returns 0 if the given <1>date</1> is not in the range.

_WorkingDays_DateIsWorkingDay

InRange(date,startdate,enddate)

Date/Time, Date/Time, Date/Time

Numeric

Returns 1 if the given <1>date</1> falls on a work day that is in the range of dates from <2>startdate</2> to <3>enddate</3>. Returns 0 otherwise.

_WorkingDays_FirstValidDateForWorkingDays()

Date/Time

Returns the first date that is valid for use in the WorkingDays function.  This must be checked/edited before first using the WorkingDays function.

_WorkingDays_IsValidDateRange

ForWorkingDays(startdate,enddate)

Date/Time, Date/Time

Numeric

Returns 1 if the range of dates from <1>startdate</1> to <2>enddate</2> is a valid range for use in the WorkingDays function. Returns 0 otherwise.

_WorkingDays_IsWorkingDay(weekday)

Numeric

Numeric

Returns 1 if the given <1>weekday</1> (1=Sunday, 2=Monday, etc.) is normally a work day, else returns 0.  This must be checked/edited before first using the WorkingDays function. It is currently set to a work week of Monday to Friday.

_WorkingDays_LastValidDateForWorkingDays()

Date/Time

Returns the last date that is valid for use in the WorkingDays user-defined function.  This must be checked/edited before first using the WorkingDays function.

Weekday_Is_In_Partial_Week (weekday, startday, endday)

Numeric, Numeric, Numeric

Returns 1 if the given <1>weekday</1> (1=Sunday, 2=Monday, etc.) is in the partial week that extends from the given <2>startday</2> up to but not including the following <3>endday</3>. Returns 0 otherwise.

Working_Days(startdate,enddate)

Date/Time, Date/Time

Numeric

Returns the number of working days in the range of dates from <1>startdate</1> to <2>enddate</2>. This calculation takes into account holidays that fall on dates that what would otherwise be normal working days.  Before using this function, you MUST customize the following user defined functions, starting with _WorkingDays_ : ComputeDaysOffInRange, IsWorkingDay, FirstValidDateForWorkingDays and LastValidDateForWorkingDays.

Qtr_Advanced(date,q1_start)

Date/Time, Date/Time

Numeric

Returns the quarter (1-4) of a <1>date</1>, based on a <2>different starting date for Quarter 1</2>.  For example, if the date specified for the start of Q1 was 1st October 2006, then 31st January 2007 would be 2. Note that you must specify a full date for the start of Q1, but the year part is not significant, the function will work over all years for the input date.

Unix_Time_Number_To_Date(unix_time)

Numeric

Date/Time

Converts a <1>Unix or Epoch time number</1> based on UTC to a date/time. Note that Date/Time is only accurate to the second.  Unix time numbers with fractions of a second will be rounded.

Date_To_Unix_Time_Number(date)

Date/Time

Numeric

Converts a <1>date</1> based on UTC to a Unix or Epoch Time Number. Note that Date/Time is only accurate to the second.