Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unifying two sheets by a common index column | Excel Discussion (Misc queries) | |||
how dio i copy page setup formats from 1 spreadsheet to another? | New Users to Excel | |||
Can I disply Euro and Dollar Formats in the same spreadsheet | Excel Discussion (Misc queries) | |||
Using external data with differing formats in a spreadsheet | Excel Discussion (Misc queries) | |||
How do I resolve too many different formats in a spreadsheet? | Excel Discussion (Misc queries) |