Date and Time Converted to Numbers

<< Click to Display Table of Contents >>

Navigation:  EDP > Troubleshooting >

Date and Time Converted to Numbers

Upon importing an EDD into EDP, the date and time fields in Excel get converted to numbers in EDP.

 

Resolution:

 

Typically this will occur if the EDD is open in Microsoft Excel while the user is also trying to load the EDD into EDP. To resolve this behavior, prior to loading the EDD, the EDD file should not be open in any application. Please verify that the EDD is not open in Microsoft Excel first, before importing the EDD in EDP.

 

Tips from the Community Center:

 

Other suggestions include saving the EDD as a txt file or the workaround below:

 

Question

 

What formula do I apply to the date field in my EDD to force data as datetime format?

 

Answer

 

To correct the date formula in Microsoft Excel so it is unaffected in EDP:

1.Open an EDD in Microsoft Excel.

2.Create a new column and select the column.

3.Right-click the column and select Format Cells.

4.Select Number Tab, and General.

5.Enter =TEXT(C3, "dd/mm/yyyy hh:mm"). Note that {C3 is the selected cell from the first column}.

6.Repeat the formula for the entire column.

7.Highlight the new column and select Copy.

8.Create a second new column and select Paste Special with the "value" radial button selected.

9.Copy the Column Header into the newly created second column.

10.Delete the original date column and the interim column created.