Date issues Closed

Question

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

ac3mark · Answer

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

DemetriRsa · Answer

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.

ac3mark · Answer

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

DemetriRsa · Answer

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

ac3mark · Answer

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

DemetriRsa · Answer

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

ac3mark · Answer

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?

Date issues

7 responses

Subscribe To Our Newsletter!