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#.Add(#From#.TimeOfDay) ...adds date and time and results in
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
| ||||||||||||||||||||||||
Retention period - incl. the whole last day | (#To#.Date - #From#.Date).Days + 1 | ||||||||||||||||||||||||
Different time spans as ticks |
... | ||||||||||||||||||||||||
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
| ||||||||||||||||||||||||
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: #Date#.AddHours(#Code# / 10).AddMinutes(((#Code# % 10) - 1) * 15)
| ||||||||||||||||||||||||
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 : 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: | ||||||||||||||||||||||||
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
(#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". | ||||||||||||||||||||||||
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):
Adaptation (which is the first day of the week?):
| ||||||||||||||||||||||||
What is date of Monday of the specified calendar week? What is the beginning of the specified calendar week? | Sample input data:
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) |
See also MSDN's DateTime