Formula operator

Date and Time

Text

General Mathematics

Conversion and Formatting

Extras

XIH Helper Functions

Example: Add number of hours to a Date/Time column

Situation: 

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



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

  1. Argument in substring is the start (Attention: 0 = first character/digit),
  2. 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]