Date Format


K

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.
 
Ad

Advertisements

D

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.
 
Ad

Advertisements

K

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

Sean Timmons said:
If you click on the cell that shows 1909, what shows in the formula bar?

KathyNeedsHelp said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top