Problem: Concatenating the Time Field and Date Field Results in Additional Dates and Times

Description

In ArcGIS Pro, the Calculate Field tool can be used to concatenate two or more attributes into one field in a table. However, in some instances, concatenating the time field and date field results in additional dates and timestamps being added to all entries of the output. The additional dates and timestamps added to the output are ‘30/12/1899’ and ‘00:00:00’.

Additionally, when added to a map, the default date value of '30/12/1899' is added to all entries in the time field.

The following example shows the additional dates and timestamps added to the output and the time field in the data's attribute table in ArcGIS Pro.

In ArcGIS Pro, the default date value 30/12/1899 is displayed before the time in the Time_Sample field. The date in the Date_Field is displayed in the dd/MM/yyyy format. Under the DateTime field where the result of the concatenation is populated, 1899-12-30 and 00:00:00 are also included.

Both the time and date fields are displayed correctly when the data is viewed in Microsoft Excel.

The table in Microsoft Excel displays the time in the hh:mm AM/PM format under the Time_Sample field, and the date in dd/MM/yyyy format in the Date_Sample field.

Cause

When an Excel file is added to a map in ArcGIS Pro, the geodatabase formats the time and date fields as datetime yyyy-mm-dd hh:mm:ss AM or PM. The settings of the Windows operating system determine how the time and date fields are displayed.

This causes the default date value of '30/12/1899' to be automatically appended to the time field without a date value, and the timestamp of '00:00:00' being appended to the date field without a time value.

Solution or Workaround

To resolve this issue, prior to concatenating the time and date field, remove the default date values in the time field using the Convert Time Field tool and the additional timestamps from the date field.

Use the Convert Time Field tool to remove the default date value in the time field.
1. In ArcGIS Pro, on the Analysis tab, click Tools to open the Geoprocessing pane.
2. In the Geoprocessing pane, search and select Convert Time Field.
3. In the Convert Time Field tool pane, for Input Table, click the drop-down arrow, and select the input table.
4. For Input Time Field, click the drop-down arrow, and select the time field displaying the additional date. In this example, the Time_Sample field is selected.
5. For Output Time Type, click the drop-down arrow, and select Text.
6. For Output Time Format, enter a custom time format. For example, to display the time as '07:30 AM', enter hh:mm tt in the box. Refer to ArcGIS Pro: Dynamic text tags for more information.
7. Click Run. The output is now populated in a new field.

Fill in the parameters in the Convert Time Field tool pane. For the Input Table, Input Time Field, and Output Time Type section, click the drop-down arrow next to the boxes to select the parameter. For Output Time Format, type hh:mm tt for a custom time format. Click Run at the bottom right of the pane to run the tool.

The result from the Convert Time Field tool is populated in the new field, Time_Sample_Converted. The time values are displayed in the hh:mm tt format. One example of the output is 07:30 AM.

Once the default date value is removed from the time field, remove the additional timestamps from the date field by referring to How To: Remove timestamp from the date field in ArcGIS Pro for the workflows.

The result from running the Convert Time Field tool to remove the default time value 00:00:00 is populated in the Date_Sample_Converted field. The date value is displayed in the dd/MM/yyyy format.

Use the Calculate Field tool to concatenate the converted time and date fields created in Step 1 and Step 2. Refer to How To: Concatenate fields in a table in ArcGIS Pro for steps to do so.

The time and date fields are concatenated without the additional date and timestamp in the DateTime field.