Page tree
Skip to end of metadata
Go to start of metadata

Introductory examples

More examples at end of this page.

SituationFormulaResult
The column 'From' contains date and time. You want to extract the date part without the time part.
#From#.Date
e.g. 2008/05/02 06:00:00 becomes 2008/05/02 00:00:00
Calculate length of a time interval in minutes (eg., of a shift).
(#To# - #From#).TotalMinutes
e.g. from 1.1.2006 8:00:00 to 1.1.2006 14:00 results in 360.0 minutes

Built-in C# functions

Funktion/PropertyReturn valueDescription
#name#.Date
date/timedate without time
#name#.TimeOfDay
date/timetime without date
#name#.Day
Integerday of the month
#name#.DayOfWeek
Integer/Stringnumber of / name of the day of the week
#name#.DayOfYear
Integernumber of the day of the year
#name#.Hour
Integerhour of the day
#name#.Minute
Integerminute of the hour
#name#.Month
Integermonth
#name#.Year
Integeryear
#name#.AddYears(n)
date/timeadds a number of years to a date/time
#name#.AddMonths(n)
date/timeadds a number of months to a date/time
#name#.AddDays(n)
date/timeadds a number of days to a date/time
#name#.AddHours(n)
date/timeadds a number of hours to a date/time
#name#.AddMinutes(n)
date/timeadds a number of minutes to a date/time
#name#.AddSeconds(n)
date/timeadds a number of seconds to a date/time
#name#.ToShortDateString()
Stringconversion of a date/time object into a string (date only)
#name#.ToString()
Stringconversion of a date/time object into a string
#name#.ToString(f)
String

conversion of a date/time object into a string formatted as specified in f

(formats see Formatting strings)

System.DateTime.Now
date/time

current date and time

(warning) it includes fractions of a second, see below how to truncate

System.DateTime.DaysInMonth(y,m)
IntegerNumber of days in month m in year y
System.DateTime.IsLeapYear(y)
Yes/No-valueIs year y a leap year?
System.DateTime.Parse(s)
date/timeConversion of a string s into a date/time object
System.DateTime.TimeOfDay
Texttime of day in format HH:mm:ss
(#To#-#From#).TotalSeconds
Number with decimal placeslength of time interval in seconds (incl. decimal places)
(#To#-#From#).TotalMinutes
Number with decimal placeslength of time interval in minutes (incl. decimal places)
(#To#-#From#).TotalHours
Number with decimal placeslength of time interval in hours (incl. decimal places)
(#To#-#From#).TotalDays
Number with decimal placeslength of time interval in days (incl. decimal places)

Examples

TaskCode
Add date and time from two cells

Sample input table:

DateFromTo
13/1/201513:0014:00


#Date#.Add(#From#.TimeOfDay)

...adds date and time and results in

DateFromToFromDate
13/1/201513:0014:0013/1/2015 13:00


For the To-Date it may be necessary to add an additional day:

#Date#.AddDays(#From# > #To#  ? 1 : 0)

...add day if from > to

or together:

#Date#.Add(#To#.TimeOfDay).AddDays(#From# > #To#  ? 1 : 0)

results in

DateFromToFromDateToDate
13/1/201522:0007:0013/1/2015 22:0014/1/2015 07:00


Retention period - incl. the whole last day
(#To#.Date - #From#.Date).Days + 1
Different time spans as ticks
  • System.TimeSpan.TicksPerSecond
  • System.TimeSpan.TicksPerMinute
  • System.TimeSpan.TicksPerHour

...

Truncate the fractions of a second
new System.DateTime(
(long)System.Math.Floor(
(double)System.DateTime.Now.Ticks / System.TimeSpan.TicksPerSecond
) * System.TimeSpan.TicksPerSecond)

Difference in minutes

(#From# - #To#).Minute +
(#From# - #To).Hours*60 +
(#From# - #To#).Day*60*24

or

(#From# - #To#).Ticks/600000000

or to allow also negative values

(#To#.Ticks - #From#.Ticks)/600000000

Note: 1 tick = 100 nano seconds, therefore 1 minute = 600 000 000 nano seconds (=milli-seconds/micro-seconds/10)

Check if a time period is a night shift (from < to)
(#From#.TimeOfDay.CompareTo(#To#.TimeOfDay) == 1 ? "T" : "N")
Check if a time point comes after another time point
(#From#.CompareTo(#To#) == 1 ? 1:0)
Overwrite a fixed time for each day and return it as a DATE (regardless the original time)

Given #From# is 13/1/2015 12:13

#From#.Date.AddHours(16).AddMinutes( 15) returns 13/1/2015 16:15

Remove the time for each day (resulting 00:00)
#From#.Date
Recalculate coded time intervals into minutes and add them to a date

(eg, Code 1 is for 0:00-0:15, 2 for 0:15-0:30, ... 4 for 0:45-1:00, 11 for 1:00-1:15, ... 14 for 1:45-2:00, ... 72 for 7:15-7:30)

in this example #Code# encodes the quarter of an hour by numbers 1..4 in the unit position, anything higher including decades encode the number of hours:
HHQ where
HH is number of hours
Q is 1st to 4th quarter of an hour

#Date#.AddHours(#Code# / 10).AddMinutes(((#Code# % 10) - 1) * 15)

#Code# % 10 ... calculates the residue class modulo 10, in other words the unit position

Check if a time interval falls into a certain time window (time window start - time window end)


other cases???

#time_window_start# <=#time_window_end# ? 
   (#From#.Date.AddHours(#time_window_start#) <= #From# && 
    #From#.Date.AddHours(#time_window_end#) >= #To# ? 1 : 0 ) :
   (#From#.Date.AddHours(#time_window_start#) <= #From# && 
    #From#.Date.AddHours(#time_window_end# + 24) >= #To# ? 1 : 0 )
http://stackoverflow.com/questions/13513932/algorithm-to-detect-overlapping-periods

Check if a time interval reaches into a specific time window. The variables "Search From in h" and "Search To in h" can be created in the formula operator.



... so übernommen aus englischem Wiki - kann das sein??

#Search From in h# >= #Search To in h# ? -1 :
#Search To in h# <= #From#.Hour + #From#.Minute/60.0 ?
(#From#.Date < #To#.Date && #Search From in h# < #From#.Hour + #To#.Minute/60.0 ? 1 : 0) :
(#From#.Date < #To#.Date && #Search From in h# < #Search To in h# ? 1 :
(#Search From in h# < #To#.Hour + #To#.Minute/60.0 ? 1 : 0 )))
 

 E.g. 2008/01/01 6:00:00    2008/01/01 in 19:00:00  in relation to 18,5 and 19 equals 1

https://de.wikipedia.org/wiki/Allen-Kalk%C3%BCl

Set everything to the first day of the data record (and keep the time of day)
#From#.AddDays(-(#From#.Date - System.Convert.ToDateTime(dataRow.Table.Rows[0][0]).Date).Days)
Attention: Decimal places of Date/Time functionsIf decimal places are to be expected in the date/time functions (eg., long or integer), a cast (compiler command for "type conversion") must be applied to any Floating-Point-Type, ideally double, eg:
1 + (double)#PresenceOfAnaesthesist Double# /
(#End Anaesthesist# - #Beginning Anaesthesist#).TotalMinutes

Merging of separated date (read in as date) and time (read in as clock time)

Merging:

System.Convert.ToString(#Date# ).Substring(0,10) +" " +  
System.Convert.ToString(#From#).Substring(10,8)

If change over midnight: #From raw#.AddDays(#To raw# < #From# ? 1 : 0)

Introduce a virtual week to compare values with each other

Create an auxiliary value for From: eg #from aux#, type: date. NOTE: Get week day with General calendar (old: allgemeinem Kalender), so that each day gets a week day number

System.DateTime.ParseExact("20080225","yyyyMMdd",null).
AddDays(System.Convert.ToDouble(#weekday#.Substring(0,1))-1).
AddHours(#From#.Hour).
AddMinutes(#From#.Minute).
AddSeconds(#From#.Second)
Enter a specific datenew DateTime(2021, 12, 25)
Parse (convert) string to date

Example 1: Date format yyyyMMddHHmmss WITHOUT punctuation mark: for conversion from e.g. 20120101090000 to 2012/01/01 09:00:00:

20120101090000 System.DateTime.ParseExact(#ColumnName#,"yyyyMMddHHmmss",null) 

Or without seconds:

201201010900 System.DateTime.ParseExact(#ColumnName#,"yyyyMMddHHmm",null) withoutSeconds  

Example 2: Date format yyyyMMdd HH:mm:ss

System.DateTime.ParseExact("20120101 18:00:00","yyyyMMdd HH:mm:ss",null)

Note: "HH" means 24h, "hh" means 12h

Date of day in SPX Format

SPX Days calculates with days since 1990/01/01:

(#From# - System.DateTime.ParseExact("19900101","yyyyMMdd",null)).Days In days


Alternatively with minutes:

(#From# - System.DateTime.ParseExact("19900101","yyyyMMdd",null)).Ticks/600000000 In minutes
Short Names of Weekdays

    ((int)#Von#.DayOfWeek == 1) ? "1 Mo." :
    ((int)#Von#.DayOfWeek == 2) ? "2 Di." :
    ((int)#Von#.DayOfWeek == 3) ? "3 Mi." :
    ((int)#Von#.DayOfWeek == 4) ? "4 Do." :
    ((int)#Von#.DayOfWeek == 5) ? "5 Fr." :
    ((int)#Von#.DayOfWeek == 6) ? "6 Sa." : "7 So."


Get the Monday of this week 00:00 ... Begin of weekSystem.DateTime.Now.Date.AddDays(
    (int) System.DateTime.Now.Date.DayOfWeek == 0 ? - 6 : 1 -  (int) System.DateTime.Now.Date.DayOfWeek)
Sort a sequence of abbreviation for weekdays

Mon, Tue, Wed ... etc. come in an „unlogical order” if sorted as text (=in alphabetical order) With the following formula, the corresponding number is placed in front of the abbreviation:

(#WeekDay# == "Mon" ? "1 Mon" :
(#WeekDay# == "Tue" ? "2 Tue" :
(#WeekDay# == "Wed" ? "3 Wed" :
(#WeekDay# == "Thu" ? "4 Thu" :
(#WeekDay# == "Fri" ? "5 Fri" :
(#WeekDay# == "Sat" ? "6 Sat" : "7 Sun" ))))))

Or if the number is taken out of the calendar:

.Replace(";Sunday"," Sun")
.Replace(";Saturday"," Sat")
.Replace(";Friday"," Fri")
.Replace(";Thursday"," Thu")
.Replace(";Wednesday"," Wed")
.Replace(";Tuesday"," Tue")
.Replace(";Monday"," Mon")
Identify even and odd numbered weeksThe following formula expects a week identifier (as text) in the following manner in #week#:

2006, 22

the formula takes the number after digit 6 (attention: this means 7 digits here since the counting in the text starts with 0 (zero)).

HERE it might be necessary to adjust the formula.

  (System.Math.Ceiling(System.Convert.ToDouble(#Week#.Substring(6,2))/2)*2 == 
   System.Convert.ToDouble(#week#.Substring  (6,2)) ? "even" : "odd")

Outputs "even" : "odd".
If only the week number was available (e.g., 22), then #week#.Substring(0,2) would need to be changed in BOTH places.

Calendar week (KW): How can I calculate the KW based on a date?Normally, the Operator General calendar would be used.
new System.Globalization.CultureInfo("de-AT").Calendar.GetWeekOfYear
 (#Date#,System.Globalization.CalendarWeekRule.FirstFourDayWeek,DayOfWeek.Monday);

Adaptation (how to calculate the KW):

  • CalendarWeekRule.FirstDay
  • CalendarWeekRule.FirstFullWeek
  • CalendarWeekRule.FirstFourDayWeek
  • exact documentation

Adaptation (which is the first day of the week?):

  • DayOfWeek.Sunday
  • DayOfWeek.Monday
  • DayOfWeek.Tuesday
  • ...

What is date of Monday of the specified calendar week?

What is the beginning of the specified calendar week?

Sample input data:

YearCalendar Week
20185
201925


Using the ISO calendar week definition https://en.wikipedia.org/wiki/Week#Week_numbering:

(new System.DateTime(#Year#,1,4).AddDays(-(int)(
( (int) new System.DateTime(#Year#,1,4).DayOfWeek != 0) 
? 
  (int) new System.DateTime(#Year#,1,4).DayOfWeek - 1 
: 
  6
)
).AddDays((#Calendar Week#-1) * 7))

Alternative use framework funcationality, which requires the declare following data types in the TIServiceWindowsService.exe.config

  • System.Globalization.CalendarWeekRule
  • System.Globalization.GregorianCalendar
  • System.DayOfWeek


new System.Globalization.GregorianCalendar().GetWeekOfYear(
  System.DateTime.Now, 
  System.Globalization.CalendarWeekRule.FirstFourDayWeek, 
  System.DayOfWeek.Monday)
Calculate the value for January 2010 from the values of January 08 and January 09

1. Add as many years to the FROM-column as lie between the FROM-year and 2010:

   #From#.AddYears(2010-#From#.Year)

2. Add as many years to the TO-column as lie between 2010 and the FROM -year ((warning) DON'T take the TO-year):

   #To#.AddYears(2010-#From#.Year)

3. Sum across calendar months and divide the results by 2.

For very advanced users

TaskCode
Calculate the number of years between FROM and TO while considering leap years
#To#.Year - #From#.Year - 1 +
(System.DateTime.ParseExact(System.Convert.ToString(#From#.Year + 1) +"0101","yyyyMMdd",null) - #From#).Days/
(System.DateTime.IsLeapYear(#From#.Year) && #From# <= System.DateTime.ParseExact(System.Convert.ToString(#From#.Year) +"0301","yyyyMMdd",null)? 366.0 : 365.0) + 
(#To#- System.DateTime.ParseExact(System.Convert.ToString(#To#.Year) +"0101","yyyyMMdd",null)).Days/
(System.DateTime.IsLeapYear(#To#.Year)  && #To# >= System.DateTime.ParseExact(System.Convert.ToString(#To#.Year) +"0301","yyyyMMdd",null)? 366.0 : 365.0)
Build time categories as text - e.g. 06:00 - 08:00, 08:00 - 10:00
(System.Math.Floor(#From#.Hour / #time_category_length_in_h#)*#time_category_length_in_h#).ToString("00") + ":" +
(System.Math.Floor(#From#.Minute / (#time_category_length_in_h# * 60 ))*#time_category_length_in_h# * 60).ToString("00") + " - " +
(System.Math.Floor(#From#.AddHours(#time_category_length_in_h#).Hour / #time_category_length_in_h#)*#time_category_length_in_h#).ToString("00") + ":" +
(System.Math.Floor(#From#.AddHours(#time_category_length_in_h#).Minute / (#time_category_length_in_h# * 60 ))*#time_category_length_in_h# * 60).ToString("00")
Scaling in 5'
#From#.AddMinutes(-#From#.Minute).AddMinutes( 5.0 * (double) System.Math.Floor(#From#.Minute / 5.0 )).AddSeconds(-#From#.Second)

See also MSDN's DateTime