Date entries for formula fields

1 Initial Problem

The formula field in data collections does not support date entries (datetime) so far. If you want to calculate the difference in days e.g. between two dates in a formula field, this is currently not possible. This means: Event A (dd.mm.yyyy) - Event B (dd.mm.yyyy) = x days (or months/years)

2 Conceptual Summary

The formula field should be extended so that Date entries can also be integrated. That means, for the valid fields, the entries of the type "Date Entry" should also be regarded and be listed. Currently, only entries of type Integer (int) can be selected for calculation, this has to be extended with entries of type Date Entry (datetime).

3 User Interface Modifications

3.1 List of Affected Views

• Data Collection > Fields > Add new Field > Formula

3.2 User Interface Details

Valid fields would have to be expanded or entries of the type "date entry" would also have to be taken into regard:

The formulas can only provide meaningful results if the following conditions are met:

• Two or more date fields are offset against each other.
• Field types that do not contain dates are not offset against each other. For example, not a test selection field with a date field. If this is the case, the result does not make sense.
• Numerical values or integers can be used directly in a formula; these are interpreted as the number of days (see example below).

The output is always a date output or a difference value in days, see operations below. Some operations do not produce an output.

3.2.1 Examples of operations without integer values

(-) date - date: output as difference in days
(+) Date + Date: No output
(*) Date * Date: No output
(/) Date / Date: No output
(^) Date ^ Date: No output
(SUM) SUM(Date1, Date2): No output
(AVERAGE) AVERAGE(Date1, Date2): Date output of the date in the middle
(MIN) MIN(Date1, Date2): Date output of the smaller date
(MAX) MAX(Date1, Date2): Date output of the larger date

For Average we would take the date that lies exactly between two dates e.g. for 10.1.23 and 20.1.23 it would be 15.1.23 (from each date 5 days
 away). 

3.2.2 Examples of operations with integer values

(-) Date - Integer: Output the date minus the number of days from Integer
(+) Date + Integer: Output of date plus number of days from integer
(*) Date * Integer: No output
(/) Date / Integer: No output
(^) Date ^ Integer: No output
(SUM) SUM(Date1, Integer): No output
(AVERAGE) AVERAGE(Date1, Integer): No output
(MIN) MIN(Date1, Integer): No output
(MAX) MAX(Date1, Integer): No output

3.3 New User Interface Concepts

There are no changes in the user interface

3.4 Accessibility Implications

The forms and displays do not change, so there is no improvement or worsening in accessibility.

4 Technical Information

In particular, it is an extension of the formula parser under Modules/DataCollection/classes/Fields/Formula/class.ilDclExpressionParser.php

5 Privacy

No change regarding privacy. No new personal data is collected, processed or stored.

6 Security

The feature does not introduce any new security-relevant endpoints or attack vectors. 

7 Contact

• Author of the Request: Lorenz, Katharina [klorenz]
• Maintainer: Amstutz, Timon [amstutz]
• Implementation of the feature is done by: sr.solutions with PR (has to be reviewed and merged by coordinators)

8 Funding

If you are interest in funding this feature, please add your name and institution to this list.

• HS Bremerhaven

9 Discussion

JourFixe, ILIAS [jourfixe], 17 APR 2023 : We highly appreciate this suggestion and schedule the feature for ILIAS 9. It would be helpful to offer some information about possible operations in the Online Help.

10 Implementation

The feature has been implemented as described above with one exception: Currently it is not possible to subtract an integer field or integer value from a date field (resp. integer fields would always be treated as seconds in this case). The implementation in the data collection currently does not allow such an extension.

Test Cases

Test cases completed at 18/10/23 by Lorenz, Katharina [klorenz]

• C272: Felder hinzufügen

Approval

Approved at 2023-10-19 by Gerdes, Ve Alexandra [vgerdes].