Common Examples of Custom Lead Fields

Carlee Benito

Modified on: Fri, 23 Oct, 2020 at 1:08 PM

In this article, you will find several Custom Field Examples for reference.

Operators, Functions, and Wildcard Characters
Converting Date Formats

Calculating Based Off of Fields
UK Custom Field Examples
Others/Misc.

Custom Fields may be used within the Lead Gen platform or within Reports when you are creating a custom column.

Custom Field Operators and Functions from the MSDN Page (http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx; C#)

*BOOL Fields. Be advised that BOOL fields can fail to evaluate if NO values are passed. By simply adding the string handlers around the token and a Capitalized True will suffice.

Operators

Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

When you create comparison expressions, the following operators are allowed:

The following arithmetic operators are also supported in expressions:

(addition)
(subtraction)

* (multiplication)

/ (division)

% (modulus)

Functions

The following functions are also supported:

CONVERT

Description

Converts particular expression to a specified .NET Framework Type.

Syntax

Convert(expression, type)

Arguments

expression -- The expression to convert.

type -- The .NET Framework type to which the value will be converted.

Example: Convert('#year#','System.Int32')

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

LEN

Description

Gets the length of a string

Syntax

LEN(expression)

Arguments

expression -- The string to be evaluated.

Example: LEN('#phone_work#')

ISNULL

Description

Checks an expression and either returns the checked expression or a replacement value.

Syntax

ISNULL(expression, replacementvalue)

Arguments

expression -- The expression to check.

replacementvalue -- If expression is Nothing, replacementvalue is returned.

Example: ISNULL('#price#','-1')

IIF

Description

Gets one of two values depending on the result of a logical expression.

Syntax

IIF(expr, truepart, falsepart)

Arguments

expr -- The expression to evaluate.

truepart -- The value to return if the expression is true.

falsepart -- The value to return if the expression is false.

Example: IIF('#total#'>1000, 'expensive', 'you are cheap')

TRIM

Description

Removes all leading and trailing blank characters like \r, \n, \t, ' '

Syntax

TRIM(expression)

Arguments

expression -- The expression to trim.

SUBSTRING

Description

Gets a sub-string of a specified length, starting at a specified point in the string.

Syntax

SUBSTRING(expression, start, length)

Arguments

expression -- The source string for the substring.

start -- Integer that specifies where the substring starts.

length -- Integer that specifies the length of the substring.

Example: SUBSTRING('#phone#', 7, 8)

Wildcard Characters

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:

"ItemName LIKE '*product*'"

"ItemName LIKE '*product'"

"ItemName LIKE 'product*'"

Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.

Testing Link: http://demo.cakemarketing.com/ad.aspx#bc852

This URL links you directly to a Buyer Contract in the "Custom Field" Vertical with a LeadID in the Contract Name that you can use test Custom Fields. The Vertical is in the Breadcrumb, so you can get to that very quickly to work on your Custom Fields for Testing.

*Please leave it neat like you found it :)

Converting Date Format Examples

Convert #date_now# to 24-hour Format
Standard Date (MM/DD/YYYY) to 3 Fields (month/day/year; based on #dob#)
ISODate (YYYY-MM-DD) => Real Date (MM/DD/YYYY)
MM/DD/YYYY to YYYY-MM-DD
Date Field with Dashes:

Convert #date_now# to 24-hour format:

ampm - string - SUBSTRING('#date_now#',LEN('#date_now#')-1,2)

findspace - number - IIF(SUBSTRING('#date_now#',8,1)=' ',8,IIF(SUBSTRING('#date_now#',9,1)=' ',9,10))

pulltime - string - SUBSTRING('#date_now#',#findspace#+2,LEN('#date_now#')-4-#findspace#)

timelen - number - IIF(LEN('#pulltime#')=7,SUBSTRING('#pulltime#',1,1),SUBSTRING('#pulltime#',1,2))

maketime - string - IIF('#ampm#'='PM',#timelen#+12,#timelen#)+SUBSTRING('#pulltime#',LEN('#timelen#')+1,10)

makedatetime - string - SUBSTRING('#date_now#',1,#findspace#)+' '+'#maketime#'MM/DD/YYYY => YYYY/DD/MM:

Formula: SUBSTRING('10/24/1988',7,4)+'/'+SUBSTRING('10/24/1988',4,2)+'/'+SUBSTRING('10/24/1988',1,2)

Start:10/24/1988 Result : 1988/24/10

Standard Date (MM/DD/YYYY) to 3 Fields (month/day/year; based on #dob#):

month = NUM = SUBSTRING('#dob#',1,2)

date = NUM = SUBSTRING('#dob#',4,2)

year = NUM = SUBSTRING('#dob#',7,4)

ISODate (YYYY-MM-DD) => Real Date (MM/DD/YYYY):

SUBSTRING('#ISODate#',6,2)+'/'+SUBSTRING('#ISODate#',9,2)+'/'+SUBSTRING('#ISODate#',1,4)

MM/DD/YYYY to YYYY-MM-DD

SUBSTRING('#pay_date1#',7,4)+'-'+SUBSTRING('#pay_date1#',1,2)+'-'+SUBSTRING('#pay_date1#',4,2)

Month Abbrev (based on #DateOfBirth# dd-mm-yyyy)

Fields: DateOfBirth:24-10-1988

Formula: IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=1,'Jan',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=2,'Feb',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=3,'Mar',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=4,'Apr',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=5,'May',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=6,'Jun',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=7,'Jul',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=8,'Aug',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=9,'Sep',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=10,'Oct',IIF(CONVERT(SUBSTRING('24-10-1988',4,2),'System.Int32')=11,'Nov','Dec')))))))))))

