How to change a date format?

**jp001** · 09-27-2016, 03:55 AM

I've got some dates in the format Sep 27, 2016 which I want to convert to the format 27/9/2016
- it won't format using the Data Format command. Any help welcome!

**Special-K** · 09-27-2016, 05:06 AM

This works, it assumes first three characters will be month then space then date then comma then space then year as per your example date

=SUBSTITUTE(REPLACE(SUBSTITUTE(A1,",",""),1,4,"")," ","/"&TEXT(LEFT(A1,3)&"1","m")&"/")+0

**jindon** · 09-27-2016, 05:12 AM

Try this

1) Select Cell(s)
2) TextToColumns - Next - Next - Choose [Date] - YMD from the list. Then Finish.

**jp001** · 09-27-2016, 05:24 AM

Thanks guys, that substitute formula did the trick!

How to change a date format?

LinkBack

Thread Tools

Rate This Thread

Display

How to change a date format?

Re: How to change a date format?

Re: How to change a date format?

Re: How to change a date format?

Thread Information

Users Browsing this Thread

Similar Threads

Show Error when a cell with number format change to date format

[SOLVED] Date formula - Change Format to YYYYMMDD from Exported Format

[SOLVED] help on vba code to change the error date format as standard format mm/dd/yyyy

Excel:How change the textbox format from text to date format?

Macro to change date value from one date format to another date format

Not able to change date format into proper excel date format

Userform date format reverting back to us format on change event

Bookmarks

Bookmarks

Posting Permissions