Date Calculations Using the Expression Builder
Date/Time/Duration formats
There are 5 formats basic of dates, times and durations for Input Box widgets and Date Picker widgets that users can choose from. You can customize the format of dates and times by clicking on the Format button next to Data Type.
- Date – Specifies a year, month and day in the default format YYYY-MM-DD. For example, 2007-06-16 corresponds to June 16, 2007.
- Time – Specifies an hour, minute and second in the default format hh:mm:ss of a 24 hour clock. For example, 13:30:45 corresponds to 1:30:45pm.
- Date/time – Specifies a year, month, day, hour, minute and second in the default format of YYYY-MM-DDThh-mm-ss. The “T” represents the start of the time portion of a Date/time. For example, 2007-6-16T13:30:45 corresponds to June 16, 2007 1:30:45pm.
- Duration Day-Time – Specifies a duration in the default format PwDxHyMzS. For example, P10D15H20M30S corresponds to 10 days, 15 hours, 20 minutes, 30 seconds.
- Duration Year-month - Specifies a duration in the default format PcYdM. For example, P5Y6M corresponds to 5 years, 6 months.
All times in the form area are updated to reflect the users' time zone. Times in views and the standard properties of a record are displayed in UTC (coordinated universal time).
Selecting Dates and Durations from the Expression Builder
You can define dates or durations easily using the calendar icon in the Expression Builder. When you do so, the value will automatically be formatted correctly and ready to be used in complex date calculation expressions.
- Click on the Calendar icon in the Expression Builder
- From the menu that appears select either Date/Time or Duration.
- If you select Date/Time a calendar window will appear. You can choose the format of the data type by clicking on the Date, Time or DateTime radio buttons. The calendar portion allows you to select a year, month, date and time. The area that the time is defined is based on a 24 hour clock, if you enter 15 for the hour it is equivalent to 3 pm.
If you select Duration the duration window will appear. From here you can select the format of the data type by clicking on the yearMonth or dateTime radio buttons. Enter an integer value in the appropriate section. For a negative duration click the Negative checkbox.
Types of date/duration calculations
There are four major types of date/duration calculations available to Coghead users. These functions can be combined to perform more advanced date calculations.
- Date/Time + Duration = Date/Time – As an example, you would use this to find the date a week from today.
- Date/Time – Date/Time = Duration - You would use this to find the duration between two times, two dates or two date/times. As an example, you could calculate how many hours are between 12am and 3pm or how many days there are until a project deadline.
- Duration + Duration =Duration – You can use this to add durations. For example, you can sum the total time it took to complete a project by adding up the duration of its tasks.
- Extract Functions – You can use the Date/Time/Duration functions to pull certain values from dates, times and duration values. For example, you can pull the number of hours from a duration.
Context functions
There are three context functions which can be used to perform calculations based on the current date or time. These three functions can be combined with other expressions to display advanced calculations such as the duration between a selected date and the current date.
Function |
Description |
| Current-date () | Inserts the current date into the field. |
| Current-time () | Inserts the current time into the field. |
| Current-dateTime() | Inserts the current date and the current time into the field. |
These calculations automatically update when a record is modified and saved.
Date/Time Functions
The following functions can be performed on a Date/Time type data field.
Function |
Description |
Example |
Date-Time |
This function turns the enclosed value into a date-Time formatted value. |
datetime(2008-02-07T20:00:00Z) will force the text string enclosed in the parentheses to behave like a date-time value. Then it can be used in date calculations with other date-time values |
Day-from-dateTime |
This function returns the day digits from the date portion of a date-time. |
If you have June 16, 2007 12:15:30am this function will return the integer 16. |
Hours-from-dateTime |
This function returns the hours digits from the time portion of a date-time. |
If you have June 16, 2007 12:15:30am this function will return the integer 12. |
Minutes-from-dateTime |
This function returns the minutes digits from the time portion of a date-time. |
If you have June 16, 2007 12:15:30am this function will return the integer 15. |
Months-from-dateTime |
This function returns the month digits from the date portion of a date-time. |
If you have June 16, 2007 12:15:30am this function will return the integer 6 (for June which is the 6th month of the year). |
Seconds-from-dateTime |
This function returns the seconds digits from the time portion of a date-time. |
If you have June 16, 2007 12:15:30am this function will return the integer 30. |
Year-from-dateTime |
This function returns the year from a date portion of a date-time. |
If you have June 16, 2007 12:15:30am this function will return the integer 2007. |
Date Functions
The following functions can be performed on a Date type data field.
Function |
Description |
Example |
Day-from-date |
This function returns the day from a date. |
If you have June 16, 2007 this function will return the integer 16. |
Month-from-date |
This function returns the month from a date. |
If you have June 16, 2007 this function will return the integer 6 (for June which is the 6th month of the year). |
Year-from-date |
This function returns the year from a date. |
If you have June 16, 2007 this function will return the integer 2007. |
Time Functions
The following functions can be performed on a Time type data field.
Function |
Description |
Example |
Minutes-from-time |
This function returns the minutes digits from a time. |
If you have 1:30:45 pm this function will return the integer 30. |
Seconds-from-time |
This function returns the seconds digits from a time. |
If you have 1:30:45pm this function will return the integer 45. |
Hours-from-time |
This function returns the hour digits from a time. |
If you have 1:30:45pm this function will return the integer 1. |
Duration Functions
The following functions can be performed on a Duration type data field.
Function |
Description |
Example |
Hours-from-duration |
This function returns the hours digits from a duration. This will return a zero if you have a year-month formatted duration. |
If you have a duration of 10 days, 12 hours, 36 minutes and 57 seconds this function will return the integer 10. |
Days-from-duration |
This function returns the day digits from a duration. This will return a zero if you have a year-month formatted duration. |
If you have a duration of 10 days, 12 hours, 36 minutes and 57 seconds this function will return the integer 10. |
Minutes-from-duration |
This function returns the minutes digits from a duration. This will return a zero if you have a year-month formatted duration. |
If you have a duration of 10 days, 12 hours, 36 minutes and 57 seconds this function will return the integer 36. |
Months-from-duration |
This function returns the months digits from a duration. This will return a zero if you have a day-time formatted duration. |
If you have a duration of 5 years 8 months this function will return the integer 8. |
Seconds-from-duration |
This function returns the seconds digits from a duration. This will return a zero if you have a year-month formatted duration. |
If you have a duration of 10 days, 12 hours, 36 minutes and 57 seconds this function will return the integer 57. |
Years-from-duration |
This function returns the years digits from a duration. This will return a zero if you have a day-time formatted duration. |
If you have a duration of 5 years 8 months this function will return the integer 5. |