false1674457662997761671193514680224842144083888638421504167335258388736126486417566335167281923276842108162551676083116777215148034259934743<font face="Verdana" size="12"/>DefaultC:\Documents and Settings\All Users\Documents\Monarch\Models\UserDefinedFunctions.xmodDetaildetail11falsefalse1ÑÑÑ040 ABC123 Train Spares 06/09/2007 123456 001 1 15,235.60 36 37 Spares TrainsWarehouse5C116L11511Project Number7C216L11717Project Description13C318L1113115Book Date8D1410L1110132Order Numberx13Ctrue613L1113143Line Number3C73L113158Book Qty18N0109R119172Book Value18N211115R1113182Week Entered18N01215R113196Current Week18N01315R1131103Work Type12C819L11121111Work Area9C99L1191124Demo120Ctrue1420L"Work Area"Demo220Ctrue1520L"Work Type"Demo320Ctrue1620L"Project Description"Table Total20Ctrue1720L"Table Total"Order Number20C520Ltrim([Order Numberx])Demo430Ctrue1830L" Assuming costs at 75%"By Type OriginalTable Total OriginalTABLE_TO01falsefalsefalsefalsefalsetruetruefalseTable Total20truefalsefalsefalsefalsefalse14Table TotalSUM([Book Value])Table TotalTABLE_TOTAfalsefalsefalsefalsefalsetruetruefalseTable TotalWarehouse20truefalsefalsefalsefalsefalse14Table TotalSUM([Book Value])Summary Data OriginalSUMMARY_01falsefalsefalsefalsefalsetruetruefalseWarehouse10truetruefalsetruetruefalsefalseProject Number13falsefalsefalsefalsefalseOrder Number20falsefalsefalsefalsefalseDemo420truefalsefalsefalsefalsefalse14WarehouseSUM([Book Value])14WarehouseSUM([Book Value])*.7514WarehouseSUM([Book Value])-(sum([book value])*.75)Summary DataSUMMARY_DAfalsefalsefalsefalsefalsetruetruefalseWarehouse10truetruefalsetruetruefalsefalseProject Number13falsefalsefalsefalsefalseOrder Number20falsefalsefalsefalsefalseDemo4Work Type20truefalsefalsefalsefalsefalse14WarehouseProject NumberOrder NumberWarehouseProject NumberWarehouseSUM([Book Value])Week Entered14WarehouseProject NumberOrder NumberWarehouseProject NumberWarehouseSUM([Book Value])*.75Current Week14WarehouseProject NumberOrder NumberWarehouseProject NumberWarehouseSUM([Book Value])-(sum([book value])*.75)By Area OriginalBY_AREA_ORfalsefalsefalsefalsefalsetruetruefalseDemo120truetruetruetruefalsefalsefalseWork Area13truefalsefalsefalsefalsefalse14Demo1Work AreaDemo1SUM([Book Value])By AreaBY_AREAfalsefalsefalsefalsefalsetruetruefalseDemo1Warehouse20truetruetruetruefalsefalsefalseWork AreaProject Number13truefalsefalsefalsefalsefalse14Demo1Work AreaDemo1SUM([Book Value])By Type OriginalBY_TYPE_ORfalsefalsefalsefalsefalsetruetruefalseDemo220truefalsetruefalsefalsefalseWork Type15truefalsefalsefalsefalsefalse14Demo2Work TypeDemo2SUM([Book Value])By TypeBY_TYPEfalsefalsefalsefalsefalsetruetruefalseDemo2Warehouse20truefalsetruefalsefalsefalseWork TypeProject Number15truefalsefalsefalsefalsefalse14Demo2Work TypeDemo2SUM([Book Value])Project Description OriginalPROJECT_01falsefalsefalsefalsefalsetruetruefalseDemo320truetruefalsefalsefalseProject Description16falsefalsefalsefalsefalse14Demo3Project DescriptionDemo3SUM([Book Value])Project DescriptionPROJECT_DEfalsefalsefalsefalsefalsetruetruefalseDemo3Warehouse20truetruefalsefalsefalseProject DescriptionProject Number16falsefalsefalsefalsefalse14Demo3Project DescriptionDemo3SUM([Book Value])Type By Area OriginalTYPE_BY_01falsefalsefalsefalsefalsetruetruefalseDemo220truetruetruetruefalsefalseWork Type15truefalsetruetruefalsefalseWork Area9falsefalsefalsefalsefalse14Demo2Work TypeDemo2Demo2Work TypeWork AreaSUM([Book Value])Type by AreaTYPE_BY_ARfalsefalsefalsefalsefalsetruetruefalseDemo2Warehouse20truetruetruetruefalsefalseWork TypeProject Number15truetruetruetruetruefalsefalseWork AreaOrder Number12truefalsefalsefalsefalsefalse14Demo2Work TypeWork AreaDemo2Work TypeDemo2SUM([Book Value])_WorkingDays_ComputeDaysOffInRangeThis is essentially a list of holidays that must be edited before starting to use the Working_Days user defined function.
Here are the other user defined functions that also need to be edited before using Working_Days:
_WorkingDays_FirstValidDateForWorkingDays
_WorkingDays_IsWorkingDay
_WorkingDays_LastValidDateForWorkingDays_WorkingDays_ComputeDaysOffInRange(startdate,enddate) 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.date_WorkingDays_ComputeNormalWorkingDaysInRange_WorkingDays_ComputeNormalWorkingDaysInRange(startdate,enddate) 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.date_WorkingDays_ComputeWorkingDaysInRange_WorkingDays_ComputeWorkingDaysInRange(startdate,enddate) 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._WorkingDays_DateIsWorkingDayInRange_WorkingDays_DateIsWorkingDayInRange(date,startdate,enddate) 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_FirstValidDateForWorkingDaysThis defines the first valid date for which the Working_Days user defined function works with. As the list of holidays is user defined, and may only cover a few years, this is a method to ensure the Working_Days function returns null if the date range falls outside the range for which holidays have been defined.
Here are the other user defined functions that also need to be edited before using Working_Days:
_WorkingDays_FirstValidDateForWorkingDays
_WorkingDays_IsWorkingDay
_WorkingDays_LastValidDateForWorkingDays
_WorkingDays_IsWorkingDay
_WorkingDays_LastValidDateForWorkingDays
_WorkingDays_ComputeDaysOffInRange_WorkingDays_FirstValidDateForWorkingDays() 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_IsValidDateRangeForWorkingDays_WorkingDays_IsValidDateRangeForWorkingDays(startdate,enddate) 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_IsWorkingDayThis defines the days which are to be normal considered working days for use with the Working_Days user-defined function. Currently it is set to Monday to Friday. This must be edited before using the Working_Days function if your normal working days are not Monday to Friday.
Here are the other user defined functions that also need to be edited before using Working_Days:
_WorkingDays_FirstValidDateForWorkingDays
_WorkingDays_LastValidDateForWorkingDays
_WorkingDays_ComputeDaysOffInRange_WorkingDays_IsWorkingDay(weekday) 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.date_WorkingDays_LastValidDateForWorkingDaysThis defines the last valid date for which the Working_Days user defined function works with. As the list of holidays is user defined, and may only cover a few years, this is a method to ensure the Working_Days function returns null if the date range falls outside the range for which holidays have been defined.
Here are the other user defined functions that also need to be edited before using Working_Days:
_WorkingDays_FirstValidDateForWorkingDays
_WorkingDays_IsWorkingDay
_WorkingDays_ComputeDaysOffInRange_WorkingDays_LastValidDateForWorkingDays() 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.Char_XLChar_XL(number)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.stringCheck_CCCheck_CC(CCNumber)This 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 fraudstringspecialCheck_NHSNumberCheck_NHSNumber(NHSNumber)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 fraudnumericspecialCheck_NINumberCheck_NINumber(NINumber)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. Taken from information at http://www.govtalk.gov.uk/gdsc/html/frames/NationalInsuranceNumber-2-1-Release.htmstringspecialCheck_SSNCheck_SSN(ssn)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. Official tools for employers are available at www.socialsecurity.gov/employer/ssnv.htmstringspecialCode_XLCode_XL(text)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.stringConcatenate_XLConcatenate_XL()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.stringCountOccurrencesOfWeekdayInRangeCountOccurrencesOfWeekdayInRange(weekday,startdate,enddate) 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>.Date_To_Unix_Time_NumberDate_To_Unix_Time_Number(date) This 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.dateconversionDateIsInRangeDateIsInRange(date,startdate,enddate) 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.dateDateSerial1900DateSerial1900(date)Returns an Excel style date serial number for a given <1>date</1> based on the 1900 date system.dateconversionDateSerial1904DateSerial1904(date)Returns an Excel style date serial number for a given <1>date</1> based on the 1904 date system.dateconversionDisplay_NameDisplay_Name(First,Last[,Middle[,Title[,Suffix]]]) 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>stringEnd_Of_Current_MonthEnd_Of_Current_Month([input_date])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.dateEnd_Of_Previous_MonthEnd_Of_Previous_Month([input_date])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.dateEnd_Of_Working_WeekEnd_Of_Working_Week(date)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.dateEuro_To_Legacy_CurrencyEuro_To_Legacy_Currency(Euro_Amount,currency)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.numericconversionEven_XLEven_XL(number)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.numericFormat_SSNFormat_SSN(ssn,format_type)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.stringJulianCalendarDate_To_DateJulianCalendarDate_To_Date(juliandate)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.dateconversionLegacy_Currency_To_EuroLegacy_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.numericconversionMid_XLMid_XL(text,start_num,num_chars)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.stringMonthName_EngMonthName_Eng(date)Returns the English Month name for a specified <1>date</1>. E.g. 01/01/2007 would return January.dateOdd_XLOdd_XL(number)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.numericParse_PathParse_Path(filespec,section)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.stringPiPi()Returns the value of Pi to 8 decimal placesnumericQtr_AdvancedQtr_Advanced(date,q1_start)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.dateReplace_XLReplace_XL(old_text,start_num,num_chars,new_text)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.stringSearch_XLSearch_XL(find_text,within_text,start_num)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.stringSign_XLSign_XL(number)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.numericStart_Of_Current_MonthStart_Of_Current_Month([input_date])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.dateStart_Of_Working_WeekStart_Of_Working_Week(date)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.dateSubstitute_XLSubstitute_XL(text,old_text,new_text)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.stringTomorrowTomorrow()Returns tomorrow's date.dateTrunc_XLTrunc_XL(number)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.numericconversionUnix_Time_Number_To_DateUnix_Time_Number_To_Date(unix_time) This 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.dateconversionValue_XLValue_XL(text)Converts a <1>text</1> string that represents a number to a number. This is an Excel function name for the intrinsic function Val.numericconversionWeekday_Is_In_Partial_WeekWeekday_Is_In_Partial_Week(weekday, startday, endday) 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.WeekdayName_EngWeekdayName_Eng(date)Returns the English name of the day for a specified <1>date</1>.dateWorking_DaysWorking_Days(startdate,enddate) 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.dateYesterdayYesterday()Returns yesterday's date.date