Result : Oct

Raw Formula:

IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=1,'Jan',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=2,'Feb',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=3,'Mar',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=4,'Apr',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=5,'May',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=6,'Jun',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=7,'Jul',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=8,'Aug',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=9,'Sep',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=10,'Oct',IIF(CONVERT(SUBSTRING('#DateOfBirth#',4,2),'System.Int32')=11,'Nov','Dec')))))))))))

Date Field with Dashes:

Fields: any_date_field:MM/DD/YYYY

Formula: SUBSTRING('MM/DD/YYYY',1,2)+'-'+SUBSTRING('MM/DD/YYYY',4,2)+'-'+SUBSTRING('MM/DD/YYYY',7,4)

Result : MM-DD-YYYY

Date_Now - 24hr.

This shows the process taken to get date_now normalized and in 24-hour format without the AM/PM at the end. There's two variables that should be used here. They are labeled on top of the code pieces. View in SQL mode to see better and make sure to take off the star and slash at the ends. The layout shows the first part and is then replacing the ## in the next part and then that replaces the next ## and so on till the variable is listed*/

12/11/2012 04:06:24 PM

IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2))

IIF(SUBSTRING(##,6,1)='/',##,SUBSTRING(##,1,3)+'0'+SUBSTRING(##,4,LEN(##)-3))

IIF(SUBSTRING(##,14,1)=':',##,SUBSTRING(##,1,11)+'0'+SUBSTRING(##,12,LEN(##)-11))

/*THIS IS THE FIRST VARIABLE. Name this "first_set" and make it a string type.........................................

IIF(SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),14,1)=':',IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),1,11)+'0'+SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)),12,LEN(IIF(SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),6,1)='/',IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),1,3)+'0'+SUBSTRING(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)),4,LEN(IIF(SUBSTRING('#date_now#',3,1)='/','#date_now#','0'+SUBSTRING('#date_now#',1,2)+SUBSTRING('#date_now#',3,LEN('#date_now#')-2)))-3)))-11))

12 hour to 24 hour and normalize the time. Starting new variable because of IIF limit----------------------

IIF(SUBSTRING(#first_set#,LEN(#first_set#)-1,2)='PM',SUBSTRING(#first_set#,1,11)+(CONVERT(SUBSTRING(#first_set#,12,2),'System.Int32')+12)+SUBSTRING(#first_set#,14,LEN(#first_set#)-12),#first_set#)

IIF(SUBSTRING(##,17,1)=':',##,SUBSTRING(##,1,14)+'0'+SUBSTRING(##,15,LEN(##)-14))

IIF(SUBSTRING(##,20,1)=' ',##,SUBSTRING(##,1,17)+'0'+SUBSTRING(##,18,LEN(##)-17))

SUBSTRING(##,1,19)

/*THIS IS THE SECOND VARIABLE. Make this variable whatever you want to name it and give it a string type.........................................

SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),20,1)=' ',IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),1,17)+'0'+SUBSTRING(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)),18,LEN(IIF(SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),17,1)=':',IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),1,14)+'0'+SUBSTRING(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'),15,LEN(IIF(SUBSTRING('#first_set#',LEN('#first_set#')-1,2)='PM',SUBSTRING('#first_set#',1,11)+(CONVERT(SUBSTRING('#first_set#',12,2),'System.Int32')+12)+SUBSTRING('#first_set#',14,LEN('#first_set#')-12),'#first_set#'))-14)))-17)),1,19)

This is the output format

MM/DD/YYYY HH:MM:SS

Calculating Based Off of Fields

Calculate age (based on #dob_month#, #dob_day#, #dob_year#)
Calculate age (based on #dob# MM/DD/YYYY)
Calculate age (based on #dob#YYYY/MM/DD)
Last Payday (based on #pay_frequency# [weekly,bi\weekly,twice_monthly,monthly], #payday\next#, #payday_following#)
Week Number (based on #month#, #date#, #year#; 2012-2013)
Date After Lead Submission (based on #ISODate# output: MM/DD/YYYY)
Output the total number of months (based on YYYY-MM-DD Date)
Weekday Determination (based on #dob# - MM/DD/YYYY)
Total Months to Years+Months (based on #months#)
Employed Start Date (based on #months#)
Employed Start Date (based on #months#, #months_to_years#, #leftover_months#)
Employed Start Date (based on #months#, #months_to_years#, #leftover_months#; uses #date\now#_)

Calculate age (based on #dob_month#, #dob_day#, #dob_year#):

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-CONVERT('#dob_year#','System.Int32')+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')<CONVERT('#dob_month#','System.Int32'),-1, IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=CONVERT('#dob_month#','System.Int32')) AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=CONVERT('#dob_day#','System.Int32')),-1,0))

Calculate age (based on #dob# MM/DD/YYYY):

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')<CONVERT(SUBSTRING('#dob#',1,2),'System.Int32'),-1, IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')) AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',4,2),'System.Int32')),-1,0))

Calculate age (based on #dob# DD/MM/YYYY):
CONVERT(SUBSTRING(‘#ISODate#’,1,4),‘System.Int32’)-CONVERT(SUBSTRING(‘#dob#’,7,4),‘System.Int32’)+IIF(CONVERT(SUBSTRING(‘#ISODate#’,6,2),‘System.Int32’)<CONVERT(SUBSTRING(‘#dob#’,4,2),‘System.Int32’),-1,IIF((CONVERT(SUBSTRING(‘#ISODate#’,6,2),‘System.Int32’)=CONVERT(SUBSTRING(‘#dob#’,4,2),‘System.Int32’))AND(CONVERT(SUBSTRING(‘#ISODate#’,9,2),‘System.Int32’)=CONVERT(SUBSTRING(‘#dob#’,1,2),‘System.Int32’)),-1,0))

Calculate age (based on #dob#YYYY/MM/DD):

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-CONVERT(SUBSTRING('#dob#',1,4),'System.Int32')+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')<CONVERT(SUBSTRING('#dob#',6,2),'System.Int32'),-1, IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',6,2),'System.Int32')) AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')),-1,0))

Last Payday (based on #pay_frequency# [weekly,bi\weekly,twice_monthly,monthly], #payday\next#, #payday_following#):

For "weekly":

IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7<1 AND CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')=1,12,IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-7<1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')-1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')))+'/'+IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-7>0,IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7<10,'0'+(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7),CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7),IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1 IN (1,3,5,7,8,10,12),31,IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1=2,28,30))+CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7)+'/'+(CONVERT(SUBSTRING('#payday_next#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-7<1 AND CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')=1,1,0))

For "bi_weekly":

IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14<1 AND CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')=1,12,IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-14<1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')-1,CONVERT(SUBSTRING('#payday\_next#',1,2),'System.Int32')))+'/'+IIF(CONVERT(SUBSTRING('#payday\_next#',4,2),'System.Int32')-14>0,IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14<10,'0'+(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14),CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14),IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1 IN (1,3,5,7,8,10,12),31,IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1=2,28,30))+CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14)+'/'+(CONVERT(SUBSTRING('#payday_next#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')-14<1 AND CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')=1,1,0))

For "twice_monthly":

IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1)+'/'+IIF(CONVERT(SUBSTRING('#payday_following#',4,2),'System.Int32')<29,SUBSTRING('#payday_following#',4,2),IIF(IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1) IN (1,3,5,7,8,10,12),31,IIF(IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1)=2,28,30)))+'/'+(CONVERT(SUBSTRING('#payday_following#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_following#',1,2),'System.Int32')-1<1,1,0))

For "monthly":

IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1)+'/'+IIF(CONVERT(SUBSTRING('#payday_next#',4,2),'System.Int32')<29,SUBSTRING('#payday_next#',4,2),IIF(IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1) IN (1,3,5,7,8,10,12),31,IIF(IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,12,CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1)=2,28,30)))+'/'+(CONVERT(SUBSTRING('#payday_next#',7,4),'System.Int32')-IIF(CONVERT(SUBSTRING('#payday_next#',1,2),'System.Int32')-1<1,1,0))

Week Number (based on #month#, #date#, #year#; 2012-2013):

IIF(#year#=2012,IIF(#month#=1,IIF(#date#<2,52,IIF(#date#<9,1,IIF(#date#<16,2,IIF(#date#<23,3,IIF(#date#<30,4,5))))),IIF(#month#=2,IIF(#date#<6,5,IIF(#date#<13,6,IIF(#date#<20,7,IIF(#date#<27,8,9)))),IIF(#month#=3,IIF(#date#<5,9,IIF(#date#<12,10,IIF(#date#<19,11,IIF(#date#<26,12,13)))),IIF(#month#=4,IIF(#date#<2,13,IIF(#date#<9,14,IIF(#date#<16,15,IIF(#date#<23,16,IIF(#date#<30,17,18))))),IIF(#month#=5,IIF(#date#<7,18,IIF(#date#<14,19,IIF(#date#<21,20,IIF(#date#<28,21,22)))),IIF(#month#=6,IIF(#date#<4,22,IIF(#date#<11,23,IIF(#date#<18,24,IIF(#date#<25,25,26)))),IIF(#month#=7,IIF(#date#<2,26,IIF(#date#<9,27,IIF(#date#<16,28,IIF(#date#<23,29,IIF(#date#<30,30,31))))),IIF(#month#=8,IIF(#date#<6,31,IIF(#date#<13,32,IIF(#date#<20,33,IIF(#date#<27,34,35)))),IIF(#month#=9,IIF(#date#<3,35,IIF(#date#<10,36,IIF(#date#<17,37,IIF(#date#<24,38,39)))),IIF(#month#=10,IIF(#date#<1,39,IIF(#date#<8,40,IIF(#date#<15,41,IIF(#date#<22,42,IIF(#date#<29,43,44))))),IIF(#month#=11,IIF(#date#<5,44,IIF(#date#<12,45,IIF(#date#<19,46,IIF(#date#<26,47,48)))),IIF(#month#=12,IIF(#date#<3,48,IIF(#date#<10,49,IIF(#date#<17,50,IIF(#date#<24,51,IIF(#date#<31,52,1))))),'')))))))))))),IIF(#year#=2013,IIF(#month#=1,IIF(#date#<7,1,IIF(#date#<14,2,IIF(#date#<21,3,IIF(#date#<28,4,5)))),IIF(#month#=2,IIF(#date#<4,5,IIF(#date#<11,6,IIF(#date#<18,7,IIF(#date#<25,8,9)))),IIF(#month#=3,IIF(#date#<4,9,IIF(#date#<11,10,IIF(#date#<18,11,IIF(#date#<25,12,13)))),IIF(#month#=4,IIF(#date#<1,13,IIF(#date#<8,14,IIF(#date#<15,15,IIF(#date#<22,16,IIF(#date#<29,17,18))))),IIF(#month#=5,IIF(#date#<6,18,IIF(#date#<13,19,IIF(#date#<20,20,IIF(#date#<27,21,22)))),IIF(#month#=6,IIF(#date#<3,22,IIF(#date#<10,23,IIF(#date#<17,24,IIF(#date#<24,25,26)))),IIF(#month#=7,IIF(#date#<1,26,IIF(#date#<8,27,IIF(#date#<15,28,IIF(#date#<22,29,IIF(#date#<29,30,31))))),IIF(#month#=8,IIF(#date#<5,31,IIF(#date#<12,32,IIF(#date#<19,33,IIF(#date#<26,34,35)))),IIF(#month#=9,IIF(#date#<2,35,IIF(#date#<9,36,IIF(#date#<16,37,IIF(#date#<23,38,IIF(#date#<30,39,40))))),IIF(#month#=10,IIF(#date#<7,40,IIF(#date#<14,41,IIF(#date#<21,42,IIF(#date#<28,43,44)))),IIF(#month#=11,IIF(#date#<4,44,IIF(#date#<11,45,IIF(#date#<18,46,IIF(#date#<25,47,48)))),IIF(#month#=12,IIF(#date#<2,48,IIF(#date#<9,49,IIF(#date#<16,50,IIF(#date#<23,51,IIF(#date#<30,52,1))))),'')))))))))))),''))

Date After Lead Submission (based on #ISODate# output: MM/DD/YYYY):

IIF(IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')))))<10,'0'+IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32'))))),IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32'))))))+'/'+IIF(IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=28,1,CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')+1))))<10,'0'+IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=28,1,CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')+1)))),IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (1,3,5,7,8,10,12)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,1,IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32') IN (4,6,9,11)) AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=30,1,IIF((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0)) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=29,1,IIF((NOT(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')%100<>0))) AND CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=2 AND (CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32'))=28,1,CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')+1)))))+'/'+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')=12 AND CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')=31,CONVERT(SUBSTRING('#ISODate#',1,2),'System.Int32')+1,CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32'))

Output the total number of months (based on YYYY-MM-DD Date). Calculating Against Date Now.

CONVERT(SUBSTRING('#date_now#',6,4),'System.Int32')-CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',1,4),'System.Int32')+IIF(CONVERT(IIF(SUBSTRING('#date_now#',2,1)='/','0'+SUBSTRING('#date_now#',1,1),SUBSTRING('#date_now#',1,2)),'System.Int32')<CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',6,2),'System.Int32'),-1, IIF((CONVERT(IIF(SUBSTRING('#date_now#',2,1)='/','0'+SUBSTRING('#date_now#',1,1),SUBSTRING('#date_now#',1,2)),'System.Int32')=CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',6,2),'System.Int32')) AND (CONVERT(SUBSTRING('#date_now#',3,2),'System.Int32')=CONVERT(SUBSTRING('#Any_YYYY-MM-DD_datefield#',9,2),'System.Int32')),-1,0))

Weekday Determination (based on #dob# - MM/DD/YYYY):

century_table = NUM = 2*(3-(CONVERT(SUBSTRING('#dob#',7,2),'System.Int32')%4))

last_2 = NUM = _CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')

_

year_math = NUM =IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=24,24,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=23,23,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=22,22,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=21,21,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=20,20,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=19,19,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=18,18,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=17,17,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=16,16,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=15,15,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=14,14,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=13,13,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=12,12,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=11,11,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=10,10,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=9,9,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=8,8,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=7,7,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=6,6,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=5,5,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=4,4,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=3,3,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=2,2,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=1,1,IIF((CONVERT(SUBSTRING('#dob#',9,2),'System.Int32')/4)>=0,0,0)))))))))))))))))))))))))

leap_year = NUM = IIF((CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')%400=0) OR ((CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')%4=0) AND (CONVERT(SUBSTRING('#dob#',7,4),'System.Int32')%100<>0)),1,0)

month_table = NUM =IIF(#leap\year#=1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=1,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=2,2,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=3,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=4,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=5,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=6,4,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=7,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=8,2,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=9,5,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=10,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=11,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=12,5,0)))))))))))),IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=1,0,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=2,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=3,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=4,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=5,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=6,4,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=7,6,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=8,2,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=9,5,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=10,1,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=11,3,IIF(CONVERT(SUBSTRING('#dob#',1,2),'System.Int32')=12,5,0)))))))))))))_

date = NUM = CONVERT(SUBSTRING('#dob#',4,2),'System.Int32')

total = NUM = #century\table#+#last_2#+#year_math#+#month_table#+#date#_

day = STRING =IIF(#total#%7=0,'Sunday',IIF(#total#%7=1,'Monday',IIF(#total#%7=2,'Tuesday',IIF(#total#%7=3,'Wednesday',IIF(#total#%7=4,'Thursday',IIF(#total#%7=5,'Friday',IIF(#total#%7=6,'Saturday','Poop')))))))

Total Months to Years+Months (based on #months#); seems easy, and it is, BUT, it helps to build into much more complicated Custom Fields):

months_to_years = NUM = (#months#-(#months#%12))/12

leftover_months = NUM = #months#%12

Employed Start Date (based on #months#); This one will default the day to '01':

CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-((#months#-(#months#%12))/12)-IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,1,0)+'-'+IIF(IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+12)-(#months#%12),CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12))<10,'0'+IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+12)-(#months#%12),CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)),IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)<1,(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')+12)-(#months#%12),CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#months#%12)))+'-01'

Employed Start Date (based on #months#, #months_to_years#, #leftover_months#); also, if the Day is > 28, then it makes it 28, so we don't get "illegal" dates like 2/31:

IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover_months#%12)<1,IIF(((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))+12)<10,'0'+((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))+12),(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))+12),IIF((CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))<10,'0'+(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12)),(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover\_months#%12))))+'/'+IIF(CONVERT(SUBSTRING('#ISODate#',9,2),'System.Int32')>28,'28',SUBSTRING('#ISODate#',9,2))+'/'+(CONVERT(SUBSTRING('#ISODate#',1,4),'System.Int32')-#months_to_years#-IIF(CONVERT(SUBSTRING('#ISODate#',6,2),'System.Int32')-(#leftover_months#%12)<1,1,0))

Employed Start Date (based on #months#, #months_to_years#, #leftover_months#; uses #date\now#_); also, if the Day is > 28, then it makes it 28, so we don't get "illegal" dates like 2/31:

NUM – months_to_years - (#months_employed#-(#months_employed#%12))/12

NUM – lefover_months - #months_employed#%12

NUM – andy1length - IIF(SUBSTRING('#date_now#',11,1)='',11,IIF(SUBSTRING('#date_now#',10,1)='',10,9))-5

NUM – andy2month - IIF(SUBSTRING('#date_now#',3,1)='/',4,3)

NUM – andy3dateparse - IIF(IIF(SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)>28,28,SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#))<10,'0'+IIF(SUBSTRING('#date\_now#',#andy2month#,#andy1length#-#andy2month#)>28,28,SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)),IIF(SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)>28,28,SUBSTRING('#date_now#',#andy2month#,#andy1length#-#andy2month#)))

NUM – andy4yearparse - SUBSTRING('#date_now#',#andy1length#+1,4)

NUM – andy5monthparse - SUBSTRING('#date_now#',1,#andy2month#-2)

NUM – andy6monthtotal - IIF(IIF(#andy5monthparse#-#leftover_months#<1,12+(#andy5monthparse#-#leftover_months#),#andy5monthparse#-#leftover_months#)<10,'0'+IIF(#andy5monthparse#-#leftover_months#<1,12+(#andy5monthparse#-#leftover_months#),#andy5monthparse#-#leftover_months#),IIF(#andy5monthparse#-#leftover_months#<1,12+(#andy5monthparse#-#leftover_months#),#andy5monthparse#-#leftover_months#))

NUM – andy7yeartotal - #andy4yearparse#-#months_to_years#-IIF(#andy5monthparse#-#leftover_months#<0,1,0)

NUM – andy8build - '#andy7yeartotal#'+'-'+'#andy6monthtotal#'+'-'+'#andy3dateparse#'

UK Custom Field Examples

REGEX for UK Zip Codes
Determine What UK Post Codes Start With (used as filter for purchasing)
UK phone validation

REGEX for UK Zip Codes:

(((B|E|G|L|M|N|S|W)\d\d?)|((BF|BR|BX|FY|HA|HD|HG|HR|HS|HX|JE|LD|SM|SR|WC|WN|ZE)\d)|((AB|LL|SO)\d\d)|((AL|BA|BB|BD|BH|BL|BN|BS|BT|CA|CB|CF|CH|CM|CO|CR|CT|CV|CW|DA|DD|DE|DG|DH|DL|DN|DT|DY|EC|EH|EN|EX|FK|GL|GU|GY|HP|HU|IG|IM|IP|IV|KA|KT|KW|KY|LA|LE|LN|LS|LU|ME|MK|ML|NE|NG|NN|NP|NR|NW|OL|OX|PA|PE|PH|PL|PO|PR|RG|RH|RM|SA|SE|SG|SK|SL|SN|SP|SS|ST|SW|SY|TA|TD|TF|TN|TQ|TR|TS|TW|UB|WA|WD|WF|WR|WS|WV|YO)\d\d?)|((EC1|EC2|EC3|EC4|SW1|W1|WC1|WC2|E1|N1|NW1|SE1)(A|B|C|D|E|F|G|H|J|K|M|N|P|R|S|T|U|V|W|X|Y)))\s?\d(A|B|D|E|F|G|H|J|L|N|P|Q|R|S|T|U|W|X|Y|Z)(A|B|D|E|F|G|H|J|L|N|P|Q|R|S|T|U|W|X|Y|Z)

Determine What UK Post Codes Start With (used as filter for purchasing):

IIF(SUBSTRING('#postcode#',2,1)IN('0','1','2','3','4','5','6','7','8','9',''),SUBSTRING('#postcode#',1,1),SUBSTRING('#postcode#',1,2))

Example prefix of UK Post Codes:

AB AL B BA BB BD BF BH BL BN BR BS BT BX CA CB CF CH CM CO CR CT CV CW DA DD DE DG DH DL DN DT DY E EC EH EN EX FK FY G GL GU GY HA HD HG HP HR HS HU HX IG IM IP IV JE KA KT KW KY L (GIR) LA LD LE LL LN LS LU M ME MK ML N NE NG NN NP NR NW OL OX PA PE PH PL PO PR RG RH RM S SA SE SG SK SL SM SN SO SP SR SS ST SW SY TA TD TF TN TQ TR TS TW UB W WA WC WD WF WN WR WS WV YO ZE

The use case here is that they buyer only purchases in certain post codes. Filter= Custom Field Operator=List Contains

UK phone validation - based on valid phone Area code and #pri_phone# as the field:

for added functionality you could validate for the phone number lenght to be 10 or 11. with most of them being 11.

IIF(SUBSTRING('#pri_phone#',1,3)IN('020','023','024','028','029','070','074','075','076','077','078','079'),'TRUE',IIF(SUBSTRING('#pri_phone#',1,4)IN('0113','0114','0115','0116','0117','0118','0121','0131','0141','0151','0161','0191'),'TRUE',IIF(SUBSTRING('#pri_phone#',1,5)IN('01200','01202','01204','01205','01206','01207','01208','01209','01223','01224','01225','01226','01227','01228','01229','01233','01234','01235','01236','01237','01239','01241','01242','01243','01244','01245','01246','01248','01249','01250','01252','01253','01254','01255','01256','01257','01258','01259','01260','01261','01262','01263','01264','01267','01268','01269','01270','01271','01273','01274','01275','01276','01277','01278','01279','01280','01282','01283','01284','01285','01286','01287','01288','01289','01290','01291','01292','01293','01294','01295','01296','01297','01298','01299','01300','01301','01302','01303','01304','01305','01306','01307','01308','01309','01320','01322','01323','01324','01325','01326','01327','01328','01329','01330','01332','01333','01334','01335','01337','01339','01340','01341','01342','01343','01344','01346','01347','01348','01349','01350','01352','01353','01354','01355','01356','01357','01358','01359','01360','01361','01362','01363','01364','01366','01367','01368','01369','01371','01372','01373','01375','01376','01377','01379','01380','01381','01382','01383','01384','01386','01387','01388','01389','01392','01394','01395','01397','01398','01400','01403','01404','01405','01406','01407','01408','01409','01420','01422','01423','01424','01425','01427','01428','01429','01430','01431','01432','01433','01434','01435','01436','01437','01438','01439','01440','01442','01443','01444','01445','01446','01449','01450','01451','01452','01453','01454','01455','01456','01457','01458','01460','01461','01462','01463','01464','01465','01466','01467','01469','01470','01471','01472','01473','01474','01475','01476','01477','01478','01479','01480','01481','01482','01483','01484','01485','01487','01488','01489','01490','01491','01492','01493','01494','01495','01496','01497','01499','01501','01502','01503','01505','01506','01507','01508','01509','01520','01522','01524','01525','01526','01527','01528','01529','01530','01531','01534','01535','01536','01538','01539','01540','01542','01543','01544','01545','01546','01547','01548','01549','01550','01553','01554','01555','01556','01557','01558','01559','01560','01561','01562','01563','01564','01565','01566','01567','01568','01569','01570','01571','01572','01573','01575','01576','01577','01578','01579','01580','01581','01582','01583','01584','01586','01588','01590','01591','01592','01593','01594','01595','01597','01598','01599','01600','01603','01604','01606','01608','01609','01620','01621','01622','01623','01624','01625','01626','01628','01629','01630','01631','01633','01634','01635','01636','01637','01638','01639','01641','01642','01643','01644','01646','01647','01650','01651','01652','01653','01654','01655','01656','01659','01661','01663','01664','01665','01666','01667','01668','01669','01670','01671','01672','01673','01674','01675','01676','01677','01678','01680','01681','01683','01684','01685','01686','01687','01688','01689','01690','01691','01692','01694','01695','01697','01698','01700','01702','01704','01706','01707','01708','01709','01720','01721','01722','01723','01724','01725','01726','01727','01728','01729','01730','01732','01733','01736','01737','01738','01740','01743','01744','01745','01746','01747','01748','01749','01750','01751','01752','01753','01754','01756','01757','01758','01759','01760','01761','01763','01764','01765','01766','01767','01768','01769','01770','01771','01772','01773','01775','01776','01777','01778','01779','01780','01782','01784','01785','01786','01787','01788','01789','01790','01792','01793','01794','01795','01796','01797','01798','01799','01803','01805','01806','01807','01808','01809','01821','01822','01823','01824','01825','01827','01828','01829','01830','01832','01833','01834','01835','01837','01838','01840','01841','01842','01843','01844','01845','01847','01848','01851','01852','01854','01855','01856','01857','01858','01859','01862','01863','01864','01865','01866','01869','01870','01871','01872','01873','01874','01875','01876','01877','01878','01879','01880','01882','01883','01884','01885','01886','01887','01888','01889','01890','01892','01895','01896','01899','01900','01902','01903','01904','01905','01908','01909','01920','01922','01923','01924','01925','01926','01928','01929','01931','01932','01933','01934','01935','01937','01938','01939','01942','01943','01944','01945','01946','01947','01948','01949','01950','01951','01952','01953','01954','01955','01957','01959','01962','01963','01964','01967','01968','01969','01970','01971','01972','01974','01975','01977','01978','01980','01981','01982','01983','01984','01985','01986','01987','01988','01989','01992','01993','01994','01995','01997'),'TRUE',IIF(SUBSTRING('#pri_phone#',1,6)IN('013873','015242','015394','015395','015396','016973','016974','016977','017683','017684','017687','019467'),'TRUE','FALSE'))))

Others/Misc.

Removing The 1 From A Phone Number
Number to US Currency 999999 to 0
Bank Routing Number/ABA Validation (on "#aba#")
Convert Lower Case State to CAPS
SQL92 Errors
Date Field Without The Time Stamp
Loan To Value (LTV):
To Deal With 1 (or more) Numbers On Your Denominator That Will Attempt to Make Us Divide By 0

To remove the 1 from a phone number.

SUBSTRING('#fieldname#',2,10)

This will skip the first number being passed in (1), and will count the next 10 digits.

Turns: 19495482253 into 9495482253

Number to US currency 999999 to 0:

Example: 1500 to $1,500

Three fields

Bank Routing Number/ABA Validation (on "#aba#") - Checks the checksum as well as makes sure the String is not all "0"s and is of LEN=9:

http://en.wikipedia.org/wiki/Routing_transit_number

IIF((((3*CONVERT(SUBSTRING('#aba#',1,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',4,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',7,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',2,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',5,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',8,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',3,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',6,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',9,1),'System.Int32')))%10=0) AND (((3*CONVERT(SUBSTRING('#aba#',1,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',4,1),'System.Int32'))+(3*CONVERT(SUBSTRING('#aba#',7,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',2,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',5,1),'System.Int32'))+(7*CONVERT(SUBSTRING('#aba#',8,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',3,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',6,1),'System.Int32'))+(1*CONVERT(SUBSTRING('#aba#',9,1),'System.Int32')))<>0) AND LEN('#aba#')=9,TRUE,FALSE)

Convert Lower Case State to CAPS

Description: Most likely will not need this because most people know that all states are CAPS. But just in case.

IIF('#employer_state#'='al','AL',IIF('#employer_state#'='ak','AK',IIF('#employer_state#'='az','AZ',IIF('#employer_state#'='ar','AR',IIF('#employer_state#'='ca','CA',IIF('#employer_state#'='co','CO',IIF('#employer_state#'='ct','CT',IIF('#employer_state#'='de','DE',IIF('#employer_state#'='dc','DC',IIF('#employer_state#'='fl','FL',IIF('#employer_state#'='ga','GA',IIF('#employer_state#'='hi','HI',IIF('#employer_state#'='id','ID',IIF('#employer_state#'='il','IL',IIF('#employer_state#'='in','IN',IIF('#employer_state#'='ia','IA',IIF('#employer_state#'='ks','KS',IIF('#employer_state#'='ky','KY',IIF('#employer_state#'='la','LA',IIF('#employer_state#'='me','ME',IIF('#employer_state#'='md','MD',IIF('#employer_state#'='ma','MA',IIF('#employer_state#'='mi','MI',IIF('#employer_state#'='mn','MN',IIF('#employer_state#'='ms','MS',IIF('#employer_state#'='mo','MO',IIF('#employer_state#'='mt','MT',IIF('#employer_state#'=' ne',' NE',IIF('#employer_state#'='nv','NV',IIF('#employer_state#'='nh','NH',IIF('#employer_state#'='nj','NJ',IIF('#employer_state#'='nm','NM',IIF('#employer_state#'='ny','NY',IIF('#employer_state#'='nc','NC',IIF('#employer_state#'='nd','ND',IIF('#employer_state#'='oh','OH',IIF('#employer_state#'='ok','OK',IIF('#employer_state#'='or','OR',IIF('#employer_state#'='pa','PA',IIF('#employer_state#'='ri','RI',IIF('#employer_state#'='sc','SC',IIF('#employer_state#'='sd','SD',IIF('#employer_state#'='tn','TN',IIF('#employer_state#'='tx','TX',IIF('#employer_state#'='ut','UT',IIF('#employer_state#'='vt','VT',IIF('#employer_state#'='va','VA',IIF('#employer_state#'='wa','WA',IIF('#employer_state#'='wv','WV',IIF('#employer_state#'='wi','WI',IIF('#employer_state#'='wy','WY','')))))))))))))))))))))))))))))))))))))))))))))))))))

SQL92 Errors

If a field is null and we do a substring on it, the system will break if we substring starting at the #1 character. CONVERT(SUBSTRING('#DateOfBirth#',1,2),'System.Int32') will not work if #DateOfBirth# is null. This is the fix CONVERT(SUBSTRING(IIF('#DateOfBirth#'='','00','#DateOfBirth#'),1,2),'System.Int32')

Date field without the time stamp:

Example: SUBSTRING('#any_date_field#',1,10)

To read this, the 1 is indicating which position to start the substring. The 10 is the number of characters to return.

Mm/dd/yyyy=10 characters.

Loan To Value (LTV):

Formula: #mortgage_balance#/#home_value#*100

Fields: mortgage_balance:130000|home_value:150000

Formula: 130000/150000*100

Result : 86.6666666666667

To deal with 1 (or more) numbers on your Denominator that will attempt to make us Divide by 0:

We want to set up a Formula that is (#Revenue#-#Cost#)/#Cost#. Because #Cost# could be 0, we get #Revenue#/0, which is impossible to calculate (ROI=0 for no initial Investment, or infinity for 100% Profit, your choice).

IIF((#Revenue#)=(#Revenue#/(#Cost#+1)),0,#Revenue#/(#Cost#+(1-IIF(#Cost#=0,0,1))))

Assign an integer based on state (map each state to a number):
IIF('#state#'='CA','13',IIF('#state#'='AZ',’2’,IIF(‘#state#’=‘PA’,’3’,...'’))) *(remember to close out with an else option such as: ,'ERROR'

In this article, you will find several Custom Field Examples for reference.

Operators, Functions, and Wildcard CharactersConverting Date Formats Calculating Based Off of FieldsUK Custom Field ExamplesOthers/Misc.

Operators

Functions

Wildcard Characters

Related Articles

Operators, Functions, and Wildcard Characters
Converting Date Formats

Calculating Based Off of Fields
UK Custom Field Examples
Others/Misc.