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

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)

See also MSDN's DateTime