# Page History

## Key

• This line was removed.
• Formatting was changed.

...

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_end#) >= #To# ? 1 : 0 ) :
#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).
```
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
language c#
```(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
)

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
language c#
```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 ( DON'T take the TO-year):

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

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

...