CALC unable to autocomplete date series mm/yyyy

English
, ,
#1

In CALC I have entered 3 rows as:
04/2025
05/2025
06/2025

When I select all three and drag down from the corner, the entire column gets filled with the first date, i.e., 04/2025.
I then formatted the three rows as “dates” , incl. all the empty cells below that I try to autofill. Then again selecting the tree entered dates and dragging down from the corner all rows get filled with 04/2025. WTF?
I don’t remember having issues with dates in CALC before. Is this some bug that was introduced as part of some update?

#2

Ask/Guide - How to use the Ask site - The Document Foundation Wiki #More_details

#4

… but is your 04/2025 really a date? Wich?
Your title mentions dd/yyyy so Calc shall guess the month?
In my local settings I can use dd.mm. and the year will be the current year, but your variant is new to me…
.
Start checking your locale and date acceptance patterns.

This will not change any strings you entered before. It is only output-formatting

1 Like
#7

Sorry, it should have said mm/yyyy in the title. Just corrected it. When I enter
04/2025
05/2025
06/2025
those are meant to be April, May and June 2025. When dragging the corner I would expect LO to autofill this until 12/2025 and then have 01/2026 in the next row.

#10

You want a series of months starting at April 2025?
Enter 1st of April as a true date with day, month and year.
Dont’ care about formatting! Value highlighting should show a blue date.
Select the cells to be filled.
Menu:Sheet>Fill>Series… by month
Hide the day portion with some number format like MM/YYYYor MMM/YYYY if you want.
Strict rule: Number format never changes the value. If the actual cell value is text (no blue highlighting), number format does not have any effect (1). If the actual cell value is a number (blue highlighting), no number format will change that value, but display the same value in some fashion.

(1) yes, I know that number formats for text values do exist, but they are rather esoteric.

#11

Marked as solution. Thank you.

#3

Or text…
Menu / View, check Value Highlighting.
The font color will show whether the cell contains a date or text.

1 Like
#6

When I do this the dates do not change colour. However, in the menu ribbon, the “Format as Date” icon is selected when I am in one of those cells. So it is formatted as date but not highlighted. How odd!

If I enter a date with the day as well, e.g., as 2025-04-01 or 01/04/2025, then it does get highlighted and when I apply the same custom formatting I applied to the manually entered dates (namely mm/yyyy) then LO automatically reformats them to 04/2025 and maintains the highlighting.

Does this mean that I ALWAYS need to enter a full date (including the day) in order for LO to get that it is date? Seems rather cumbersome and fairly restrictive; also not very practical if you have received a file with loads of prefilled date values that do not meet these requirements.

#8

You’ve already done a little research. :slight_smile:

The cell value (text, number) and cell format live their own lives (as in Excel). Changing the cell format never changes the cell value.

As for the convenience of entering dates, Calc has a special customization mechanism - Date acceptance patterns.

2 Likes
#9

Good to know about those patterns. Thanks.

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