Converting dates: YYYY-MM-DD into DD Month YYYY

**jacopo** · 10-21-2014, 06:01 PM

Hello,

This is my first post - sorry if it's in the wrong subforum.

I have a column of approximately 1300 historical dates that follow this format:

YYYY-MM-DD

I'd like to automatically turn them into text, i.e.:

1801-12-03 -> 03 December 1801

1832-01-22 -> 22 January 1832

1812-11-12 -> 12 November 1812

If the day begins with a zero, it would be helpful if I it could be automatically omitted, i.e. 3 December 1801 rather than 03 December 1801.

How would I do this in Excel 2010? I'm a novice user, so very clear instructions would be very much appreciated.

Thanks!

**TMS** · 10-21-2014, 06:10 PM

You'll have to do it with a formula. Excel doesn't recognise dates before 01/01/1900.

Try something like:

Formula:

Please Login or Register  to view this content.

for a date in cell A9

Regards, TMS

**TMS** · 10-21-2014, 06:11 PM

	A	B	C
1
2	1801-12-03 -> 03 December 1801
3
4	1832-01-22 -> 22 January 1832
5
6	1812-11-12 -> 12 November 1812
7
8
9	1801-12-03	3 December 1801	=--RIGHT(A9,2)&TEXT(DATE(1,MID(A9,6,2),1)," mmmm ")& LEFT(A9,4)
10	1832-01-22	22 January 1832
11	1812-11-12	12 November 1812
12

**daddylonglegs** · 10-21-2014, 07:39 PM

This formula will also work to convert a date in A2

=TEXT(1900&MID(A2,5,6),"d mmmm ")&LEFT(A2,4)

If you want to do anything more complex with pre-1900 dates John Walkenbach has an add-in that might assist you - see here