Date Format

[KathyNeedsHelp]

I have copied data from a Word table into and Excel worksheet. One column
contains dates from the mid-1800's to the 2000's. In Word they mostly look
like dd-mmm-yyyy. However, not all cells are populated completely and may
only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy.

I need to keep have an excel format of dd-mmm-yyyy. However, when I put
this format on the column I get the following:

Original WORD Data: 7-Aug-1915
Excel Data (pre formatting): 7-Aug-15
Excel Data (post formatting): 7-Aug-1915

Original WORD Data: 1909
Excel Data (pre formatting): 1909
Excel Data (post formatting): 23-Mar-1905

How can I force Excel to use 4 digits in the year without loosing my data
that does not include the complete date, but only a partial date?

I thank you for your help.

 

[Sean Timmons]

If you click on the cell that shows 1909, what shows in the formula bar?

 

[Dave Peterson]

To excel, all dates are just numbers--a count of days since a starting point.
Most wintel users use Jan 1, 1900.

So when you format 1909 as a date, you're really counting 1909 days since Jan 1,
1900. And that turns out to be about 5 years later (Mar 23, 1905).

If you want to convert that number to a real date, you could use a helper column
and a formula like:
=date(a1,1,1)
(January 1, of the year in A1).

The other problem you have is that your dates before that starting date
(pre-1900) won't be treated as dates by excel.

But John Walkenbach has some utilities that may help you:
http://spreadsheetpage.com/index.php/tip/extended_date_functions/

I have copied data from a Word table into and Excel worksheet. One column
contains dates from the mid-1800's to the 2000's. In Word they mostly look
like dd-mmm-yyyy. However, not all cells are populated completely and may
only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy.

I need to keep have an excel format of dd-mmm-yyyy. However, when I put
this format on the column I get the following:

Original WORD Data: 7-Aug-1915
Excel Data (pre formatting): 7-Aug-15
Excel Data (post formatting): 7-Aug-1915

Original WORD Data: 1909
Excel Data (pre formatting): 1909
Excel Data (post formatting): 23-Mar-1905

How can I force Excel to use 4 digits in the year without loosing my data
that does not include the complete date, but only a partial date?

I thank you for your help.

 

[KathyNeedsHelp]

The erroneous date appears in the formula bar. So, in the 1909 example, I
would see 3/23/1905 in the formula bar.

I could live with this information going into Excel as text rather than
dates, but I don't seem

If you click on the cell that shows 1909, what shows in the formula bar?

I have copied data from a Word table into and Excel worksheet. One column
contains dates from the mid-1800's to the 2000's. In Word they mostly look
like dd-mmm-yyyy. However, not all cells are populated completely and may
only contain data that looks like yyyy or [ca yyyy] or even [dd-mmm-]yyyy.

I need to keep have an excel format of dd-mmm-yyyy. However, when I put
this format on the column I get the following:

Original WORD Data: 7-Aug-1915
Excel Data (pre formatting): 7-Aug-15
Excel Data (post formatting): 7-Aug-1915

Original WORD Data: 1909
Excel Data (pre formatting): 1909
Excel Data (post formatting): 23-Mar-1905

How can I force Excel to use 4 digits in the year without loosing my data
that does not include the complete date, but only a partial date?

I thank you for your help.