Formula Operator: Date and Time

Introductory examples

More examples at end of this page.

Situation	Formula	Result
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/Property	Return value	Description
#name#.Date	date/time	date without time
#name#.TimeOfDay	date/time	time without date
#name#.Day	Integer	day of the month
#name#.DayOfWeek	Integer/String	number of / name of the day of the week
#name#.DayOfYear	Integer	number of the day of the year
#name#.Hour	Integer	hour of the day
#name#.Minute	Integer	minute of the hour
#name#.Month	Integer	month
#name#.Year	Integer	year
#name#.AddYears(n)	date/time	adds a number of years to a date/time
#name#.AddMonths(n)	date/time	adds a number of months to a date/time
#name#.AddDays(n)	date/time	adds a number of days to a date/time
#name#.AddHours(n)	date/time	adds a number of hours to a date/time
#name#.AddMinutes(n)	date/time	adds a number of minutes to a date/time
#name#.AddSeconds(n)	date/time	adds a number of seconds to a date/time
#name#.ToShortDateString()	String	conversion of a date/time object into a string (date only)
#name#.ToString()	String	conversion 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 it includes fractions of a second, see below how to truncate
System.DateTime.DaysInMonth(y,m)	Integer	Number of days in month `m` in year `y`
System.DateTime.IsLeapYear(y)	Yes/No-value	Is year `y` a leap year?
System.DateTime.Parse(s)	date/time	Conversion of a string s into a date/time object
System.DateTime.TimeOfDay	Text	time of day in format HH:mm:ss
(#To#-#From#).TotalSeconds	Number with decimal places	length of time interval in seconds (incl. decimal places)
(#To#-#From#).TotalMinutes	Number with decimal places	length of time interval in minutes (incl. decimal places)
(#To#-#From#).TotalHours	Number with decimal places	length of time interval in hours (incl. decimal places)
(#To#-#From#).TotalDays	Number with decimal places	length of time interval in days (incl. decimal places)

Examples

Task

Code

Add date and time from two cells

Sample input table:

Date	From	To
13/1/2015	13:00	14:00

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

...adds date and time and results in

Date	From	To	FromDate
13/1/2015	13:00	14:00	13/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

Date	From	To	FromDate	ToDate
13/1/2015	22:00	07:00	13/1/2015 22:00	14/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)

#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.

#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

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 functions

If 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 date

new System.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 week

System.DateTime.Now.Date.AddDays(
(int) System.DateTime.Now.Date.DayOfWeek == 0 ? - 6 : 1 - (int) System.DateTime.Now.Date.DayOfWeek)

Transform times for Time Grupping (Zeiten gruppieren)
to work with Engl & German to convert to Mo-So

XIH.ColumnIndex("Bis Hilf") < 0 ?
    XIH.SetDateTime("Until help", XIH.GetDateTime("Until help").AddDays(((int) XIH.GetDateTime("From help" ).DayOfWeek) == 0 ? 7 : 0 )) &&
    XIH.SetDateTime("From help", XIH.GetDateTime("From help" ).AddDays(((int) XIH.GetDateTime("From help" ).DayOfWeek) == 0 ? 7 : 0 ))
:
    XIH.SetDateTime("Bis Hilf", XIH.GetDateTime("Bis Hilf")) &&
    XIH.SetDateTime("Von Hilf", XIH.GetDateTime("Von Hilf"))

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 weeks

The 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, Calendarweek):
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
 (#HERE YOUR DATE#, System.Globalization.CalendarWeekRule.FirstFourDayWeek, System.DayOfWeek.Monday)

It may be necessary to declare the following data types in the TIServiceWindowsService.exe.config file:

System.Globalization.CultureInfo
System.Globalization.CalendarWeekRule
System.DayOfWeek

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:

Year	Calendar Week
2018	5
2019	25

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 ( 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

Task	Code
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)
Getting a Date that has the correct workday	#Eingabe# == "Mo" ? (new System.DateTime(2022, 4, 11)) : #Eingabe# == "Di" ? (new System.DateTime(2022, 4, 12)) : #Eingabe# == "Mi" ? (new System.DateTime(2022, 4, 13)) : #Eingabe# == "Do" ? (new System.DateTime(2022, 4, 14)) : #Eingabe# == "Fr" ? (new System.DateTime(2022, 4, 15)) : #Eingabe# == "Sa" ? (new System.DateTime(2022, 4, 16)) : #Eingabe# == "So" ? (new System.DateTime(2022, 4, 17)) :(new System.DateTime(2022, 4, 11))