[Solved+Issue] Date format and date entry do not match

[b.mckerchar]

I am struggling to format dates in a spreadsheet. I have formatted the one cell for Date as DD-MMM-YY and when entering 7 February 2024 as 7/2, the result is returned as 2 July 2024. When entering 7/2/24 the result is returned as 24 February 2007. Please assist with the date format function.

[Mountaineer]

You mix 2 features: Recognition depends on date-acceptance pattern, wich you could set in preferences (on Mac, Menu Tool>Options on other OS) - if you use LibreOffice .The recognized sequence is converted to a float. The integer part representa days since 30.12.1899, the decimals the part of a day, wich can be read as time: 0.25 = 06:00 AM
.
How the float is shown in the cell is determined by the formatting of the cell, and this may be completly different from date-acceptance pattern. Cell format is only used for output, not for input.

(Edit: Was told date acceptance patterns not configurable in AOO, so depend on locale only I guess.)

[MrProgrammer]

I have formatted the one cell for Date as DD-MMM-YY and when entering 7 February 2024 as 7/2, the result is returned as 2 July 2024. When entering 7/2/24 the result is returned as 24 February 2007.

The cell format controls how the cell value is displayed but is not relevant during data entry. When interpreting your entry in a cell, the setting which matters is your locale, which you did not tell us. No one can really help much until you reveal your locale. Always enter data as determined by your locale, no matter what the cell format specifies. It is not clear of entering means typing or if it means pasting. If the latter, the data which was copied, which we do not know, might affect how it is pasted.
Date input SNAFU
Read sections 5. Understanding data entry, 3. Dates in cells, and 2. Controlling how data is displayed in Ten concepts that every Calc user should know.

Perhaps you are expecting that dates are to be entered according to the same DD-MMM-YY style you have specified for display. That is not how Calc works. Read the links above.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Mac FAQ

[b.mckerchar]

My locale is Ballito in South Africa.

[robleyd]

OpenOffice doesn't have such a locale; there is English (South Africa). The setting for locale, on Mac, is under OpenOffice | Preferences | Language Settings | Languages as mentioned in the locale link MrProgrammer provided. What do you see there? From the symptoms you are reporting, I'd guess English (USA)

[b.mckerchar]

My user Interface is set as English (USA) with another option Default - English (USA) in the drop down. The Locale setting is English South Africa

[b.mckerchar]

I also tried changing the User Interface to Default - English (USA), but the same date format issue displays

[Nick N,]

Dear Mr. Mc Kerchar,

May I ask you if you did try to enter the date in the cell first and then right/click for the menu?

Awaiting your reply

Sincerely yours

Nick

[b.mckerchar]

I entered the date as 8/2 expecting 8 Feb, but the result is 2 Aug. When I right click on the cell followed by Date, under Category in the Format Cells window the date under Format is displayed as 31-12-99. The bottom of the Format Cells window states shows the format code as user defined with the format DD-MMM-YY.

[Nick N,]

Pardon me Sir,

I was trying to find the words you would agree, i.e. if you right click on the cell, a small window will pop up, offering you several choices. Please be so kind to choose format and a panel will show up. Then you could scroll the central little window and apply for the MM-DD-YYYY format if you fancy....

Regards

Nick

[Villeroy]

The locale is the relevant setting. You can set it for the entire office document, for cells and cell ranges, text fields, for anything numeric in templates for documents.
How to enter a date in a spreadsheet cell (Calc, Excel and others):
1/ enters the first day of current month
1/2/ enters this year's first of February or second of January in US-English locale context.
1/2/3 enters first of February 2003 or second of January in US-English locale context.
The cell may be formatted to show "Saturday, February 1 in the holy year 2003" or simply 1/2/03 or 37635 (which is the actual value of the cell) or $37'635.00. It is the exact same value regardless of number format.

[b.mckerchar]

Nick, perhaps I didn't explain myself very clearly and I attach a screenshot of what everything looks like from my end

[Mountaineer]

You explained precise, but you look at the wrong place, as your problem

... when entering 7 February 2024 as 7/2, the result is returned as 2 July 2024. ...

is not the formatting ( DD MMM YYYY for 2 July 2024) but wrong recognition/interpretation of abbreviated input as M/D, while you would prefer D/M.

After your input was wrongly stored as a representation of July, 2nd formatting may change July to Jul or 07, but never you will get your desired Feb, 7th.

[robleyd]

I can confirm this behaviour; using either English (South African) or Afrikaans (South Africa) as locale gives the same result. AOO 4.1.15 on Linux as per my sig.

If you input 7/2 into a default formatted cell you get 24/07/02 in the cell and 2024/07/02 in the input box.

As a workaround, perhaps set your locale to something that gives you the desired date format - one of the other English ...- and set your required currency instead of accepting the default for the selected locale. You might also have to fiddle with the decimal separator.

I have a vague recollection of seeing something elsewhere about an oddity with ZA locales, but I haven't been able to find it yet. Of course, old age may be playing with my memory

Edit: 15 minutes later: Aha, I'm not quite senile yet. https://bz.apache.org/ooo/show_bug.cgi?id=22190

[Nick N,]

Dear Mr. Kerchar,

The subject is getting hotter and hotter.

I do not see any problem with my LO version, as shown below.

Regards

Nick

[robleyd]

Please note that we are discussing an issue with the locale English (South Africa) on Apache OpenOffice. For the record the same issue with date formats using the English (South Africa) locale can be seen in LibreOffice 7.6.4.2.

Your screen capture appears to show an English (USA) locale judging by the default date formats.

[Nick N,]

Dear Mr. Robleyd,

Hereafter English RSA Format.

Regards

Nick

[b.mckerchar]

Thank you all, resolved by changing my Locale.