Multiple date formats - formatting

[phartpants]

Hi,</SPAN>

I need to run a regular report on around 250,000 rows of data from an application I don’t have permission to clean up the data from. Over time, this application has been populated by a lot of different people, without any validation rules, which means we have date fields in the following formats;</SPAN>

• m/dd/yyyy h:mm:ss AM/PM</SPAN>
• mm/dd/yyyy h:mm:ss AM/PM</SPAN>
• dd/mm/yyyy hh:mm</SPAN>

I would like all of these values to be stored in either of the following formats;</SPAN>

• dd/mm/yyyy hh:mm</SPAN>
• dd/mm/yyyy</SPAN>

Preference would be to include the time if possible, however that’s not critical.</SPAN>

I have tried to remove formatting and apply the date format I want, using text to columns and also tried converting them to numbers, but all of these only affect the data currently stored in the dd/mm/yyyy hh:mm formats. The others are unaffected.</SPAN>

As this is a weekly report, I need something a solution which takes little effort. Appreciate any tips people may have?</SPAN>

 

[MUKESHY12390]

hey .. I think you need to check out , it's value whether it is actual date or something like text .
Always concern with Value of cell, not what it's looks like..!
to check ....
copy and paste as value Or
Use formula : isnumber()
if this thing are not useful.... pls post little part of your Data...

Good Luck...

 

[phartpants]

@MUKESHY12390 no, the US format dates appear to be text and am unable to change them by altering the format.

For example, if I select the column and change to 'NUMBER' the Australian date fields all update to a decimal number, but the US date fields remain as text.

Here is a snippet:

[TABLE="width: 155"]
<TBODY>[TR]
[TD]1/20/2009 8:15:13 PM</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]11/05/2008 15:08</SPAN>[/TD]
[/TR]
[TR]
[TD]10/28/2008 4:34:45 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]1/28/2009 3:39:31 PM</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12/08/2008 20:02</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:29 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:32 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12/08/2008 20:02</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:33 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:34 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12/08/2008 20:02</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:35 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:36 PM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD]12/16/2008 12:00:00 AM</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]12/08/2008 20:02</SPAN>[/TD]
[/TR]
[TR]
[TD]12/15/2008 8:51:37 PM</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[MUKESHY12390]

now m wondering ...it's working on my sheet.. I just change the format to dd/mm/yyyy by using custom mode ..

[TABLE="width: 126"]
<colgroup><col></colgroup><tbody>[TR]
[TD]20/01/2009[/TD]
[/TR]
[TR]
[TD]05/11/2008[/TD]
[/TR]
[TR]
[TD]28/10/2008[/TD]
[/TR]
[TR]
[TD]28/01/2009[/TD]
[/TR]
[TR]
[TD]08/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]08/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]08/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]16/12/2008[/TD]
[/TR]
[TR]
[TD]08/12/2008[/TD]
[/TR]
[TR]
[TD]15/12/2008[/TD]
[/TR]
</tbody>[/TABLE]

 

[phartpants]

Well, that is weird. I am not able to do that before, or even after copying the above text into a new workbook. I have tried just changing to default date values and by using the cutom format but neither work.

Could you please advise which version of excel you have?

 

[MUKESHY12390]

ms excel 2007