11

I have a spreadsheet with existing date values that I'd like to convert from MM/DD/YYYY to DD/MM/YYYY format. Is there any way of doing this?

The spreadsheet comes from a csv file from someone else that i have opened in calc.

enter image description here enter image description here

Steve
  • 418
  • 1
  • 4
  • 16
  • Have you tried changing the format of the cells? – cybernetic.nomad Feb 11 '20 at 17:34
  • @cybernetic.nomad yep, doesn't work unfortunately – Steve Feb 11 '20 at 17:43
  • Are you sure these are dates and not text that looks like dates? – cybernetic.nomad Feb 11 '20 at 17:47
  • @cybernetic.nomad hmmm, they could be text. how would i know? i never work with spreadsheets/calc so i'm a bit out of my depth here... – Steve Feb 11 '20 at 17:51
  • @cybernetic.nomad okay, looks like they are def text values... – Steve Feb 11 '20 at 18:12
  • 1
    Where do the values comes from? Did you enter them by hand, imported them from a CSV or so, got the spreadsheet from someone else? -- Would you mind to post one of these values? You might try to copy-paste it, or only if that does *not* resemble the value as shown in Calc, provide a (small) screenshot. -- Please [edit] your question, don't add information down here in the comments. – the busybee Feb 11 '20 at 18:40
  • @thebusybee copy/pasting looks the same as it does in Calc. Have added the rest to the question. – Steve Feb 11 '20 at 19:27
  • 2
    When you opened the CSV in Calc you are asked about the columns format. Did you try to set the data type of `payment_date` to "date"? -- Would you mind to provide a few lines of the CSV file, or at least some excerpt of it that includes `payment_date`? Obviously you would like to edit some sensitive values, and to limit the number of fields. You can open the CSV file with any text editor. – the busybee Feb 11 '20 at 19:34
  • @thebusybee I included a screenshot in the question. i'll add another one now with the box that pops up when i open the file before i click "ok" – Steve Feb 11 '20 at 19:46
  • Try using "English US" as the language(`MM/DD/YYYY` is the US date format) and/or activating "Detect Special Numbers" – cybernetic.nomad Feb 11 '20 at 20:03
  • OK, where the "Fields" are shown, you should be able to change from "Standard" to "Date". -- Concerning the CSV file excerpt, you can use gedit to open it. I think that you're running Ubuntu, right? – the busybee Feb 11 '20 at 20:04
  • @thebusybee yes, ubuntu. i can change it to date and then select DMY but it doesn't change anything – Steve Feb 11 '20 at 20:25
  • Now I'm really curious about to see the values in the CSV file. – the busybee Feb 11 '20 at 20:27
  • @thebusybee if it's a text value is there no way of making a function that swaps the day/month around? – Steve Feb 11 '20 at 20:37
  • Sure, but I'd like to get something to experiment with. Without seeing what your CSV file contains *exactly* we'll be guessing all night. That's clearly a waste of time for both of us. And at my place it's time to go to bed now. :-} Please try to provide an excerpt of the CSV file. As I said, you may use any text editor to open it, and to remove all but a few lines, and to protect the sensitive data. See you tomorrow morning. – the busybee Feb 11 '20 at 21:06
  • The tip was gold, when opening the CSV in Calc, select column(s) with dates and change from Standard to Date, worked for me. – gseattle Dec 18 '21 at 11:31

4 Answers4

14

  1. Select the cells with the dates and in Cell -> Format, choose the date format you wish (DD/MM/YYYY)

  2. Making sure the cells are selected, go to Data -> Text to columns choose "Separated by" and "Tab" and click "OK"

Cells should now have the desired format

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
7

  1. Cut (or copy then erase) all values from the column (CTRL + X).

  2. Choose Edit -> Paste Special -> Unformatted text (MAJ + CTRL + ALT + V).

  3. On "Fields", click on your column, then select "Date (MDY)". It is important here to select the format the data currently is, NOT the format you want it to be.

If date is still not OK, you will need to follow this additional step (response above from @cybernetic.nomad):

  1. Select the cells with the dates and in Cell -> Format, choose the date format you wish (DD/MM/YYYY).

NB: Note that my LibreOffice Calc is not in english so I am not 100% sure about the option names.

dandyboh
  • 117
  • 1
  • 2
  • << On "Fields" >> here, you lost me. Where is that "Fields" widget? – Hans Deragon May 27 '22 at 12:50
  • @HansDeragon : if you cut (CTRL + X) then Unformatted text paste (MAJ + CTRL + ALT + V), a window called "Text import", similar to the one shown on Steve question, should appear. "Fieds" is located on this window. – dandyboh Jun 24 '22 at 12:49
5

You select the cells to format, then you press Ctrl-1 (or choose "Format" - "Cells..." from the menu, or any other way) to open the dialog to format cells. There is a text field "Format code" where you enter "DD/MM/YYYY" without the quotation marks. Click "OK".

If that does not format the date like you want, the value in the cell is not interpretable as a date. Investigate the cell's value, you might have a text marker (a single apostrophe ') as the first character.

To check this out and experiment with the formats, choose "Insert" - "Date" from the menu. It puts the current date into the current cell.

Disclaimer: Since my Calc is not set to English, the names of menus and their entries might be different. I just guessed how they could be named.

EDIT:

If you can't change the cell format because the date is constantly recognized as "text", you might like to try this:

  1. Insert a column right from the date column. This produces in your case the column O. All columns right of it are pushed to the right.

  2. Put this formula in cell O2 on the right of the first date text:

    =CONCATENATE(MID(N2;4;3);MID(N2;1;3);MID(N2;7;4))

    It takes snippets (MID()) from cell N2 and concatenates them in the new order. Details of the functions are in Calc's documentation.

    You will need to adjust N2 to the correct cell reference if it is not the cell directly left of the cell you're entering the formula.

  3. Put the cell cursor on N2.

  4. Press these key combinations in sequence to mark a deep enough range of cells in column O: Control-Down, Right, Shift-Control-Up. The cell cursor will be on cell O2 now, and the range below and including it is marked.
  5. Now press Control-D to copy the formula in O2 into each cell in the range.

Now you will have a new column O with the text of N, but exchanged characters. The values are still "text", though.

If you like, you can shrink the width of column N now, or hide it.

the busybee
  • 10,755
  • 3
  • 13
  • 30
  • It looks like it is text and not date values... how can i get the desired format as a text value? – Steve Feb 11 '20 at 18:03
  • 1
    There are some converter functions. Please see Calc's documentation, or add more information to your question, as I just asked for above. – the busybee Feb 11 '20 at 18:41
0

Example: Input wanted: DD/MM/YYYY (31/12/2021)

Input accepted: MM/DD/YYYY (12/31/2021)

First add new Date type

  1. Go to Format --> Cells
  2. In "Category" select "Date"
  3. In "Format code" write the input wanted. In this case DD/MM/YYYY
  4. Press the green check

Second format style

  1. Go in the cell where you want to add the date
  2. Write the date in the wrong format. Example: 12/31/2021 (for 31st December 2021) even if you want 21/11/2021
  3. Select that cell and press CTRL-X. The text should disappear
  4. Select that now empty cell and do: Edit -> Paste Special -> Pase unformatted text
  5. The date should be there in the wrong format
  6. Select that cell with the wrong date and do: Format --> Cells
  7. In "category" select "date" and double click on the newly added date type (aka DD/MM/YYYY)
Magofoco
  • 5,098
  • 6
  • 35
  • 77