Convert date entries with different formats as dd-mmm-yyyy.

[pmich]

I have a sheet with date entries which are not in particular format.
Year is in column B.
Date and month are in column B with different formats.
In some rows in date column the full date is in dd-mm-yyyy format.
I want to get a result as dd-mmm-yyyy.
Since there are different formats, if I have to use a formula for each format, please suggest me. I will convert set by set using the guidelines given by any expert.
[TABLE="width: 500"]
<tbody>[TR]
[TD]S. No.[/TD]
[TD]Year[/TD]
[TD]Date[/TD]
[TD]Expected Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016[/TD]
[TD]0418[/TD]
[TD]18-Apr-2016[/TD]
[/TR]
[TR]
[TD]489[/TD]
[TD]2016[/TD]
[TD]1109[/TD]
[TD]11-Sep-2016[/TD]
[/TR]
[TR]
[TD]490[/TD]
[TD]2016[/TD]
[TD]1409[/TD]
[TD]14-Sep-2016[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]2016[/TD]
[TD]1810[/TD]
[TD]18-Oct-2016[/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD][/TD]
[TD]18-10-2016[/TD]
[TD]18-Oct-2016[/TD]
[/TR]
[TR]
[TD]631[/TD]
[TD]2016[/TD]
[TD]10/26[/TD]
[TD]26-Oct-2016[/TD]
[/TR]
[TR]
[TD]653[/TD]
[TD]2016[/TD]
[TD]2-Nov[/TD]
[TD]2-Nov-2016[/TD]
[/TR]
[TR]
[TD]654[/TD]
[TD]2016[/TD]
[TD]11/02[/TD]
[TD]2-Nov-2016[/TD]
[/TR]
[TR]
[TD]1012[/TD]
[TD]2017[/TD]
[TD]21-Mar[/TD]
[TD]21-Mar-2017[/TD]
[/TR]
[TR]
[TD]1018[/TD]
[TD]2017[/TD]
[TD]22/3[/TD]
[TD]22-Mar2017[/TD]
[/TR]
[TR]
[TD]1789[/TD]
[TD]2017[/TD]
[TD]6-Dec[/TD]
[TD]6-Dec-2017[/TD]
[/TR]
</tbody>[/TABLE]
I tried several methods. But of no use.
In G2 i used =mid(c2,1,2) and got 04.
In H2 i used =mid(c2,3,2) and got 18.
In I2 i used =date(b2,g,h2) and got 18-Apr-2016. This cell is formatted as date with 14-mar-2001 US format .
But in slno 490, the third in this example, instead of 14-Sep-2016, the result is 9-Feb-2017.
And in slno 600, the fourth in this example, instead of 18-Oct-2016, the result is 10-Jun-2017.
Please help with right solution.
Thanking you.

 

[Rick Rothstein]

Now I think that, based on your query, I have to filter my data having similar entries as mentioned below.

MM DD
0418
1227
1015

DDMM
1109
1409
1810

I think you could possibly be setting yourself up to calculate incorrect dates which, unfortunately, I don't think there will be an answer to. Gerald touched on the problem, but from the above, I am wondering if you grasped to the full extent of the problem. This is the first three entries that you posted in Message #1 ...
[table="width: 500"]
[tr]
[td]S. No.[/td]
[td]Year[/td]
[td]Date[/td]
[td]Expected Result[/td]
[/tr]
[tr]
[td]1[/td]
[td]2016[/td]
[td]0418[/td]
[td]18-Apr-2016[/td]
[/tr]
[tr]
[td]489[/td]
[td]2016[/td]
[td]1109[/td]
[td]11-Sep-2016[/td]
[/tr]
[tr]
[td]490[/td]
[td]2016[/td]
[td]1409[/td]
[td]14-Sep-2016[/td]
[/tr]
[/table]
The value in question is the one I highlighted in red. I have no idea where your data came from, but from the variability of it, I would guess individuals typed them in. If the same person who entered the third row's values (day first, then month) also entered the second row's value, then I would agree with your expected date of 11-Sep-2016. However, if the person who entered the first row's values (month first, then day) also entered the second row's value, then I would contend he/she meant the date to be 09-Nov-2016. The problem that I mentioned at the beginning... you have no way of knowing what order the person was thinking about when he/she placed the value. You will have the same uncertainty about any date where the month and day numbers are less than 13. With the day you showed us, I can think of no way to reliably work out what the correct date should be for such values.

 

