I am trying to upload employee data, but I am getting an error with the date format.
Sometimes, a date on Excel might look like a date and state that it is in date format, however Excel might read it as text.
The best way to test whether a field in Excel is formatted as a date is to convert it to “General” data type. Dates in Excel are stored as a number, which is the number of days since 1/1/1900. To test follow these steps:
- Right-click on one of the fields, then click Format
- Change the format to General
- Pay attention to the Sample value in the preview window.
- If you see a date, then this is a text value and not stored as the required number format
When importing the data into Peakon, if the date is stored as a text value, the two systems are unable to recognize the date and apply it to the relevant employee. The solution is to use the 'Text to columns' method.
- Select the affected column eg. Start date, minus the column header. You can select the first date in the column, then Ctrl + Shift + ↓
- Click on the Data tab at the top of the Excel file
- Choose the Text to Columns button, which will open the Text to Columns wizard
- On page 1 or 3 of the wizard, choose the Delimited option and click Next
- Leave page 2 of 3 as it is, with the Tab option selected, and click Next
- On page 3 of 3, choose the Date option, then choose the current format of the dates, for example if your dates are DD-MM-YYYY, choose DMY
- Click Finish
Article is closed for comments.