Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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:

Code Block
languagec#
(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


Code Block
languagec#
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.

...