Formula operator: Conversion and Formatting

Formula operator	Date and Time	Text	General Mathematics	Conversion and Formatting	Extras	XIH Helper Functions

Conversion

First Example: Simple type conversion

A simple type conversion is done by selecting the desired type for result value.

Situation	Code / Procedure	Result
text "07"	If e.g. 07 is entered into the formula editor as text and only the result is converted to number, the result will be the number 7. Quite practical.	number "7"

Second Example: Add number of hours in one column to a date without hours in another column

Given following input table:

A	B
01.05.2006 00:00:00	07:00

How to get the number of hours in the text "07:00" in column B?

System.Convert.ToInt32(#B#.Substring(0,2))

A	B	Result
01.05.2006 00:00:00	07:00	7

How add the hours of column B to column A?

#A#.AddHours(#Result#)

A	B	Result
01.05.2006 07:00:00	07:00	7

Functions

Please note:

C# is case sensitive. Be careful with upper and lower case letters!
The decimal point for System.Convert.ToDouble() is set either to point '.' or comma ','.
Thus, it can happen that the string '0,5' is by mistake converted to 5.
Therefore, in order to convert text or strings to numbers with decimal places, please use the operation Conversion "String -> Double"

Function/Property	Return value	Description
System.Convert.ToDouble(o)	Number with decimal places	Converts a value or string into a number with decimal places.
System.Convert.ToDouble(o)	Number with decimal places	If possible use Conversion "String -> Double"
System.Convert.ToInt32(o)	Integer	Converts a value or value string into an integer.
System.Convert.ToString(o)	Text	Converts a value or value string into a text string.

More Examples

Situation	Code
Convert string "01-JAN-08" into a date	First, check if the "culture" is correct. E.g., in Austria "JÄN", in Germany "JAN", in America "OCT" etc. If this is correct: System.DateTime.ParseExact(#columnname#,"dd-MMM-yy",null) ... e.g. german date format in german TIS If the culture does not fit then a conversion is possible: System.DateTime.ParseExact(#DepDate#,"dd-MMM-yy",new System.Globalization.CultureInfo("en-US")) different cultures: "de-AT", "de-DE", "de-CH", "en-GB", "en-US", "en-AU" ... This allows to avert for example the popular topics Jänner, Januar ... Please note: During import it is possible that for some months the import works and for some months it doesn't work. The culture cannot be changed in the import operation. Therefore, import as text and continue as described above.
Convert string into date or time	Date format yyyyMMddHHmmss WITHOUT separators: To convert from e.g. 20060101090000 to 01.01.2006 09:00:00 with the formula operator (row-by-row): `20060101090000 System.DateTime.ParseExact(#columnname#,"yyyyMMddHHmmss",null) 200601010900 System.DateTime.ParseExact(#columnname#,"yyyyMMddHHmm",null)` ... without seconds ATTENTION Argument in substring is the start (Attention: 0 = first character/digit), Argument is the amount of characters with `System.DateTime.Parse(#columnname#)`
	Alternatively first use the formula operator (row-by-row): #FromTime#.Substring(6,2) + "." + #FromTime#.Substring(4,2) + "." + #FromTime#.Substring(0,4) + " " + #FromTime#.Substring(8,2) + "." + #FromTime#.Substring(10,2) + ":" + #FromTime#.Substring(12,2) + ":" +
Add a number of hours as a string in #B# to date #A#	#A#.AddHours(System.Convert.ToInt32(#B#.Substring(0,2)) ...
Convert a number into a certain text format	#Number#.ToString("000") ... converts exactly into a 3-digit value More complex examples incl. the distinction between positive and negative (similar to EXCEL "$#,##0.00;($#,##0.00);Zero") \n could produce a line break in C# but this is not allowed in the formula editor.
Convert a number format for a time (hour) into a time of day format	Example: If a column #From time (hours)# contains a value for an hour, e.g. 17.5, then convert to the value "17:30". System.Math.Floor(#From time (hours)#).ToString("00") + ":" + ((#From time (hours)# - System.Math.Floor(#From time (hours)#))*60.0).ToString("00")
Convert a numeric date value (with offset start date as the base) into a date format	For example: System.Convert.ToDateTime("01.01.1970").AddSeconds(#duration#) With the operation formula editor such date fields can be converted. Enter the following command in the formula editor: new System.DateTime( System.TimeSpan.FromTicks(new System.DateTime(1900, 1, 1).Ticks + ((long)#InputValue# * 0xc92a69c000)).Ticks Brief explanation: Constant 0xc92a69c000 = Number of ticks per day #InputValue#: Here you have to enter the column with the number of days. The # need to remain in the command. Why the type conversion (long) (other name for type conversion: cast)?: If the column with the day-value is not a long-column, it has to be casted to long, because the function requires a long-parameter.
Calculating with Ticks.	A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond, or 10 million ticks in a second. Example: System.DateTime(System.TimeSpan.FromTicks(new System.DateTime(1900, 1, 1).Ticks + ((long)#A# * 0xc92a69c000)).Ticks constant 0xc92a69c000 = number of ticks a day #A#: column with number of days type cast to long is necessary if the type of this column is not long. This example can be done much more simple: System.DateTime(new System.DateTime(1900, 1, 1).AddDays(#A#)).Ticks
Add a (almost) military time ("1" = 00:00, "30" = 00:30, ... "200" = 02:00, ...) to a date	#From time# == 1 ? #Date#.AddHours(0) : #Date#.AddMinutes((60* (int)(#From time#/100))+System.Math.IEEERemainder(#From time#,100))
How can I split a string consisting of various parts with different lengths after each punctuation mark?	First Part, delimiter is ',': #Column#.Split(new char[]{','})[0] Second Part, delimiter is ',': #Column#.Split(new char[]{','})[1] Third Part, delimiter is ',': #Column#.Split(new char[]{','})[2] Fourth Part, delimiter is ',': #Column#.Split(new char[]{','})[3]