Date Fromatting

[Fish55]

Hi,

I am trying to convert a date written in text to be formatted as MMM-YYYY.

In the example attached I have tried to take the text date and using DATEVALUE convert it DD/MM/YYYY.

The issue i'm having is one is in US format and the other in UK format. Is there anyway to change this.

[Sam Capricci]

is your first example 12/1/2017 supposed to be 1/12/2017 or 12/1/2017?

[Fish55]

the first example is MM/DD/YYYY, I am trying to change it to DD/MM/YYYY and also have MMM/YYYY

[ChemistB]

Try
=--TRIM(MID(SUBSTITUTE(C4,"/",REPT(" ",10)),10,10)&"/"&LEFT(SUBSTITUTE(C4,"/",REPT(" ",10)),10)&"/"&RIGHT(C4,4))
with cell formatted as mmm-yy

[Sam Capricci]

for the examples provided, this appears to work...
=IFERROR(DATEVALUE(TEXT(C4,"dd/mm/yyyy")),IF(ISNUMBER(C4),C4,DATE(MID(C4,FIND("/",C4,FIND("/",C4)+1)+1,LEN(C4)),LEFT(C4,FIND("/",C4)-1),MID(C4,FIND("/",C4)+1,(FIND("/",C4,FIND("/",C4)+1))-FIND("/",C4)-1))))

I don't know what to make of your mmm/yyyy w/o an example AND an expected result.

[MrShorty]

From what I have seen, the DATEVALUE() function uses your operating system regional settings to decide how to read a text date. In your case, I'm guessing that your system setting is set to a DMY date format, and so the DATEVALUE() function cannot understand MDY dates (or it will misunderstand them). I notice that, when I open your file (on a system set to MDY), it has not trouble with these text strings to date conversion.

Rather than a formula like the others have proposed, I would probably use the text to columns command. At step 3 of the text import wizard, be sure to select the "Date" rate button and specify MDY dates (and D4 as the destination cell, if desired).

Column E can then be a simple copy of column D (=D4 copied down) and formatted as "mmm-yyyy" to display "dec-2017". Remember that the underlying value is still the serial number for 1 dec 2017 (43070).

[ChemistB]

MrShorty,
I like your approach as long as you are sure of your data (i.e. that all the text dates in column C will convert to dates in the new format. If they don't, then excel will keep them in the same format (i.e mm/dd/yyyy) and not give an error.
If the OP is sure that all of column C is correctly inputted as mm/dd/yyyy, as it appears to be, then I'd go with your method.