Date stored mixed in two different formats to convert to One single DD-MM-YYYY format..

[kundanlal]

Dear Sir,

Please help.

I have file with one column having 2 type of dates in mixed sequence.

1. One type Stored as "General" format. I need to convert this to actual date format like "dd/mmm/yyyy".. Can any one help in this..

The present data stored as under

4/15/2015 12:00:00 AM
11/30/2015 12:00:00 AM
1/27/2016 12:00:00 AM

2. Another type Stored as "custom format dd-mm-yyyy h:mm", data as under :

01-04-2016 0:00
12-08-2015 0:00

Actually there is some error in entire data.

I need to have these dates a 4-Jan-2016, 8-Dec-2015 with only one formula (i.e. "DD/MMM/YYYY") to be applied across column.

How I convert.

Thanks and Regards..

Kundanlal

[hemesh]

it would be better if you could upload a sample book

[FDibbins]

It sounds like those are not really dates, but text that looks like dates - if that is the case, no amount of formatting will change that. Test them with =isnumber(cell-ref)...FALSE indicates they are text.

See if this will help...

	A	B
5	4/15/2015 12:00:00 AM*	15/Apr/2015
6	11/30/2015 12:00 AM	30/Nov/2015
7	1/27/2016 12:00 AM	27/Jan/2016
8	1/4/2016 12:00 AM	04/Jan/2016
9	12/8/2015 12:00 AM	08/Dec/2015

B5=IF(ISTEXT(A5),--LEFT(A5,FIND(" ",A5)-1),A5)
copied down

Then just format as needed

[shukla.ankur281190]

try

B1=IFERROR(DATEVALUE(MID(LEFT(A1,LEN(A1)-3),FIND("/",LEFT(A1,LEN(A1)-3))+1,2)&"/"&LEFT(LEFT(A1,LEN(A1)-3),FIND("/",LEFT(A1,LEN(A1)-3))-1)&"/"&MID(LEFT(A1,LEN(A1)-3),FIND(" ",LEFT(A1,LEN(A1)-3))-4,4)+0),A1)

[kundanlal]

Thanks for help and suggestions..

I have tried solution given by Mr. Shukla, it works properly on those cells which are stored as General.. I am attaching sample file where cells highlighted in Yellow shows wrong answers..

Kindly help here as well..

regards..

Kundanlal

[shukla.ankur281190]

What would be expected result on highlighted cells??

Once try

=IFERROR(DATEVALUE(MID(LEFT(A3,LEN(A3)-3),FIND("/",LEFT(A3,LEN(A3)-3))+1,2)&"/"&LEFT(LEFT(A3,LEN(A3)-3),FIND("/",LEFT(A3,LEN(A3)-3))-1)&"/"&MID(LEFT(A3,LEN(A3)-3),FIND(" ",LEFT(A3,LEN(A3)-3))-4,4)+0),(MONTH(A3)&"/"&DAY(A3)&"/"&YEAR(A3))+0)

or

=IFERROR(DATEVALUE(MID(LEFT(A3,LEN(A3)-3),FIND("/",LEFT(A3,LEN(A3)-3))+1,2)&"/"&LEFT(LEFT(A3,LEN(A3)-3),FIND("/",LEFT(A3,LEN(A3)-3))-1)&"/"&MID(LEFT(A3,LEN(A3)-3),FIND(" ",LEFT(A3,LEN(A3)-3))-4,4)+0),(DAY(A3)&"/"&MONTH(A3)&"/"&YEAR(A3))+0)

[kundanlal]

Perfect..When applied for both the formats, the output is correct.. Many Thanks..

[shukla.ankur281190]

Glad it helps you and thanks for feedback.

If you satisfy with any solution please add reputation by clicking * star icon left corner on your screen and mark thread as solved

[hemesh]

another way may be a little shorter approx 89 character short
copy paste below in b3 and drag down

=IFERROR(DATE(YEAR(A3),DAY(A3),DAY(MONTH(A3))),DATEVALUE(MID(A3,FIND("/",A3)+1,2)&"/"&LEFT(A3,FIND("/",A3)-1)&"/"&RIGHT(LEFT(A3,FIND(" ",A3)-1),4)))

[FDibbins]

Did you even try my suggestion? It seems to work on all your dates?

	A	B	C	D
2	TRDT
3	6/4/2015 0:00	4/Jun/15	6/4/2015 0:00	6/4/2015 0:00
4	4/13/2015 12:00:00 AM	4/13/2015 12:00:00 AM	4/13/2015 0:00	4/13/2015 0:00
5	4/15/2015 12:00:00 AM	4/15/2015 12:00:00 AM	4/15/2015 0:00	4/15/2015 0:00
6	4/16/2015 12:00:00 AM	4/16/2015 12:00:00 AM	4/16/2015 0:00	4/16/2015 0:00
7	4/17/2015 12:00:00 AM	4/17/2015 12:00:00 AM	4/17/2015 0:00	4/17/2015 0:00
8	4/27/2015 12:00:00 AM	4/27/2015 12:00:00 AM	4/27/2015 0:00	4/27/2015 0:00
9	4/28/2015 12:00:00 AM	4/28/2015 12:00:00 AM	4/28/2015 0:00	4/28/2015 0:00
10	4/29/2015 12:00:00 AM	4/29/2015 12:00:00 AM	4/29/2015 0:00	4/29/2015 0:00
11	4/30/2015 12:00:00 AM	4/30/2015 12:00:00 AM	4/30/2015 0:00	4/30/2015 0:00
12	5/5/2015 0:00	5/May/15	5/5/2015 0:00	5/5/2015 0:00
13	6/5/2015 0:00	5/Jun/15	6/5/2015 0:00	6/5/2015 0:00
14	5/19/2015 12:00:00 AM	5/19/2015 12:00:00 AM	5/19/2015 0:00	5/19/2015 0:00

[kundanlal]

Yes Sir.. I tried this as well.. But at my end I am getting some error .. output was as under

04-09-2015 0:00 4-Sep-15 It correctly converted for this input
4/16/2015 12:00:00 AM #VALUE! Showing error here..

[FDibbins]

hmm my sample that I posted here was copied from your file with my formula applied, see the attached

[kundanlal]

When I edited formula, it is giving error #Value!..

[hemesh]

@kundan Which formula You are referring to.

[kundanlal]

The one which is present in book1.xls, a solution provided by FDibbins..Thanks..

[hemesh]

Ok, what you are trying to change

[kundanlal]

When tried to use in another file it shows error. Also when tried to edit in same cell, it gives error..

[kundanlal]

=IF(ISTEXT(A11),--LEFT(A11,FIND(" ",A11)-1),A11)

4/30/2015 12:00:00 AM #VALUE! Gives error here when copied to another cell and tried to correct A11 as cell ref.
05-05-2015 0:00 05-05-2015 0:00 It do not give error here..

[FDibbins]

Which version of excel are you trying to use this on?

[kundanlal]

Which version of excel are you trying to use this on?

Excel 2007