[Solved] MM/DD/YYYY to YYYY-MM-DD format not working.

Discuss the spreadsheet application
Post Reply
Bones541
Posts: 7
Joined: Sun Sep 25, 2022 6:23 am

[Solved] MM/DD/YYYY to YYYY-MM-DD format not working.

Post by Bones541 »

Hi, sorry i know this questions is asked alot but ive been reading for hours and nothings helped.

I have imported from csv into a column 500 odd entries in the format MM/DD/YYYY , sometimes the month or day is only a single digit though.

I need them displayed YYYY:MM:DD

I have tried multiple times to highlight all cells or the entire column to format the cells to the needed format, everytime the cells remain unchanged.

Im at a total loss, help much appreciated, thanks.
Last edited by robleyd on Sun Sep 25, 2022 2:25 pm, edited 3 times in total.
Reason: Add green tick
OpenOffice 4.1.13 Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: MM/DD/YYYY to YYYY-MM-DD format not working.

Post by robleyd »

The most likely situation is that the import settings for the csv file were not correctly set* and the dates were imported as text. You can confirm they are text by using View | Value Highlighting, or Ctrl + F8. Text cells are formatted in black, formulas in green, and number cells in blue, no matter how their display is formatted.

*The setting missed was to select the option Detect special numbers
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Bones541
Posts: 7
Joined: Sun Sep 25, 2022 6:23 am

Re: MM/DD/YYYY to YYYY-MM-DD format not working.

Post by Bones541 »

Many thanks, you were correct in that they were imported as text.

I have re imported them with the detect special numbers also selected, this has given me mixed results.
Many of the imports are now in blue, and can be modified.
Many are still in black.

I have examined the sets still in black vs blue, and have a theory about why but am not sure how to fix it.

I converted the sets in blue easily, but they now show in YYYY-DD-MM and not YYYY-MM-DD despite me using the format to YYYY-MM-DD option.

My theory is on import its expecting the date / special numbers to be in DD/MM/YYYY and changing them to number format, but not changing anything it thinks is MM/DD/YYYY

So my theory is its also a region / world zone type setting.
Because all those still in black are e.g. 10/30/21 08/14/22 etc, are where the middle numbers are over 12.

Again many thanks for your answer, but any ideas how to fix them not all being imported as numbers and some still being text?
OpenOffice 4.1.13 Windows 10
Bones541
Posts: 7
Joined: Sun Sep 25, 2022 6:23 am

Re: MM/DD/YYYY to YYYY-MM-DD format not working.

Post by Bones541 »

Ahah, my theory was correct was a little to keen asking instead of toying, i re imorted and changed language to english usa instead of english, they are all now imported as numbers and convert properly.

Thanks again, enjoy your day.
OpenOffice 4.1.13 Windows 10
Post Reply