Page 1 of 1

Time not displaying correctly

PostPosted: Sun Jul 21, 2019 5:07 pm
by RUSSELL044
I am using OpenOffice 4.1.6. I formatted column to display time as 9:00 AM. Going to format time, I click the format I want to use, ( 01:37 AM ). But when entering time into the cells, it displays as 9 A. This issue just started today. Up to now, it would display correctly. Even created a new worksheet formatting cells for time. Still same result. So would appreciate any advice on how this be corrected.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 6:28 pm
by FJCC
When you choose the format, what is the format code shown at the bottom of the dialog? It should be
HH:MM AM/PM

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 7:38 pm
by RUSSELL044
That is what I see in the format code box.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 7:44 pm
by RoryOF
Is the cell wide enough to display all characters of the time?

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 7:45 pm
by FJCC
Please upload a small file showing the problem. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 7:49 pm
by RusselB
You have stated what you see, but we can't actually see it.. unless you upload/attach a screenshot.
Even then, for diagnostic reasons, it's better for you to attach a copy of your spreadsheet, rather than a screenshot.
To attach a file (spreadsheet or screenshot), use the PostReply button, which will allow you access to the Upload attachment tab, which, in turn, will allow you to pick the file to attach to this topic.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 7:53 pm
by RUSSELL044
I think I was able to upload the file showing what it looks like.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 7:56 pm
by RUSSELL044
Here is the worksheet.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 8:10 pm
by FJCC
All of the cells in column C contain text, not numbers. You can see this by selecting the menu View -> Value Highlighting. Cells in black are text. If you enter 0.75 in C8, it will appear as 06:00 PM, as expected. Where did the contents of column C come from?

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 8:13 pm
by RusselB
The entries in your "time" column are text. They will not be affected by the number formatting.
In order to the number formatting to be applied, you have to enter the time as a number. eg: 7:00 for 7:00 AM, or 19:00 for 7:00 PM or 7:00 pm for 7:00 pm
Entering the information correctly, along with the settings you are showing, should resolve the problem

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 9:12 pm
by RUSSELL044
I am sorry, I just started using OpenOffice within the past few days. Have been using Microsoft Office 365. The data that is in column C was entered manually. For example: I would type 8:55 p for 8:55 PM, 9 a for 9:00 AM. I am sending the first worksheet I created a few last week.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 9:16 pm
by RoryOF
Just enter your times in full form: 08:55 AM rather than 8:55 a.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 9:21 pm
by RUSSELL044
Just thought of something, and am not sure this is the issue. I did a recovery couple days ago and had to re-download the software again and reinstall it.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 9:30 pm
by RoryOF
It's not the problem - enter your time in full format. OpenOffice is not an Excel clone.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 9:33 pm
by RUSSELL044
Thank you all for taking the time to respond to my question.

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 9:35 pm
by RusselB
If it was working for you using 8:55 a rather than 8:55 am, then it was, imo, a fluke, as the normal operation would take the 8:55 a as text, since the AM is missing to tell Calc that you are working with a time.
Just add the letter m at the end of each time entry, so that you get 8:55 am or 8:55 pm (rather than 8:55 a or 8:55 p) will resolve this problem and make your entries consistent with what Calc is expecting for a time entry

Re: Time not displaying correctly

PostPosted: Sun Jul 21, 2019 10:34 pm
by Villeroy
Another spreadsheet formatted to death.
Stop using any formatting attributes and take care of the cell values. Centered content makes it difficult to distinuguish numbers from text.

With completely unformatted cells and in US English context:
23: enters 11pm
11: enters 11 am
23/ enters this month's 23th day
12/23 enters this year's 23rd of December (US context only)

If the values are right, you may format them any way you like later. But first you need correct cell values before anything else.

Re: Time not displaying correctly

PostPosted: Mon Jul 22, 2019 12:23 pm
by Bill
RUSSELL044 wrote:I am sorry, I just started using OpenOffice within the past few days. Have been using Microsoft Office 365. The data that is in column C was entered manually. For example: I would type 8:55 p for 8:55 PM, 9 a for 9:00 AM.

MS Excel accepts "8:55 p" as a time value. AOO Calc and LO Calc do not. When using Calc, you have to enter the time as "8:55 pm".

Format numbers as dates or times

Re: Time not displaying correctly

PostPosted: Mon Jul 22, 2019 4:32 pm
by MrProgrammer
RUSSELL044 wrote:So would appreciate any advice on how this be corrected.
Select the cells, Edit → Find & Replace → More options
deselect Match casedeselect Entire cells → select Current selection only
→ Search for a → Replace with am → Replace All
→ Search for p → Replace with pm → Replace All.
I recommend unchecking Current selection only before clicking Close.

8L12 P appears to be a typing error; fix it manually.