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.

 

[pmich]

I tried =MID(C10,1,2)&"-"&TEXT(MID(C10,4,3),"mmm")&"-"&B10 but instead of 21-Mar-2017, the result is 42-Jan-2017.

Why am I getting wrong result?
Please suggest correct solution.
Thanks.

 

[Gerald Higgins]

For "S. No" 1, you seem to be assuming that the DATE column is in the format MMDD, to be converted into 18th April.
For "S. No" 490, you seem to be assuming that the DATE column is in the format DDMM, to be converted into 14th September.
For "S. No" 489, you seem to be assuming that the DATE column is in the format DDMM, the same as S. No 490, to be converted into 11th September, but how do you know that is shouldn't be MMDD, the same as S. No 1, for conversion into 9th November ?

 

[pmich]

You are right. I never thought that way. Thanks for pointing this out.
I am sorry for my delayed response. I was trying various methods, but in vain.

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


DD-MM-YYYY
18-10-2016


MM/DD
10/26


DD-MM
2-Nov


After filtering like this, what should I do to get my results?
Please suggest for each category.
I know some VBA. I have written and am still writing some VBA code. I am also helping some enquirers here with my solutions. But I am not an expert. So I need your help -- either full code or some guidance to get my end results.
I have two files each with about 7000 records and so manually correcting the dates is tedious.
Thanking you in advance for sparing your time and expertise.

 

[Gerald Higgins]

I'm assuming that in ALL cases, your original data is in the form of TEXT STRINGS, and NOT Excel numbers formated as dates.
If you're not sure what I mean by this, post back for clarification.

If you able to filter your data out into the different categories, as described in post #4 , then you can address each category using a tailored formula, and no need for VBA.

For example, for the first category, and assuming that the year is in cell B1 and the MMDD is in cell C1 . . .

=date(b1,left(c1,2),right(c1,2))
and format the result with whatever date format you like.

If this works for you, you should be able to work out DDMM, and so on.
DD-MM-YYYY is a little more challenging, but can still be done by similar means, perhaps adding in the MID function.

Post back if you need more help.

 

[pmich]

You have saved my time! Thanks for your simple but useful suggestion. It works perfectly.
In SlNo 490, to convert using year 2016 and DDMM 1409, I edited your code as below:
=DATE(B4,RIGHT(C4,2),LEFT(C4,2))
The result is : 14-09-2016.


Sl No Year MMDD/DDMM Result Formula Used
1 2016 0418 18-04-2016 =DATE(B2,LEFT(C2,2),RIGHT(C2,2))
489 2016 1109 09-11-2016 =DATE(B3,LEFT(C3,2),RIGHT(C3,2))
490 2016 1409 14-09-2016 =DATE(B4,RIGHT(C4,2),LEFT(C4,2))
600 2016 1810 18-10-2016 =DATE(B5,RIGHT(C5,2),LEFT(C5,2))
631 2016 10/26 26-10-2016 =DATE(B6,LEFT(C6,2),RIGHT(C6,2))
654 2016 11/02 02-11-2016 =DATE(B7,LEFT(C7,2),RIGHT(C7,2))
1018 2017 22/03 22-03-2017 =DATE(B8,RIGHT(C8,2),LEFT(C8,2))


18-10-2016
In H9 I pulled the year using: =RIGHT(C9,4)
In I9 I pulled the month using: =MID(C9,4,2)
In J9 I pulled the date using: =MID(C9,1,2)
In K9 I got the result using your code: =DATE(H9,I9,J9)


If there is a short method to do this please inform me.
Moreover, kindly guide me regarding converting 2-Nov, 21-Mar, 6-Dec etc.,
Thanks in advance.

 

[Dave Patton]

Excel 2010
	B	C	D
17		18-10-2016	18-Oct-16
18	2016	2-Nov	2-Nov-16
19	2016	21-Mar	21-Mar-16
20	2016	6-Dec	6-Dec-16
21		6-Jan	6-Jan-18
22
1c

Cell Formulas
Range	Formula
D17	=C17+0
D18	=(C18&"-"&B18)+0
D19	=(C19&"-"&B19)+0
D20	=(C20&"-"&B20)+0
D21	=C21+0

Real dates are custom formatted dd-mmm-yy

 

[pmich]

Dave Patton,
Thanks.
But only =c21+0 is giving 6-Jan-2018 as result in the cell which is formatted as date with the pattern 14-Mar-2001 - English (United States).
Others are giving #VALUE as the result.
Please guide.

 

[Dave Patton]

Try the formulas cited with the years shown where applicable.
The solutions are for systems with regional setttings of dd/mm/yy.

Post #1 stated "I want to get a result as dd-mmm-yyyy."

 

[pmich]

In B1 I have year 2016.
In C1 I have year 2-Nov in the cell that is formatted as dd-mmm.
In D1 I posted the formula =(C1&"-"&B1)+0 and the result was #VALUE !
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-11-2016.
If there is a shortcut, kindly suggest me.
Thanks.


[TABLE="width: 50"]
<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]=(c1&""&b1)+0[/TD]
[TD][/TD]
[TD]=month(c1)[/TD]
[TD]=day(c1)[/TD]
[TD]=date(b1,f1,g1)[/TD]
[/TR]
</tbody>[/TABLE]