[Solved] Date auto-formatting
-
- Posts: 3
- Joined: Tue Jan 23, 2018 9:02 pm
[Solved] Date auto-formatting
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
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
Windows 10
Re: Date auto-formatting
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.
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.
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.
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Date auto-formatting
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.Wallaceman105 wrote:I don't want the year, and cannot find a setting to disable this unwanted autocorrection.
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).
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).
-
- Posts: 3
- Joined: Tue Jan 23, 2018 9:02 pm
Re: Date auto-formatting
So this won't work if there are multiple different dates going in that column?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.
OpenOffice 4.1.1
Windows 10
Windows 10
Re: Date auto-formatting
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Date auto-formatting
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.Wallaceman105 wrote:So this won't work if there are multiple different dates going in that column?
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
-
- Posts: 3
- Joined: Tue Jan 23, 2018 9:02 pm
Re: Date auto-formatting
Thank you very much for the help, this cleared it up!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.
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.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.
OpenOffice 4.1.1
Windows 10
Windows 10
Re: Date auto-formatting
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.Wallaceman105 wrote:Really? I've never run into an issue with other spreadsheet software doing this, ...
AOO4/LO5 • Linux • Fedora 23
Re: [Solved!] Date auto-formatting
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.)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.
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.
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.
Re: [Solved] Date auto-formatting
"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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice