Format Cell

Discuss the spreadsheet application

Format Cell

Postby bsfrey » Tue Apr 30, 2019 7:14 pm

When I try to Format a Cell- Numbers - Date it defaults to 07/03/17 and I cannot change it.
OpenOffice 4.1.6 on Windows 10 Home
bsfrey
 
Posts: 4
Joined: Tue Apr 30, 2019 6:34 pm

Re: Format Cell

Postby Zizi64 » Tue Apr 30, 2019 7:20 pm

Maybe your date is not a numeric value, but it is a simple text (string).
Try the "Value highlighting " function from the menu.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8144
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Format Cell

Postby bsfrey » Tue Apr 30, 2019 7:28 pm

I am using Windows 10 Home and no matter how I enter a date I get the same results. I am not familiar with function option.
OpenOffice 4.1.6 on Windows 10 Home
bsfrey
 
Posts: 4
Joined: Tue Apr 30, 2019 6:34 pm

Re: Format Cell

Postby Villeroy » Tue Apr 30, 2019 8:06 pm

[Tutorial] Ten concepts that every Calc user should know (same as any spreadsheet of the past 30 years)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Format Cell

Postby bsfrey » Tue Apr 30, 2019 8:53 pm

I understand the concept and have been using Excel for many years. What I don't understand is why it will not except any date I put in instead it substitutes something altogether different than what I enter.
OpenOffice 4.1.6 on Windows 10 Home
bsfrey
 
Posts: 4
Joined: Tue Apr 30, 2019 6:34 pm

Re: Format Cell

Postby Zizi64 » Tue Apr 30, 2019 9:11 pm

What I don't understand is why it will not except any date I put in instead it substitutes something altogether different than what I enter.

Type-in the dates in ISO 8601 standard format (YYYY-MM-DD), then format them by a user defined Cell style.

https://en.wikipedia.org/wiki/ISO_8601
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8144
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Format Cell

Postby RusselB » Tue Apr 30, 2019 9:28 pm

Part of the problem is that Calc is relying on the locale reported by your computer to determine what date 07/03/17 is.
The options I can think of quickly are: July 3, 2017, Mar. 7, 2017, Mar. 17, 2007.
All of these are valid dates, but without knowing the date format that your computer is expecting, there's no way of determining which it is.
The dates you are entering are not being substituted, but are being displayed in a manner different from what you are entering.
If you think that the dates are being substituted, please give some examples showing the date that you entered and the date that you see displayed.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5296
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Format Cell

Postby Villeroy » Tue Apr 30, 2019 10:41 pm

bsfrey wrote:I understand the concept and have been using Excel for many years. What I don't understand is why it will not except any date I put in instead it substitutes something altogether different than what I enter.

you don't tell us if your cell has a text or number.
you don't tell us what you enter nor what you get.
I can enter dates, numbers and text exactly like I did in MS Excel some 20 years ago. There is hardly any difference between Calc and Excel.
20/ enters this months 20th day
20/7 enters this year's 20th July ( 7/20 in US context)
20/7/17 enters 20th July 2017 (7/20/17 in US context).
'20/7/17 (with leading apostrophe) enters a text with 5 digits and 2 slashes.
A cell that has been formatted with number format "@" treats any input as literal text.
=ISNUMBER(X99) tells if X99 has a number or not
Text is left bound. Numbers are right bound to the cell border.
menu:View>Highlight Values displays numbers in blue.
Only the last feature is Calc specific
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Format Cell

Postby bsfrey » Thu May 02, 2019 1:28 am

I went through the process and made screenshots. I could not make any small enough, it said the file is too big even for one screenshot.
So here are the steps I did and the results.
New spreadsheet: Highlighted A1 through A30, went up to top of menu, selected Format, Cells, Numbers, Date. Middle of screen far right says 05/18/03, Hit OK.
Go back to A1 entered 010298, Enter. A1 show 10298. Go back to A1 to highlight, up to top of menu, selected Format, Cells, Numbers, Date. Middle of screen far right now says
03/11/28, Hit OK. A1 now says 03/11/28. Go up to Input line and delete that and re-enter 010298. A1 now displays 10298 again.
Hope this is clearer to understand.
OpenOffice 4.1.6 on Windows 10 Home
bsfrey
 
Posts: 4
Joined: Tue Apr 30, 2019 6:34 pm

Re: Format Cell

Postby RusselB » Thu May 02, 2019 1:35 am

There is NOTHING in the sequence 010298 to tell Calc that you want it as a date. Use a - or a / to separate them.
Eg: 01-02-98 or 01/02/98
You could even enter it as Jan 2 98 or Feb 1 98 (depending on which of those you want)

If I enter 10298 into an unformatted cell, it shows as 10298
If I then format the cell as a date, it gives me the equivalent of Mar. 11, 1928
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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
RusselB
Moderator
 
Posts: 5296
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Format Cell

Postby Zizi64 » Thu May 02, 2019 8:07 am

If I then format the cell as a date, it gives me the equivalent of Mar. 11, 1928

Read: Mar. 11, 1928 is the 010298-th day relative to the base date, which is 1899 dec. 30. by default.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8144
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 32 guests