Formula operator: Date and Time

Formula operator	Date and Time	Text	General Mathematics	Conversion and Formatting	Extras	XIH Helper Functions

Date and Time

First Examples

Situation	Formula	Result
The column 'From' contains date and time. You want the date only without specific time.	#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	only time
#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 with formatting format settings see Grundbegriffe:Formate
System.DateTime.Now	date/time	current date and time
System.DateTime.DaysInMonth(y,m)	Integer	How many days does month m have 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)

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

...

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

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

Sort a sequence of abbreviances 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): 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:

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

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)