[pmich]

You are right, Mr. Rick Rothstein. I am actually helping my friend who is maintaining this database. The data entry was not done by one person. Each one entered in his / her own way, because the entry was not done using a programme. It was not monitored by anyone. But now, I am trying to modify the database with uniform data. While doing so, I have the liberty to convert 1109 as either Sep 11th or Nov 9th. Because the data is of old contacts and we need some date as the date on which an enquiry was received from that person. So, there wont be any problem if this date is in the format of either mmdd or ddmm. Because there is no proof to say whether it is Sep 11th or Nov 9th. But we definitely need a date as the contact date, since I have developed a VBA programme to sesarch and retrieve data to view. The date column is a must for this VBA programme. After correcting or converting all the dates, from now on data entry will be restricted to one format only though it is going to be done without a programme. (Later on I wish to provide ADD, EDIT and DELETE options in my VBA programme). So, I need some help by way of code or suggestions to write code. I am prepared to filter the database category-wise (dd-mm, mm-dd, dd-mmm, dd-mm-yyyy etc.,) in separate excel files and then follow the suggestions to convert each category. Any help will be much appreciated. As I have mentioned earlier, the database has been maintained not in the proper way and I myself do not have much VBA experience though I have given some suggestions to a few on this forum. Mr. Rick Rothstein, earlier I have received your valuable suggestions to my queries. Thanks for your help.

 

[pmich]

In B1 I have year 2016.
In C1 I have year 2-Nov in the cell that is formatted as d-mmm.
H1 is formatted as 14-Mar-2001 Eng United States

I used =MONTH(C1) in F1 and got the result 11.
I used =DAY(C1) in G1 and got the result 2.
I used =DATE(B1,F1,G1) in H1 and got the result 2-Nov-2016.

