Changing the format of the date (From text to number)

[Liberty Prime]

Hi,

sort of recurrent topic, but I would need some help regarding this case.

Because I work with a third party tool that pulls out different reports to be later processed in excel, I'm a bit stuck since they have run a recent update in which they have changed the date format.

Before it was dd.mm.yyyy (dots instead of dash or slash for Germany), but now the data that is coming out is "Jul 4, 2018" and this format can't be changed. My attempt to rearrange and combine them together with the following formula did not work:

=DATE(right(A2,4),mid(A2,4,2),left(A2,3)))

Maybe I need to tell excel that Jul is for July to be assign with the value 4, but I can't figure out how could I make it work with either MONTH or DATEVALUE.

Would you be so kind to help me a bit with this?

Thanks in advance!

 

[Joe4]

If you date is text and just looks like "Jul 4, 2018", you can simply use the DATEVALUE function on it, and choose any date format you desire in the format of the cell, i.e.
=DATEVALUE(A2)

 

[Special-K99]

There's probably a better way

=(TRIM(MID(A1,5,2))&"/"&MONTH("01/"&LEFT(A1,3))&"/"&RIGHT(A1,4))+0

 

[Special-K99]

If you date is text and just looks like "Jul 4, 2018", you can simply use the DATEVALUE function on it, and choose any date format you desire in the format of the cell, i.e.
=DATEVALUE(A2)

Doesnt seem to work, I get a #VALUE error

 

[Peter_SSs]

Another option to try:
Select the columns -> Data -> Text To Columns -> Delimited -> Next -> Remove all Delimiter check box marks -> Next -> In the Date drop-down choose MDY -> Finish
Then format differently if you want.

 

[Joe4]

Doesnt seem to work, I get a #VALUE error

Odd, works just fine for me. Maybe it is dependent upon which version of Excel you are using (European vs. American) or regional settings.

 

[steve the fish]

There's probably a better way

=(TRIM(MID(A1,5,2))&"/"&MONTH("01/"&LEFT(A1,3))&"/"&RIGHT(A1,4))+0

With Jul 4, 2018 i get a value error. Probably because of the comma. Heres a way that seems to work for UK. It will be different in US where datevalue would work:

=0+REPLACE(REPLACE(A1,FIND(",",A1),0,LEFT(A1,FIND(" ",A1))),1,FIND(" ",A1),"")

 

[steve the fish]

Odd, works just fine for me. Maybe it is dependent upon which version of Excel you are using (European vs. American) or regional settings.

In the UK 4 Jul, 2018 works with datevalue but not Jul 4, 2018

 

[Dave Patton]

Excel 2010
	B	C	D	E
2	Jul 4, 2018	04-07-2018	04-07-2018	04-07-2018
3	Jul 24, 2018	24-07-2018	24-07-2018	24-07-2018
4
1d

Cell Formulas
Range	Formula
C2	=DATE(RIGHT(B2,4),MONTH(("1"&LEFT(B2,3))),MID(B2,FIND(",",B2)-2,2))
C3	=DATE(RIGHT(B3,4),MONTH(("1"&LEFT(B3,3))),MID(B3,FIND(",",B3)-2,2))
D2	=--(MID(B2,5,1)&LEFT(B2,3)&RIGHT(B2,4))
D3	=--(MID(B3,5,2)&LEFT(B3,3)&RIGHT(B3,4))
E2	=--(MID(B2,5,LEN(B2)-10)&LEFT(B2,3)&RIGHT(B2,4))
E3	=--(MID(B3,5,LEN(B3)-10)&LEFT(B3,3)&RIGHT(B3,4))

Try the alternative that you prefer.
Format to your preference.

 

[Peter_SSs]

I also get #VALUE with Joes' formula, but I also get the same error with post #3 formula if the date only has a single-figure day like the date in post #1 .
This formula works for me for dates with single or double digit days.
=DATEVALUE(RIGHT(LEFT(F2,FIND(",",F2)-1),2)&REPLACE(F2,5,3,""))