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!

 

[Liberty Prime]

Wow, very surprised for the quick response. Thanks to all!

A few things:

- The excel version I'm using is the 2016, from a Windows 10 machine in German (for the regional settings), but English in display/working language.
- The formula from Steve the fish =0+REPLACE(REPLACE(A1,FIND(",",A1),0,LEFT(A1,FIND(" ",A1))),1,FIND(" ",A1),"") works well! I get a number that only needs to be converted to date (but in this case, as easy as selecting the right format from the dropdown menu)
- The formula from Dave Patton =DATE(RIGHT(B5,4),MONTH(("1"&LEFT(B5,3))),MID(B5,FIND(",",B5)-2,2)) work as well

 

[wideboydixon]

Language and region agnostic but tied specifically to this date format:

Book1
	A	B
1	Jul 4, 2018	04/07/2018
Sheet1

Cell Formulas
Range	Formula
B1	=DATE(RIGHT($A1,4)+0,MATCH(LEFT(A1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),MID($A1,5,FIND(",",A1)-5)+0)

:D

WBD

 

[Peter_SSs]

A few things:

- The excel version I'm using is the 2016, from a Windows 10 machine in German (for the regional settings), but English in display/working language.
- The formula from Steve the fish =0+REPLACE(REPLACE(A1,FIND(",",A1),0,LEFT(A1,FIND(" ",A1))),1,FIND(" ",A1),"") works well! I get a number that only needs to be converted to date (but in this case, as easy as selecting the right format from the dropdown menu)
- The formula from Dave Patton =DATE(RIGHT(B5,4),MONTH(("1"&LEFT(B5,3))),MID(B5,FIND(",",B5)-2,2)) work as well

I'm not sure if you are implying that all the other suggestions failed?
In any case, here is a shorter one that works for me

=DATEVALUE(MID(SUBSTITUTE(A2,",",LEFT(A2,3)),5,10))

 

[Liberty Prime]

Some of them failed, yes. Others (i.e. the case of the many ones from Dave Patton), I pick the one that fits the better to my taste and worked. The one that you have just posted worked too.

It seems that if there is any other guy with the same issue, he will have plenty of ways to solve the problem :P

 

[Peter_SSs]

It seems that if there is any other guy with the same issue, he will have plenty of ways to solve the problem :P

That is true - there were many suggestions in this thread.

 

[Liberty Prime]

Hi, it's me again

Because of an update in the way that Google Ads displays the dates in its reports, I need to come back to this topic to do my job, otherwise I'm stuck... I would need to change this type of date format (German ISO):

dd.mm.yy

To this one (the one that Google Sheets uses)

mm/dd/yyyy

-----

PS: At the end I figured it out. This was the solution:

=DATE(RIGHT(TEXT(E4,"MM/DD/YYYY"),2)+100,MID(TEXT(E4,"MM/DD/YYYY"),4,2),LEFT(TEXT(E4,"MM/DD/YYYY"),2))

 

[steve the fish]

So you need to change it to a genuine date and the machine settings will take care of the format? See what this does:

=DATE(20&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

 

[Liberty Prime]

It works as well, and it's actually shorter than my solution

Thanks for the help!