Excel interfering with dates

[Chris Watts]

How can I stop Excel 2007 interfering with dates?
I have a variety of dates to enter. Some are before 1900, some after. Some
are partial (with no year given); some are just a year. All are entered in
the form 1 Sep 1854 (with some parts missing).
I have defined the format of the cells to be Text/String but Excel ignores
that and corrupts the dates by assuming, if no year is present, that they
are 2011 and if they are after 1900 it converts them to 01/01/2011 format.
How can I force Excel to do what it is told and treat them all as Text?

 

[Ron Rosenfeld]

How can I stop Excel 2007 interfering with dates?
I have a variety of dates to enter. Some are before 1900, some after. Some
are partial (with no year given); some are just a year. All are entered in
the form 1 Sep 1854 (with some parts missing).
I have defined the format of the cells to be Text/String but Excel ignores
that and corrupts the dates by assuming, if no year is present, that they
are 2011 and if they are after 1900 it converts them to 01/01/2011 format.
How can I force Excel to do what it is told and treat them all as Text?

You need to format the cells as "Text" before you enter your strings. Doing that works properly on my Excel 2007. To check that you have done this properly, BEFORE you enter any data, on the "Home" ribbon in the "Number" section, it should say "Text" in the drop-down box, and not "General" or something else.

 

[Stan Brown]

How can I stop Excel 2007 interfering with dates?
I have a variety of dates to enter. Some are before 1900, some after. Some
are partial (with no year given); some are just a year. All are entered in
the form 1 Sep 1854 (with some parts missing).
I have defined the format of the cells to be Text/String but Excel ignores
that and corrupts the dates by assuming, if no year is present, that they
are 2011 and if they are after 1900 it converts them to 01/01/2011 format.
How can I force Excel to do what it is told and treat them all as Text?

By *entering* them as text, i.e. with a leading apostrophe.

 

[Chris Watts]

You need to format the cells as "Text" before you enter your strings.
Doing that works properly on my Excel 2007. To check that you have done
this properly, BEFORE you enter any data, on the "Home" ribbon in the
"Number" section, it should say "Text" in the drop-down box, and not
"General" or something else.

I thought that I had done just that.

Is there any way to change it after date has been entered? I am happy to
use vba to achieve it if necessary.

cheers
Chris

 

[Ron Rosenfeld]

I thought that I had done just that.

You need to be certain. I was not able to get Excel 2007 to change an entry to an "Excel date" if I formatted the cell as text before entering any data.

Did you check the cell, as I suggested in the last post, BEFORE you entered your data?

If you have a cell that is already formatted as TEXT, what happens if you replace the contents with 14-Jun, or something similar.

If, for some reason, you can't figure out what's going on, then prefix your date entries with a single quote -- that will force them to be treated as text.

Is there any way to change it after date has been entered? I am happy to
use vba to achieve it if necessary.

Pre-1900 dates, that were entered with a year, should be OK. Excel interprets them as text

Post 1900 dates will have been changed to a serial number, with 1 = 1 Jan 1900. So it'll be difficult to devise a robust algorithm to figure out where the entry represents a year vs a date early in the last century.

 

[Stan Brown]

Is there any way to change it after date has been entered? I am happy to
use vba to achieve it if necessary.

No. The original information has been lost, because Excel has
converted (its interpretation of) your input to a day number.

 

[Chris Watts]

You need to be certain. I was not able to get Excel 2007 to change an
entry to an "Excel date" if I formatted the cell as text before entering
any data.

Did you check the cell, as I suggested in the last post, BEFORE you
entered your data?

If you have a cell that is already formatted as TEXT, what happens if you
replace the contents with 14-Jun, or something similar.

If, for some reason, you can't figure out what's going on, then prefix
your date entries with a single quote -- that will force them to be
treated as text.



Pre-1900 dates, that were entered with a year, should be OK. Excel
interprets them as text

Post 1900 dates will have been changed to a serial number, with 1 = 1 Jan
1900. So it'll be difficult to devise a robust algorithm to figure out
where the entry represents a year vs a date early in the last century.

Thanks for the thoughts, Ron. [and for removing the address in my signature
that got left in by me by accident - spam awaited!!]

cheers
Chris

 

[Ron Rosenfeld]

Thanks for the thoughts, Ron. [and for removing the address in my signature
that got left in by me by accident - spam awaited!!]

cheers
Chris

Glad to help. I wish I could take credit for having thought about your address removal, but I was rather just quoting what I needed to respond in context.