Wrong date format in exported calendar

[derbyu3a]

I am trying to export my Thunderbird calendar to a CVS file, but the date format in the converted file is wrong. I am in the UK and my PC date settings are DD/MM/YYYY. I have set the closest city on Thunderbird to Europe/London. When I open the CVS file in Wordpad, the dates are all in the format MM/DD/YY. When I open the file in Excel, those dates where the first two digits are 12 or less are then interpreted as DD/MM/YYYY. The other dates are not understood. Is there any way I can do the conversion so that the dates are exported in DD/MM/YY or DD/MM/YYYY format.

Thanks for any help.

[mgagnonlv]

Hello,

I'm not 100% familiar with CSV export format, but it might be part of the specs of calendar exports in general. That being said, you have two round-about solutions which are not as elegant as you might like.

1. Change (temporarily) your regional parameters.

– First make sure Excel is closed, otherwise it will bug.
– Export the CSV file.
– Change the regional parameters to mm/dd/yy. If you have decimal numbers in your export, you might as well make sure that you use the decimal point and the comma as thousands separator. The easiest way to do all of this at once is to change parameters to US.
– Start Excel and Import the CSV file. Dates will be imported correctly.
– Save the file in Excel format (.xlsx or .xls)
– Quict Excel and set back your regional parameters as you like.
– Reopen the Excel file. Your dates will still be OK and formatted the British way.

2. Use LibreOffice.

LibreOffice is a free office suite available on all 3 major platforms. While it has different strong and weak points, it can save files in formats compatible with Microsoft Office.

Once you have installed LibreOffice, here is what to do:
– Open the CSV with LibreOffice (hint: right click on the file name and Open with LibreOffice).
– At the prompt, you'll see a window that will show you a preview of the file as interpreted. At that point, you are invited to define various parameters such as: how fields are separated (enter ,), what is the decimal separator (.), how are dates interpreted (mm/dd/yy) and what is the character encoding (check accented letters to see if you should select UTF-8 or something else).
– Assuming you prefer to continue to work in Excel, save the file in ".xlsx" format, quit LibreOffice and get back with Excel.