This topic explains how to create a formula to calculate a date that is earlier or later than another set date, or a time period based on two dates.
For example, to calculate a previous or later date consider the following form (see figure below) in which a start date for an activity and a number of days for it to expire are defined. The value of the third field is calculated based on the values of the two previous fields.
In this example, the Start Date field is a Date/Time attribute and the Expiration (Days) field is a Number attribute. The Expiration Date field is a Formula attribute with Date/Time output, which is based on the values provided for the two previous fields, following the DateTime structure below.
#DateAdjustment = ((@start_date==null) ? DateTime.Today : @start_date);
#expiration_date = ((@expiration_days>0) ? #DateAdjustment.AddDays(@expiration_days) : #DateAdjustment);
#expiration_date
In this case, the number of days entered in the Expiration (Days) field will be added to the date specified in the Start Date field. This means that if a negative value is provided for this field, the formula will calculate a past date. If no date is set in the Start Date field, the current date will be considered. If the Expiration (Days) field is blank, the value from the Start Date field will be returned.
To calculate a time period, consider the example below.
In this example, the Expected Start Date and Expected End Date are Date/Time attributes with the option to display only a selected date. Likewise, the Expected Start Time and Expected End Time fields are also Date/Time attributes, but with the option to only display a selected time transformed in TimeSpan. The Expected Duration field is a Formula attribute with a text output that is based on the values of the four previous fields and that follows the structure below. Note that for the result of formula to be displayed in hours, the ToString function must be used to transform the value of the attribute into a text with the time format, as displayed in the last line of the formula.
#TotalDate = (@ExpectedEndDate - @ExpectedStartDate);
#TotalTime = (@ExpectedEndTime - @ExpectedStartTime);
#total = #TotalDate.Add(#TotalTime);
T(System.Math).Truncate(#total.TotalHours).ToString('00') + #total.ToString('\\:mm')
Note: For more information on the DateTime structure and its functions, please visit https://msdn.microsoft.com/library/system.datetime(v=vs.110).aspx.