up vote 0 down vote favorite
share [g+] share [fb]

I have a fairly large spreadsheet and the dates column hasn't been locked down with an input mask/format and users have been using an incorrect dating scheme. Users are inputting dates as Apr 1/11 when they are supposed to be 1-Apr-11.

How could I go about using regex in excel to fix these values? This isn't just for the month of April, it is for several years and every month.

link|improve this question
92% accept rate
feedback

2 Answers

up vote 1 down vote accepted

looking at the .png in your comment to Dave, and assuming that the users were consistent in their input, the following should work. Put this into an empty cell

=DATEVALUE(SUBSTITUTE(A1644,"/",", "))

and then apply a date format to the results.

link|improve this answer
This worked, thanks! – qroberts Aug 24 '11 at 17:40
feedback
up vote 0 down vote

Excel stores dates as numeric values rather than strings. If you want to display them as 1-Apr-11, you shouldn't have to use a regex at all; just apply the desired date format to that column (d-mmm-yy in this case).

link|improve this answer
So select the cells in question, right click, format cells, and change the type to d-mmm-yy? It didn't work. i.imgur.com/sBORP.png Here's a sample of what's happening. – qroberts Aug 24 '11 at 17:15
note the little green triangles on each of those "bad" dates - they're not really dates, they're text strings that look (to us dumb humans) like dates. If they were to type APR 1,11 (or 4/1/11 or 4/1/2011) instead of APR 1/11, then Excel would know that they're really dates and treat them properly. – StevenV Aug 24 '11 at 17:29
I know this. The problem is to format them correctly so that excel recognizes that they are indeed dates. – qroberts Aug 24 '11 at 17:36
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.