Convert multiple date entries to DATE format

January 11, 2011 at 05:35:30
Specs: Windows 7
Eg: In the below table (Excel) right now date is numeric want to convert them to DATE format
Date of Birth Sex Date of diagnosis
1 1/07/41 F 21.02.09
2 12/06/90 M 2/24/2261
4 1/07/62 M 11/02/09
5 1/07/48 F 26.05.09
6 1/07/39 F 11.02.09
7 1/07/76 F 2.05.09
8 1/07/60 F 30.05.09
9 1/07/80 F 1.03.09
11 1/07/56 F 22.03.09
14 1/07/65 F 22.02.09
16 1/07/74 M 22.02.09
17 1/07/67 F 24.03.09
19 1/07/33 M 25.02.09
20 1/07/45 F 21.03.09

See More: Convert multiple date entries to DATE format

Report •


#1
January 12, 2011 at 00:17:58
Whoever wrote the file couldn't even decide on a standard date format. Needs two routines. I prefer to normalize to yyyy/mm/dd.

Report •

#2
January 12, 2011 at 00:38:02
Hey Fred...

Thanks .. and I agree with u... but at the moment i need to handle this with a VB code inserted so that i dont have to manually change the format..

And i am not very good at the same.. your help is highly appreciated
Regards,
Vidya


Report •

#3
January 13, 2011 at 05:39:50
Date of Birth Sex Date of diagnosis
2 12/06/90 M 2/24/2261

Time travel detected! (Also moving this to the Office forum)

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
January 13, 2011 at 08:01:57
I don't think you need VBA code, but there are some things that are unclear:

First, you didn't specify Column letters, so I am going to assume your values are in A:C. (Please read the How To referenced in my signature line to see the proper way to post data in this forum.)

Second, you didn't say it, but I am going to assume that the values in Column C are the only ones you are concerned about. The dates in Column A seem fine.

Third, is 2/24/2261 an error? As Razor2.3 intimated, there would need to be Time Travel involved for that to be a valid "Date of Diagnosis".

Fourth, I am going to assuming that the dates with dots are in the "format" of day.month.year since there is no month 22, 25, etc.

Fifth, the dates with the slashes are hard to make an assumption about since 2/24/2261 has to be month/day/year (since there is no month 24) but 11/02/09 could be month/day/year or day/month/year. For the purpose of this exercise I am going to assume that the format is month/day/year since that is the format of Column A.

Sixth, I am going to assume that you want the final output to be month/day/year since that is the format of Column A.

OK, after all that, here we go...

Put this in D1 and drag it down:

=IF(NOT(ISERR(MONTH(C1))),C1,IF(LEN(C1)=8,MID(SUBSTITUTE(C1,".","/"),4,3)&LEFT(SUBSTITUTE(C1,".","/"),3)&RIGHT(SUBSTITUTE(C1,".","/"),2),MID(SUBSTITUTE(C1,".","/"),3,3)&LEFT(SUBSTITUTE(C1,".","/"),2)&RIGHT(SUBSTITUTE(C1,".","/"),2)))

Here's how it works:

NOT(ISERR(MONTH(C1))) checks to see if the value is a valid date, e.g. 11/02/09. If it is, it simply returns that date.

If MONTH returns an error (#VALUE), the formula checks to see if the value is 8 characters long, meaning it has a 2 digit "day". If it's not 8 characters, it must have single digit date.

In either case, the SUBSTITUTE function replaces the dots with slashes and the RIGHT, LEFT and MID functions rearrange the strings to get it in the format of mm/dd/yy.

The LEN check is there to decide what values to use as the start_num arguments.


is use the MONTH function to check if the value is a valid date. If it is, the final value_if_true

This is the output I got when I used that formula:

       D
1    02/21/09
2    02/24/61
3    11/02/09
4    05/26/09
5    02/11/09
6    05/2/09
7    05/30/09
8    03/1/09
9    03/22/09
10   02/22/09
11   02/22/09
12   03/24/09
13   02/25/09
14   03/21/09


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 16, 2011 at 22:07:53
Dear Derby,

Thanks a ton for such a marvellous explanantion... I do agree the dates hav been messed up but thats how i hav received the file :-)

I tried the formula that you gave and it works ....:-) but my only concern is when i check the format of these dates(via Format cell option) it still shows G (general) rather than D (dates).

And in this document i need to have the dates to be in date format so that the file can be used for analysis purposes.

Thank you very much for all the effort,
Vidya


Report •

#6
January 18, 2011 at 15:26:08
Just select the cells and change the format to Date.

Excel might get quirky and not display the dates in the date format that you choose.

If that's the case, try this:

With the list of dates created by the formula in D1:D14, enter this in E1 and drag it down:

=D1+ 0

The dates in Column E should really be dates, formatted as such.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question