Date issues [Closed]

Report
Posts
4
Registration date
Sunday February 16, 2014
Status
Member
Last seen
February 16, 2014
-
 Blocked Profile -
Hello,

i have the following problem with excel 2010 on windows 7 pro.

i select col A and format it to Date yyyy-MM-dd

i enter 2014-01-08 in A1 and it places the entry to the left of the cell making it a text.
i then enter =A1+1 in A2 and i get #value! (confirming the text cell)

i then enter 1 on A5 and it displays 1900-01-00
entering =A5+1 in A6 returns 1900-01-01 (date format)


entering any integer into the column you get a date displayed and can do calculations on it.

entering a date in any format (eg. 2014-01-08, 2014-1-8, 1-8-2014, 1/8/2014 just displays as text and no calculations can be done on it.

if i copy a date cell from a different workbook (.xls-office 2000) it copies as a date and you can do calculations on it.

windows is set to English UK (US keyboard) and excel is set to English UK

i have un-installed MSO and then re-installed, but nothing changes.

any ideas would be appreciated.

thank you

7 replies


Hang in there.

Let us think about it!

Ok, first question: How did you format the WHOLE row into the yyyy-mm-dd format?

Second: Does it have to be in that format? Why?


Everything that you have described, is true and I agree, as the software is operating normally. If you were to format the whole column into a date, and then put a 1 value into a cell, then the system will convert 1 into a date, which in this case is the system start of 1-1-1900.

See Below. It works for me. Do not format your whole column in to date.




"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Posts
4
Registration date
Sunday February 16, 2014
Status
Member
Last seen
February 16, 2014

hi Mark and TY

i cannot add a picture here to show you, however, any new blank sheet should convert an entry of eg. 2/2 to your current date format and not into text.

You are welcome, and any time.

Your date format has changed from the original question, but anyway.....

Open a new worksheet, and see if it stills does what you talk about, or if you get a different result.

This is what I get when I open a new worksheet, and format the whole column of A as shown. It is a date, and not text. The only way I get a text value, is if I format it as text.



"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Posts
4
Registration date
Sunday February 16, 2014
Status
Member
Last seen
February 16, 2014

Mark

it makes no difference if the workbook is new or existing

anything that resembles a date displays as text.

the 2/2 was just an example not a variation.

i can see in your picture above that your 2/2 is displaying on the right of the cell, hence a date.

mine display on the left only.

i am starting to think that this may be a windows 7 issue rather.

i just have never seen this before in 30 years in the IT industry.

MS tells me that there is a problem.

Demetri

The only other cause beside problem, would be a "preference" for new sheets. Check your preferences. Maybe try to "repair" the program. Go to help, and repair



I am curious!

Thanks.

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon
Posts
4
Registration date
Sunday February 16, 2014
Status
Member
Last seen
February 16, 2014

Mark i have done a repaired 2 times and un installed Office and re installed 1 time.

Demetri,

Does it operate the smae way AFTER you format the column into a DATE?

If it does, then Yes You have a hacked up load of excel, get an update or get your money back!

I am at a loss, as it is more then one possible cause. You have have already looked at the requirements and compatabilites, so that isn't it.

Maybe missing a library build because of 32/64 bit? Try to run in compatibilty mode?