[Solved] Date auto-formatting

Discuss the spreadsheet application
Post Reply
Wallaceman105
Posts: 3
Joined: Tue Jan 23, 2018 9:02 pm

[Solved] Date auto-formatting

Post by Wallaceman105 »

Hello, I'm new here, and making a spreadsheet for a customer service log for my store.
I'm trying to display dates as "1/23", but the software keeps adding the year. I don't want the year, and cannot find a setting to disable this unwanted autocorrection.

Help is appreciates
Last edited by Wallaceman105 on Wed Jan 24, 2018 9:38 pm, edited 1 time in total.
OpenOffice 4.1.1
Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Date auto-formatting

Post by RusselB »

While there isn't a way to disable this, you can set the format for the column to only show the month and day as you have indicated.
You can do that by right clicking on the column letter, located at the top of the column, then selecting Format cells from the pop-up menu
As the format you have specified isn't one of the built in options (or at least I couldn't find it on my version). then you need to click in the box marked Format code
In that box enter M/DD
That will apply the format you have specified to all cells in that column. All entries, both current and new will be displayed in that format.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Date auto-formatting

Post by MrProgrammer »

Wallaceman105 wrote:I don't want the year, and cannot find a setting to disable this unwanted autocorrection.
Read sections 2. Controlling how data is displayed and 3. Dates in cells in Ten concepts that every Calc user should know. Spreadsheets will be a mystery to you until you learn how they work. Hint: format the cells before you enter any dates. The tutorial will save you hours of frustration, so read it rather than just using the hint.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Wallaceman105
Posts: 3
Joined: Tue Jan 23, 2018 9:02 pm

Re: Date auto-formatting

Post by Wallaceman105 »

RusselB wrote:While there isn't a way to disable this, you can set the format for the column to only show the month and day as you have indicated.
You can do that by right clicking on the column letter, located at the top of the column, then selecting Format cells from the pop-up menu
As the format you have specified isn't one of the built in options (or at least I couldn't find it on my version). then you need to click in the box marked Format code
In that box enter M/DD
That will apply the format you have specified to all cells in that column. All entries, both current and new will be displayed in that format.
So this won't work if there are multiple different dates going in that column?
OpenOffice 4.1.1
Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Date auto-formatting

Post by FJCC »

It will display all dates as a month number and a day number. Be aware that the year information will still be embedded in the value of the cell, just not displayed.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Date auto-formatting

Post by acknak »

Wallaceman105 wrote:So this won't work if there are multiple different dates going in that column?
For numeric data that you want to do any kind of formatting or analysis with, no. Spreadsheets assume only one value in any single cell.

If you want to have multiple dates in a single cell, they will have to be plain text, and you'll have to manage all the formatting when you type them. If you ever want to sort the records by date, it's likely that you won't get a result that makes sense.

Solving this issue is a big reason why people often split record keeping into data storage (a database, one value in each cell) and data reporting (whatever you want to make it look nice). Trying to do both at the same time, in the same spreadsheet, leads to lots of chewing gum and bailing wire and headaches.
AOO4/LO5 • Linux • Fedora 23
Wallaceman105
Posts: 3
Joined: Tue Jan 23, 2018 9:02 pm

Re: Date auto-formatting

Post by Wallaceman105 »

FJCC wrote:It will display all dates as a month number and a day number. Be aware that the year information will still be embedded in the value of the cell, just not displayed.
Thank you very much for the help, this cleared it up!
acknak wrote:For numeric data that you want to do any kind of formatting or analysis with, no. Spreadsheets assume only one value in any single cell.

If you want to have multiple dates in a single cell, they will have to be plain text, and you'll have to manage all the formatting when you type them. If you ever want to sort the records by date, it's likely that you won't get a result that makes sense.

Solving this issue is a big reason why people often split record keeping into data storage (a database, one value in each cell) and data reporting (whatever you want to make it look nice). Trying to do both at the same time, in the same spreadsheet, leads to lots of chewing gum and bailing wire and headaches.
Really? I've never run into an issue with other spreadsheet software doing this, my boss just wanted us to use OO Calc on this for some reason. Seems like a lot of hoops for a simple formatting option.
OpenOffice 4.1.1
Windows 10
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Date auto-formatting

Post by acknak »

Wallaceman105 wrote:Really? I've never run into an issue with other spreadsheet software doing this, ...
I misunderstood what you want. I thought you wanted to record more than one date in a cell. As long as you only need one date per cell, it's manageable.
AOO4/LO5 • Linux • Fedora 23
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved!] Date auto-formatting

Post by Zizi64 »

Wallaceman105 wrote:
Really? I've never run into an issue with other spreadsheet software doing this, ...

I misunderstood what you want. I thought you wanted to record more than one date in a cell. As long as you only need one date per cell, it's manageable.
You can enter more than one date-like "value" into one cell, but it will be a STRING. You will not able calculete anything with them. (for example: you can not calculate sums, or differences based on the string formats.)

The Date-time values are numbers, but you can format them variously.

The integer part represent the elapsed days since the base date. It means: the Date-time value always has a year part, but in some sases - maybe - it equals to zero.
And it means: the Date value always has a Time part too, but in some cases - maybe - it equals to zero. The integer-like date-time values are named as Dates. The date-time values with 0 value in integer part named as Time.

For example:
2018-01-25 07:16:05 = 43125,3028403935
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Date auto-formatting

Post by Villeroy »

"Auto-formatting" would take place if the program would detect the type of number and then apply the same format that has been entered. Luckily, it does not work like this.
The program detects some special numeric input and if the cell has no explicit formatting, it applies a distinct number format for the detected input. Without any explicit formatting the general number format provides one number format for decimals, currencies, times, dates, boolean values.

[Tutorial] Ten concepts that every Calc user should know
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply