Ask Your Question

Column Formatting of cells in an .XLS Calc sheet that were originally time to number does not work in

asked 2018-03-25 16:10:51 +0100

JohnRB gravatar image

updated 2018-03-30 20:08:21 +0100

This problem started with the I have columns of cells formatted as number, general no decimal. When I enter new numbers from the keyboard the format of the cell changes to time. Is this a bug? If not, is there a way to prevent this from happening

edit retag flag offensive close merge delete


What exactly do yo enter in the cell, and what's the language?

m.a.riosv gravatar imagem.a.riosv ( 2018-03-25 18:44:04 +0100 )edit

Entering a 2 digit number (current pulse rate). If I view the cell properties before making the entry it is General-Number-no decimal places and 1 leading zero English. I enter my two digit number and it displays as 12:00 AM. When I check the cell format it has been changed to Time 1:37 PM English Display example 12:00 AM Format code H:MM AM/PM.

My sheet was working properly before the update to Only some columns have this problem, formatting the entire column is no help

JohnRB gravatar imageJohnRB ( 2018-03-25 19:15:53 +0100 )edit

Please can you share a sample file with the issue fot test?

m.a.riosv gravatar imagem.a.riosv ( 2018-03-26 01:04:01 +0100 )edit

This file is very large, over 7000 rows and it originated as and Excel file. I worked on trying to make a very small test file and discovered that the problem is not what I first though it was. It seems that after the install of the all of the unused rows in the problem columns had the format changed to time - I did not do this. When I first noticed the problem I formatted columns as numeric, However that is what does not work. Thus new entries display as time. Comtinued..

JohnRB gravatar imageJohnRB ( 2018-03-26 15:40:25 +0100 )edit

Formatting the cells individually works fine, but formattiing by entire column does not change format from time to numeric. I have a very small test file that fails but I do not see a way to attach. I can sent the test file if you provide a URL.

JohnRB gravatar imageJohnRB ( 2018-03-26 15:43:03 +0100 )edit

We don't need the whole file, I think with a coupe of rows with data it's enought to test.

m.a.riosv gravatar imagem.a.riosv ( 2018-03-27 01:44:56 +0100 )edit

That is what I did yesterday but I don't know where to send it. I don't see an option to attach a file. I have fixed my large file by selecting the unused cells and changing the format to number. It appears that the only thing that does not work is changing the cell format of the entire column.

JohnRB gravatar imageJohnRB ( 2018-03-27 15:19:28 +0100 )edit

I have upvoted your question so you can attach the file to the question.

m.a.riosv gravatar imagem.a.riosv ( 2018-03-27 17:20:53 +0100 )edit

Well I just loaded the test file and it does not fail after a save. However the columns that contained data formatted as time, still displayed as time. Doing a column format to numeric does not change the cells that are displaying time. I still do not see a way to attach a file, how do I do that?

JohnRB gravatar imageJohnRB ( 2018-03-27 22:00:15 +0100 )edit

You might not need my file. Open a new file, give it a name and save as .XLS. Now open the file and change the entire"A" column to Time 01:37 PM code HH:MM AM/PM. Enter 12 in the first 7 "A" rows and they should display as 12:00 AM. Now format cells A8 through A12 as numeric General no decimal place and enter 12 in A8 through A12. They should display as 12. Now format column A as numeric General no decimal. In my test file the format of cells A1-A7 still display as time, not numeric.

JohnRB gravatar imageJohnRB ( 2018-03-27 22:50:47 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-03-29 20:44:01 +0100

erAck gravatar image

Given the latest comment from JohnRB about fragments of a column formatted differently, that's the problem. The General format is displayed if the number formatter dialog is opened on a selection that has multiple number formats applied and the selection can't be changed to General in that case. Workaround is to format the selection once to a different format, for example 0, and then on the same selection invoke the dialog again and change the format to General.

However, data entry of plain numbers is not changed to time unless the cell was already formatted as Time. The title and original description of this question doesn't quite describe the problem.

edit flag offensive delete link more

answered 2018-03-30 20:07:54 +0100

JohnRB gravatar image

I agree, title was wrong, I have changed it. I hope that was the proper thing to do.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-25 16:10:51 +0100

Seen: 40 times

Last updated: Mar 30 '18