How do I preformat all the cells in a column?

English
#1

I have a very simple date and data column sheet. Each day I enter the date and that day’s data in their respective column. I have been unable to force them to accept and change it to display as I like, ie. the date will not correctly format (DD-MMM-YY) when entered as 10-06-26 and nor will the data column automatically convert a number entered as 56890.5 to 56,890.50. In other words, I cannot force the columns to modify the inputs to preset column formats. It is frustrating since the inputs are “cut and paste” operations. It works in Excel. Help, please!

#2

That is a confusing date format, at first glance either end could be the year, 28-Sep-26. More of a problem is that the layout is similar to that for ISO date format YYYY-MM-DD, when you don’t define the year when entering the date, Calc assumes ISO date format so it believes that the date is the year and the year is the date.

  • The best option is to just use ISO format,
  • The second best is to use the default date format for your locale

If you insist on using your indefinite date format then

  1. Click Tools > Options > Languages and Locales > General
  2. In the Date acceptance patterns field add ;D-M-Y and OK

Select the column, apply the format code, then type your date

#3

Understand that if I input a date into a cell, say for instance May 6, 2026 I have thirty options under right click, Format Cells to change the date to DD-MMM-YY in the Format Cells box to my preferred date format of 06-May-26. What I want to be able to do is to have that happen automatically when I paste any date into the date column.

#4

When you paste a date, it will bring it’s own format from where it was copied, overriding the existing format. For example, if you copy 28-Sep-26 from here and paste it into an unformatted cell, Calc will recognise that it is a date but will impose the default date format for your locale, maybe DD/MM/YY. If you paste it as unformatted text into your formatted cells, then it will be pasted as a date but take format of the cell.

If you copy 2026-09-28 then it will paste into an unformatted cell as YYYY-MM-DD

Paste as unformatted text then the pasted date will receive the existing format from the cell

#5

You do not understand what I have done in the past in Excel and thought I was able to do the same in Calc. Since we cannot understand each other on this topic, tell me, can I go into settings and format the spreadsheet’s default date to follow my preferred date format?

#6

I have told you how to add another date acceptance pattern, this might be needed so that LibreOffice knows the unformatted text is a date.
You can set the locale to somewhere else but I do not know of a country/language that uses DD-MMM-YY as its default date format.
You can set the column in your desired date format and as long as you paste as unformatted text, the pasted date will accept the format.

LibreOffice does not aspire to be a clone of Excel and does not try to second guess the user.

#7

Let’s compare.
Open the attached file in Excel and Calc, and describe the sequence of actions that leads to different results.
ForumExample.xlsx (9.5 KB)

#8

It seems that setting a cell style for the column can override an htm format (but not a Calc format {paste as unformatted text}) stored on the clipboard as long as LibreOffice recognises it is a date. Your other example date, May 6, 2026, would be pasted as text if the default for your locale is D/M/Y and there isn’t a date acceptance pattern M/D/Y. You will need to do this for the date cells only.

  1. In the Sidebar, in the Styles pane, right click Default and select New
  2. In the new dialogue
    1. In the General tab give the new style a name, e.g. DateMyStyle
    2. In the Numbers tab in the Format Code field, enter DD-MMM-YY
  3. OK
  4. Select the date column then double click the new style DateMyStyle to apply it

To have this style available in all new spreadsheets you would need to create a default template

  1. Open a new blank spreadsheet
  2. Create the cell style as above but don’t apply it
  3. Click File > Templates > Save as template
  4. In the dialogue
    1. Give the template a suitable name, e.g. MyDate
    2. Select My Templates
    3. Click the box Set as default template
    4. Save

To stop it being the default template, click File > Templates > Manage templates, right click MyDate and select Reset Default

#9

Thank you all for your help. It appears as though amongst all the work around and advice offered seems to have worked for me. The pasting of unformatted text was the key. Thanks again to all for your input.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.