I am unable to change Date Format in a large spreadsheet from a variety of formats to one consistent date format. Everything I try to change 1/3/2010 to 2010-01-03 does not work.
Please advise. Thank you.
Are your dates really dates? Click View > Value highlighting to see, Numbers and Dates will be blue, Formulas will be green and text will be black.
If your dates are actually text then this page will help, Calc Number text to Numeric data
3 Likes
So we are talking about 3rd of January 2010, in US notation 1/3/2010, 3/1/2010 in the rest of the world. Most likely, you imported these values from a text file or from clipboard ignoring the import options.
On the text import dialog:
- Check “Detect special numbers”
- Choose the right import language, “English (USA)” in this particular case.
Thanks for the replies and advice. Unfortunately the steps suggested didn’t work. The cells in question are text (black) and non-responsive to format change. The document has been managed by a variety of staff over time and it seems odd that a simple entry per cell or copy/paste per cell should reveal such a awkward fault for future adjustments. In terms of ‘ease of use’ regardless of computer or software knowledge, to me this is a glaring ‘glitch’ that needs repair. I am excited to support, promote and use LibreOffice as a primary Spreadsheet tool for my business, but this issue makes my choice to use the software a dilemma. My only option, I will go forward and individually change each cell entry to correct the problem.
Time is precious.
We waste too much time because of all those words with no actual data. I can import any csv file into Calc, no matter were it came from, given that the file contains valid csv anyway.
No that is not the only option. You may import correctly again, as @Villeroy suggested if the source is still available or you can use the link given in the first comment by @EarnestAl to the FAQ-topic. It shows several methods to change the type of all values. I prefer “text-in-columns”, but choice is up to you
.
Manual edit I’d recommend for less than 10 dates to convert and you middex the opportunity to learn something useful for the next time when there’s text instead of data.
Ok. Sit down and write precise directions how a program can decide wether your copied data 01/03/24 is first of march or third of january, and if we have the birthday of my grandfather in 1924 or my daughter in 2024. If you solve the problem there will be a programmer to implement it. If not, it is better code is not altering data it does not comprehend.
1 Like
Are the dates all formatted in USA style, that is M/D/YYYY or variants in the same M/D/Y order? If so:
- Format the column to Date, 12/31/99, Language (English USA) or just MM/DD/YY
- Click Edit > Find and replace Ctrl+H
- enter Find: .+
- enter Replace: $0
- under Other options activate
- Current selection only
- Regular expressions
- hit Replace All
- Finally, format the column to your desired format, e.g. YYYY-MM-DD
