Date fields can now be used in Text Calculations

Date fields can now show correctly as dates (NOT as milliseconds). Date fields can also have a specific format set for them such as dd/mm/yyyy

Date fields can now be used in text calculations and table text calculations

Date fields can now be used in text calculations and table text calculations

Previously, if a date field was used in a text equation or a table text equation it would appear as milliseconds

Previously, if a date field was used in a text equation or a table text equation it would appear as milliseconds

Previously, you could not create a table text calculation, or a text calculation field in a table that included a Date field. The date would show as milliseconds (as shown above).

To retain a date format in a text equation you need to change the concatenate equation from just referencing the date field name (e.g. Date Affected) so that is has a date(fieldname) before the field name

To retain a date format in a text equation you need to change the concatenate equation from just referencing the date field name (e.g. Date Affected) so that is has a date(fieldname) before the field name

To use a date field in a text equation (as shown here) the equation must use date(fieldname) in the equation

To use a date field in a text equation (as shown here) the equation must use date(fieldname) in the equation

You MUST add date before the field name, or the date will be shown in milliseconds. For example,

concatenate (Date Affected, ", ", Medical Issue) would need to be changed to

concatenate (date(Date Affected), ", ", Medical Issue)

The example here shows that date was added to the Text Calculation field (named Summary). This is then referenced by the Table Text Calculation Field and still retains the date format correctly.

The example here shows that date was added to the Text Calculation field (named Summary). This is then referenced by the Table Text Calculation Field and still retains the date format correctly.

As an example, the Summary field is then pulled into the Table Text Calculation and it is formatted correctly.

If you were performing a table text calculation of the date field (without using a text equation in the table as shown above), then date would need to be added before the field name (as shown here)

If you were performing a table text calculation of the date field (without using a text equation in the table as shown above), then date would need to be added before the field name (as shown here)

Because date was added, the date field "Date Affected" is pulled through correctly into the Table Text Equation. Without "date" added before the field name, the Date Affected would show as milliseconds (as shown in the step below)

Because date was added, the date field "Date Affected" is pulled through correctly into the Table Text Equation. Without "date" added before the field name, the Date Affected would show as milliseconds (as shown in the step below)

The example here shows that a table text calculation has been created that summarises the table column Date Affected. "date" was added before the field name in the calculation and the date is pulled through correctly.

An example without "date" being added before the field name shows the "Date Affected" field appearing in milliseconds in the Table Text calculation

An example without "date" being added before the field name shows the "Date Affected" field appearing in milliseconds in the Table Text calculation

In addition to adding date before the date field name so it displays correctly, you can set the format that the date is displayed as

In addition to adding date before the date field name so it displays correctly, you can set the format that the date is displayed as

The image here shows the Date Affected field in the "Summary" field Text calculation being displayed as dd-mm-yyyy. This is the default format for dates to be displayed. This text equation would be written as :

concatenate (date(Date Affected), ", ", Family Medical Issue)

The Date Affected field is also being used in the Text Calculation "Summary with date format added". You will notice there is a difference between the ways the dates are displayed in the Summary and the Summary with date format added fields; dd-mm-yyyy and dd/mm/yyy respectively.

To reformat the way the date is displayed, the Text calculation equation needs to have a format added. The "Summary with date formate added" field had to be written with a format dd/mm/yyyy for it to display in that format:

concatenate (date(Date Affected, "dd/mm/yyyy"), ", ", Family Medical Issue)

You can reformat a date field that you use in a Text calculation and Table Text Calculation from the dd-mm-yyyy format (that is the default format for dates in the system) to a different format (as shown in the image above) by adding a format into the equation

You can reformat a date field that you use in a Text calculation and Table Text Calculation from the dd-mm-yyyy format (that is the default format for dates in the system) to a different format (as shown in the image above) by adding a format into the equation

The steps above outline how to set up a date field to show in  text calculation. Now you can change the format the date is displayed in by adding a format to it.

It can be any one of the formats below, but it must be written like this:

date(Date Field, "format") e.g. date(Date Field, "MM-dd-yy")

This takes the Date field you are referencing and specifies that it will be displayed as a date format, and then it specifies what format it will display as.

date(date field, "dd-MM-yyyy")

date(date field, "MM-dd-yyyy")

date(date field, "dd/MM/yyyy")

date(date field, "MM/dd-yyyy")

date(date field, "dd-MM-yy")

date(date field, "MM-dd-yy")

date(date field, "dd/MM/yy")

date(date field, "MM/dd-yy")

0 Comments

Add your comment

E-Mail me when someone replies to this comment