[Solved] Date formatting in spreadsheet

[marigold022]

I have many years of data in an excel spreadsheet which I converted to OO. for a year or more it worked fine, then on opening the sheet, all the dates were rubbish.

I cannot make it work. A field formatted as DD/MMM/YY into which I type 1/8/16 writes as the eighth of January - obviously American. I cannot find any way of insisting that it uses UK or Australian dating. Bad enough that I will have to try to re-create all the dates, but when I did a few and closed the sheet, re-opening sent all the dates to rubbish again.

It never used to be like this, what has changed?

[acknak]

Sorry you're having trouble. Dates can be troublesome because the internals are different from what you see on the screen. It's important to know and understand how the insides work.

First, what document format are you saving to? Saving to any format other than OOo's native ODF format (.ods for spreadsheets) carries some risk of losing formatting or even content. If you're not saving as ODF, try that from here on.

Second, and this relates to what I said about knowing the inner workings, the cell format does not affect data input, or the value stored in a cell. For input, Calc uses built-in rules, based on what locale it thinks you're working in. If your locale is English (US), then dates are always entered as M/D/Y, regardless of the cell format. If I'm in a situation where I'm not sure what input rules Calc is using, I enter dates in international format: YYYY-MM-DD That works correctly no matter what.

Finally, are you still using OO 3.1 as shown in your forum signature (bottom line of your posts)?

If none of this helps sort out the problem, can you attach your sheet here so we can see what you're dealing with?

[Villeroy]

Tools>Options>LanguageSetting>Languages>Locale (2nd option) is set to English(USA). This option determines how all numerals in the entire office suite are handled by default. Within the cell formatting dialog you can override this language for selected cells. So you have 2 options: Change the handling of numeral input for the entire suite or for the cells in question. Working with templates and styles you can also specify the right context for (groups of) documents.

[MrProgrammer]

A field formatted as DD/MMM/YY into which I type 1/8/16 writes as the eighth of January - obviously American.

What do you have for option Language Setting → Languages? Options are displayed/set with OpenOffice.org → Preferences on a Mac, Tools → Options on other platforms (like Windoze). It will help us if you'd attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

I did a few and closed the sheet, re-opening sent all the dates to rubbish again.

Background material on how dates are handled in Open Office can be found in section 3. Dates in cells in Ten concepts that every Calc user should know, and section 5. Understanding data entry explains how Calc interprets values (including dates) that you enter. Advice about fixing dates in a column can be found in [Tutorial] Text to Columns; Q40/A40 discusses the case where all the dates have the month and year reversed, not quite your situation but perhaps a piece of the solution. Be sure to have a backup of the spreadsheet before attempting to fix the problem with Text to Columns. Be sure you know which copy you're working with, since you now have two of them. I find that it's helpful to put the date in the backup's name (File as of YYYYMMDD.ods) so that you can tell, months later, that it is old. Read section 6. Saving your data and making backups in the Ten Concepts tutorial. Better yet, tell us your locale and attach your spreadsheet so we can give you specific instructions for your situation. Tell us which dates are "rubbish" because there is no way we can know if 4/7/2016 means April 7 or July 4.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

Tell us which dates are "rubbish" because there is no way we can know if 4/7/2016 means April 7 or July 4.

Well, he complains that typing 1/8/16 gives an unwanted January date. So there might be a global US locale or a cell specific US locale.

[marigold022]

Thank you people, turned out to be my locale setting. I had gone to languages before but did not realise that I was on level one and all it said was this is where you make settings for languages. My idiocy I'm afraid. Still don't get why it worked fine for 18 months and then fried. I do have version 4.1.2 so maybe an update altered something. Thank you all once more.

[acknak]

I believe the keyboard can actually change the locale, for example if you type an accented character or a character from another language, the keyboard can tell OO to change locale. Sorry, I don't know the details of how that works but I've seen it discussed here.

[Villeroy]

This is one of those settings with a default. In that list you have ...

Default -- English(USA)
... and ...
English(USA)
...

The default locale is taken from the operating system (the locale only, not any individual settings for that locale!). When you switch the OS locale from Australia to USA, the office suite might change this setting from Default--English(Australia) to Default--English(USA), possibly after restarting the suite.