Formula for changing date to custom d-mmm-yy

[UNAB]

Hi


I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy. 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either. Any help is appreciated.


Thank you

Una


http://rapidshare.com/files/805666827/FormatDates.xlsx or http://my.rapidshare.com/UNAB/15854

 

[Debraj]

Hi Betty!


In B7.. write formula as

=SUBSTITUTE(REPLACE(MID(A7,4,3)&A7,7,3,""),"-","/")+0

Regards,

Deb

 

[UNAB]

Hi Deb!!!


I get #VALUE! I must be doing something wrong?


Una

 

[Debraj]

Hi Una,


Try this also..

=REPLACE(MID(A7,4,3)&A7,7,3,"")+0


PS:

Do me a favor...


* Open Excel..>

* Press Alt + F11 >

* Press CTRL + G >

write 


? Application.International(xlDateSeparator)

Can you please confirm the output.. I guess "-"


Regards,

Deb

 

[Kevin@Radstock]

Hi UNAB


Nothing fancy required for that and no need for a formula!

Select the data A7:A350 > Data Tab > Text to Columns > Delimited > Next > Next > Tick the Date: & select MDY > Finish > Format as required.

Or if you really want a formula, in say B7 and copy down: =DATE(RIGHT(A7,4),LEFT(A7,2),MID(A7,4,2))

 

[UNAB]

Thanks Deb & Kevin!!!!!!!!!! I have been away from the formum trying to use the formulas that have been so graciously provided to try to facilitate the processing of over 1000 claims that need to be billing witin two days. I will try the formulas above as soon as possible.


Deb - when I try to follow your directions (I may not be doing it right) I get a Go To reference box - I put in ? Application.International(xlDateSeparator) wherever I could, nothing happened.


Thanks

 

[SirJB7]

Hi, UNAB!

Despite if Debraj Roy's instructions work for you or not, why not sticking to any of both methods from Kevin@Radstock that work fine?

Regards!