cannot change date format

[3Suk]

dear all,
in my worksheet, some of the fields cannot change the date format. When it display as TEXT as below :

PAYDATE
41926
16/6/2000 <====
16/6/2000 <====
13/7/2000 <====
36531

when display as my preferred format d/m/yyyy
PAYDATE
14/10/2014
16/6/2000
16/6/2000
13/7/2000
6/1/2000

any advice for changing the 3 'year 2000' format?
I tried to 'clear format' and then reapply but it didn't work.

Thanks
Patrick.

 

[Claus Busch]

Hi Patrick,

Am Tue, 14 Oct 2014 02:20:03 -0700 (PDT) schrieb 3Suk:

PAYDATE
41926
16/6/2000 <====
16/6/2000 <====
13/7/2000 <====
36531

try Data => TextToColumns => Fixed width and choose in step 3 of the
assistent for this column format Date dmy


Regards
Claus B.

 

[3Suk]

Hi Patrick,



Am Tue, 14 Oct 2014 02:20:03 -0700 (PDT) schrieb 3Suk:









try Data => TextToColumns => Fixed width and choose in step 3 of the

assistent for this column format Date dmy





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Claus,
great, it works. Thanks so much for the hep.
i have a further query on this problem : may i have the reason / concept behind this problem? a link of web help to describe is good for me to learn the concept. Thanks again.

Regards,
Patrick.

 

[Claus Busch]

Hi Patrick,

Am Tue, 14 Oct 2014 03:48:05 -0700 (PDT) schrieb 3Suk:

i have a further query on this problem : may i have the reason / concept behind this problem? a link of web help to describe is good for me to learn the concept. Thanks again.

sometimes it is hard for Excel to format text back to a number or a
date. If formatting does not work I use the workaround with
TextToColumn.


Regards
Claus B.

 

[3Suk]

To elaborate further, usually the problem is due to the entry being TEXT.

Excel stores dates as the number of days since 1/1/1900.



When data that looks like a date is entered into a cell, it will be recognized as TEXT if

the date is preceded with a single quote (or other non numeric character)

the cell was previously formatted as text (changing the formatting does not affect the text property of the entry)

the date is in a format not consistent with your Windows Regional Settings (set in Control Panel).



With regard to the latter, if that is going on, you may need to check theother dates.

For example, if your Windows Regional Settings show a date format of MDY,and you enter a date in Excel in a DMY format, the following occurs:



14/10/2014 --> becomes text since there is no 14th month

6/1/2014 --> gets translated to 1 June 2014 (and not 6 Jan 2014 as you might wish)



While the text-to-columns wizard can correct the text entry; it will not correct the mistranslated entry.



So you need to review how the data is getting into your worksheet, so that appropriate measures can be taken. If the data is being obtained from a text file; or possibly from a web page, the fix may be to IMPORT rather than OPEN (or paste) the file. By using the Import method, the text-to-columns wizard will open before the mistranslations occure, and you can select the dmy format at that time.

Ron, Claus,

Thanks so much for your replies.

Regards,
Patrick