|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → Recorder Troubleshooting and Bug Reports → Date error on Excel import
Dates were incorrectly transferred from an xlsx file I've just imported.
No sign of anything going wrong until I looked for the records in Recorder and found a fairly consistent error:
30/6/2015 became 29/6/20011
and the other 5 records also subtracted 4 years and 1 day
Thank goodness it was a small file so I could fix it by hand
If it had been 1st April, I wouldn't have believed this. The import wizard checks the dates so you can't get dates in beyond now. This is also checked at the import stage, so this must be some sort of corruption after R6 has proceseed the data. I hope it is a one off.
Sorry, that's a typo, I meant 29/6/2011. So no dates into the future, just a loss of 4 years and 1 day
I have just run an import using 30/6/2015 and it has come through OK. Keep an eye on the dates in future imports and if necessary it can be investigated in more detail. Or just run a simple test with one record for that date to see if it happens again.
I suspect that the Excel file has originated from a Mac computer. The difference of 4 years and 1 day is due to the difference between Mac and Windows date systems. Day 1 = Jan 1 1900 (Windows) = Jan 2 1904 (Mac). As the import process is reading the date value and not the formatted appearance, it is not apparent when just looking at the Excel file. I have one regular recorder with a Mac; his files don't always cause this issue but I now check by Copy/Paste Value into a new Excel file and then reformatting the date into dd/mm/yyyy format. The magic number to rectify the issue is 1462.
Many thanks for the explanation - I was worried that there might be a serious problem.
Thanks Alison
This has happened to me several times in the past and did again recently. The problem is that it is very difficult to spot unless you know the file has come from someone who uses a Mac. Is there any way that a future update to Recorder can recognise a difference between the displayed date and the actual date that is imported and so include a warning?
Phil
CPERC
The date system is encoded in the workbook, but it depends whether the Import Wizard (an external component?) can detect this.
Easiest work around is to save as CSV.
Forum → Recorder Troubleshooting and Bug Reports → Date error on Excel import
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.