Date Cleanup Function

[kapzlok]

I am trying locate a function to cleanup dates in an excel spreadsheet so that all of the dates are in the following format: mm/dd/yyyy

Currently the formats vary, here are some examples:

1/1/97
01/04/92
3/12/1999

I wanted them to look like the following:

01/01/1997
01/04/1992
03/12/1999

 

[KenWright]

If they are real dates then simply select them all at once and format them as such using Format / Cells / Date...

Are they not stored as real dates?

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[byundt]

If your "dates" are text that looks like dates, then Ken's suggestion won't work until after you convert the text into numbers. To do this:
1) Copy a blank cell
2) Select the cells containing your dates, and Edit...Paste Special...Add. The cells should then turn into numbers like 37853
3) While the cells are still selected, use the Format...Cells...Number menu item to change the format to your desired mm/dd/yyyy format

Note: the suggested procedure assumes that your dates are already in the same form as your system date format--either m/d/yy or d/m/yy

Brad

 

[KenWright]

If not in system format, then just select all the data, use Data / Text To Columns, Fixed Width, take out any breaks and then pick the correct date format from the list of options.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

FYI

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[bluedragon2]

I'll tell you why Skip:

Forgot mom's birthDATE, which at the TIME was alright, until the DATE when I went to visit...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[kapzlok]

Thanks for all your help, I appreciate it.

 

[KenWright]

Mom is forgiving - The wife isn't!!!!!!!!

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Must I ALSO add Normalized Relationships to my FAQ? ;-)

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]