If there is a shortcut, kindly suggest me.
Thanks.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2016[/TD]
[TD]2-Nov[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MONTH(C1)[/TD]
[TD]=DAY(C1)[/TD]
[TD]=DATE(B1,F1,G1)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Short cut to what, exactly ?

 

[pmich]

I am using the following methods:
I used =MONTH(C1) in F1 and got the result 11.
I used =DAY(C1) in G1 and got the result 2.
I used =DATE(B1,F1,G1) in H1 and got the result 2-Nov-2016.
Is this okay or is there any other short method to get the result?
Kindly reply. Thanks in advance.

 

[Gerald Higgins]

Hi, the methods you have posted in post #15 seem pretty short already, and if they consistently give you the right results, then I'd settle for that.

 

[pmich]

Thanks, Gerald Higgins.
I grouped the data, applied the relevant formula and got the result.
Formula shown in F is typed in Col E.
I got the text form of the Formula in E by using =FORMULATEXT(C2) in F2.
A B C D E F
S.No. Yr Dt Expected Result Formula Used
1 2016 0418 18-Apr-2016 18-Apr-2016 =Date(B2,MID(c2,1,2),Mid(c2,3,2))


489 2016 1109 11-Sep-2016 11-Sep-2016 =Date(B4,Mid(c4,3,2),Mid(c4,1,2))
490 2016 1409 14-Sep-2016 14-Sep-2016 =Date(B5,Mid(c5,3,2),Mid(c5,1,2))
600 2016 1810 18-Oct-2016 18-Oct-2016 =Date(B6,Mid(c6,3,2),Mid(c6,1,2))


601 18-10-2016 18-Oct-2016 18-Oct-2016 =Date(Mid(c8,7,4),mid(c8,4,2),Mid(c8,1,2))


631 2016 10/26 26-Oct-2016 26-Oct-2016 =Date(B10,mid(c10,1,2),Mid(c10,4,2))
654 2016 11/02 2-Nov-2016 2-Nov-2016 =Date(B11,mid(c11,1,2),Mid(c11,4,2))


1018 2017 22/3 22-Mar-2017 22-Mar-2017 =Date(B13,mid(c13,4,2),Mid(c14,1,2))


653 2016 02-Nov 2-Nov-2016 2-Nov-2016 =Date(B15,Month(C15),Day(C15))
1012 2017 21-Mar 21-Mar-2017 21-Mar-2017 =Date(B16,Month(C16),Day(C16))
1789 2017 6/Dec 6-Dec-2017 6-Dec-2017 =Date(B17,Month(C17),Day(C17))

Thanks Dave Patton, Rick Rothstein and Gerald Higgins for guiding me.
Great support, indeed.
Hope what I have given above is acceptable.
I am getting my results.
Thanks for this FORUM.

 

[pmich]

Thanks, Gerald Higgins.
I grouped the data, applied the relevant formula and got the result.
Formula shown in F is typed in Col E.
I got the text form of the Formula in E by using =FORMULATEXT(C2) in F2.
---A--------B------C--------D-----------E--------------------F
[TABLE="class: grid, width: 618"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]S.No[/TD]
[TD]Year[/TD]
[TD]Date[/TD]
[TD]Expected[/TD]
[TD]Result2 by Formula[/TD]
[TD]Formula Text[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2016[/TD]
[TD]0418[/TD]
[TD="align: right"]18-Apr-16[/TD]
[TD="align: right"]18-Apr-2016[/TD]
[TD]=DATE(B2,MID(C2,1,2),MID(C2,3,2))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]489[/TD]
[TD]2016[/TD]
[TD]1109[/TD]
[TD="align: right"]11-Sep-16[/TD]
[TD="align: right"]11-Sep-2016[/TD]
[TD]=DATE(B4,MID(C4,3,2),MID(C4,1,2))[/TD]
[/TR]
[TR]
[TD]490[/TD]
[TD]2016[/TD]
[TD]1409[/TD]
[TD="align: right"]14-Sep-16[/TD]
[TD="align: right"]14-Sep-2016[/TD]
[TD]=DATE(B5,MID(C5,3,2),MID(C5,1,2))[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]2016[/TD]
[TD]1810[/TD]
[TD]18-Oct-2016[/TD]
[TD="align: right"]18-Oct-2016[/TD]
[TD]=DATE(B6,MID(C6,3,2),MID(C6,1,2))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD] [/TD]
[TD]18-10-2016[/TD]
[TD]18-Oct-2016[/TD]
[TD="align: right"]18-Oct-2016[/TD]
[TD]=DATE(MID(C8,7,4),MID(C8,4,2),MID(C8,1,2))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]631[/TD]
[TD]2016[/TD]
[TD]10/26[/TD]
[TD]26-Oct-2016[/TD]
[TD="align: right"]26-Oct-2016[/TD]
[TD]=DATE(B10,MID(C10,1,2),MID(C10,4,2))[/TD]
[/TR]
[TR]
[TD]654[/TD]
[TD]2016[/TD]
[TD]11/02[/TD]
[TD]2-Nov-2016[/TD]
[TD="align: right"]2-Nov-2016[/TD]
[TD]=DATE(B11,MID(C11,1,2),MID(C11,4,2))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1018[/TD]
[TD]2017[/TD]
[TD]22/3[/TD]
[TD]22-Mar-2017[/TD]
[TD="align: right"]22-Mar-2017[/TD]
[TD]=DATE(B13,MID(C13,4,2),MID(C13,1,2))[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]653[/TD]
[TD]2016[/TD]
[TD]02-Nov[/TD]
[TD]2-Nov-2016[/TD]
[TD="align: right"]2-Nov-2016[/TD]
[TD]=DATE(B15,MONTH(C15),DAY(C15))[/TD]
[/TR]
[TR]
[TD="align: right"]1012[/TD]
[TD]2017[/TD]
[TD]21-Mar[/TD]
[TD]21-Mar-2017[/TD]
[TD="align: right"]21-Mar-2017[/TD]
[TD]=DATE(B16,MONTH(C16),DAY(C16))[/TD]
[/TR]
[TR]
[TD="align: right"]1789[/TD]
[TD]2017[/TD]
[TD]6/Dec[/TD]
[TD]6-Dec-2017[/TD]
[TD="align: right"]6-Dec-2017[/TD]
[TD]=DATE(B17,MONTH(C17),DAY(C17))[/TD]
[/TR]
</tbody>[/TABLE]

Thanks Dave Patton, Rick Rothstein and Gerald Higgins for guiding me.
Great support, indeed.
Hope what I have given above is acceptable.
I am getting my results.
Thanks for this FORUM.