Date Format

[shahfahad]

Hello Excel Geniuses!

I have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:

=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))

Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result. Kindly help me out..

These are the type of dates:

[TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl63, width: 92"]12/10/2013
[TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]12/9/2013
[TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]9/11/2013
[TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]9/9/2013
[TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl65, width: 92"]7/25/2013

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shadow12345]

when you say they aren't being converted... what do you mean? did you try =text(b218,"dd-mm-yyyy") ?

 

[dispelthemyth]

try the below

=TEXT(LEFT(B218,FIND("/",B218,1)-1),"00")&"/"&TEXT(MID(B218,FIND("/",B218,1)+1,FIND("/",MID(B218,FIND("/",B218,1)+1,LEN(B218)),1)-1),"00")&"/"&RIGHT(B218,LEN(B218)-FIND("/",B218,1)-FIND("/",MID(B218,FIND("/",B218,1)+1,LEN(B218)),1))

Edit: Or as Shadow states above

 

[shahfahad]

Both formulas are not working..

By not working i mean that they stay in this format 12/31/2013. however i want them in this format 31-Dec-13. I tried to change its formatting, applied the text formula and everything but it is not working..

I can mail you the file if you give me your email address. I dont think we can attach file at the forum

 

[dispelthemyth]

=text(mid(b218,find("/",b218,1)+1,find("/",mid(b218,find("/",b218,1)+1,len(b218)),1)-1),"00")&"-"&text(left(b218,find("/",b218,1)-1),"00")&"-"&right(b218,len(b218)-find("/",b218,1)-find("/",mid(b218,find("/",b218,1)+1,len(b218)),1))

 

[shahfahad]

the formula gets the value..but it is putting it in this format..12-10-2013..it is not changing to 10-Dec-13..

 

[dispelthemyth]

=text(mid(b218,find("/",b218,1)+1,find("/",mid(b218,find("/",b218,1)+1,len(b218)),1)-1)&"-"&left(b218,find("/",b218,1)-1)&"-"&right(b218,len(b218)-find("/",b218,1)-find("/",mid(b218,find("/",b218,1)+1,len(b218)),1)), "dd-mmm-yyyy")

 

[shadow12345]

Or add this function

Public Function ChangeDateFormat(inputString As String) As String
  Dim firstDate As Date

  Dim trimmedInput As String
  trimmedInput = Trim$(inputString)
  firstDate = DateValue(Left$(trimmedInput, 10))


  ChangeDateFormat = Format(firstDate, "dd-mmm-yyyy")
End Function

then just type =changedateformat(b218)

 

[shahfahad]

VBA Code is not giving correct dates on all instances. It is giving 11-Mar-13 as 3-Nov-13...

 

[shahfahad]

Thank you all. I figured out the solution.

=IFERROR(IF(LEN(B2)=10,(MID(B2,4,2)&"/"&LEFT(B2,2)&"/"&RIGHT(B2,4))+0,IF(LEN(B2)=9,(MID(B2,3,2)&"/"&LEFT(B2,1)&"/"&RIGHT(B2,4))+0,IF(LEN(B2)=8,(MID(B2,3,1)&"/"&LEFT(B2,1)&"/"&RIGHT(B2,4))+0,""))),(MID(B2,4,1)&"/"&LEFT(B2,2)&"/"&RIGHT(B2,4)+0))+0