Unifying different date formats in a spreadsheet
 

I inherited a spreadsheet with 2,285 rows; each row has dates typed into
them, and the dates are in three random formats, ie,

5-Aug-05
12/27/97
3-12-04

Is there a way, other than manually, to correct all the dates to one uniform
date format?

Unifying different date formats in a spreadsheet
 

You can just highlight all the cells you want to change (these can
include text values as these won't be affected) and then just Format |
Cells | Number tab and choose the date format you require.

Hope this helps.

Pete

On Nov 6, 10:06 pm, Bob W wrote:
I inherited a spreadsheet with 2,285 rows; each row has dates typed into
them, and the dates are in three random formats, ie,

5-Aug-05
12/27/97
3-12-04

Is there a way, other than manually, to correct all the dates to one uniform
date format?

Unifying different date formats in a spreadsheet
 

Try this macro:

Sub fixum()
s1 = "d-mmm-yy"
s2 = "mm/dd/yy"
s3 = "m-dd-yy"
For Each r In ActiveSheet.UsedRange
If r.NumberFormat = s1 Or r.NumberFormat = s2 Or r.NumberFormat = s3 Then
r.NumberFormat = "dd mmmm yyyy"
End If
Next
End Sub
--
Gary''s Student - gsnu200754


"Bob W" wrote:

I inherited a spreadsheet with 2,285 rows; each row has dates typed into
them, and the dates are in three random formats, ie,

5-Aug-05
12/27/97
3-12-04

Is there a way, other than manually, to correct all the dates to one uniform
date format?

Unifying different date formats in a spreadsheet
 

Thanks for both solutions - both are great, but alas, trying them reveals
that the crux problem I'm encountering is that Excel is adroitly warning me
(with a little green triangle highlight in the upper left corner of the
offending date cells) that all the dates that are formatted as"9/30/99",
"1/7/01", etc. must somehow have been originally entered as date strings with
only two digits for the year, so Excel apparently has no data to know whether
the century digits should be "19" or "20".

I have tried setting the format for those cells to some other date schema
but nothing happens - it doesn't change them.

I also looked at Excel Help where it suggests changing the Regional options
in Windows Control Panel but the default setting there, seems perfectly OK
for interpreting two-digit century dates.

Unless there's some simple hidden setting that needs to be changed, I think
the macro by Gary's Student may be the best solution, but it would need some
additional clever code to allow it to determine whether the two year digits
for each date should be preceeded by 19 or 20. I think the logic to get there
might be to say if the yy value is between 99 and (some arbitrary lower
value) then it insert a '19', otherwise if the yy value is 00 and above (up
to some arbitrary value) then insert a '20'.

Hopefully this interesting challenge will pique Gary's Student's creative
genius one more time...with my almost zero grasp of vbasic syntax, it could
easily take me a week or two to puzzle this out with a lot of help from
Barnes & Noble, black coffee, numerous 2AM cheeseburgers, random guesses, an
array of antacids, and a plelthora of creative cuss-word combinations.

"Gary''s Student" wrote:

Try this macro:

Sub fixum()
s1 = "d-mmm-yy"
s2 = "mm/dd/yy"
s3 = "m-dd-yy"
For Each r In ActiveSheet.UsedRange
If r.NumberFormat = s1 Or r.NumberFormat = s2 Or r.NumberFormat = s3 Then
r.NumberFormat = "dd mmmm yyyy"
End If
Next
End Sub
--
Gary''s Student - gsnu200754


"Bob W" wrote:

I inherited a spreadsheet with 2,285 rows; each row has dates typed into
them, and the dates are in three random formats, ie,

5-Aug-05
12/27/97
3-12-04

Is there a way, other than manually, to correct all the dates to one uniform
date format?