Date formats

[JEC8878]

Hi All

Is there a way you can convert date - Mon 25 Mar - to - 25/03/24 and copy formula down for hundreds of dates

 

[Asheesh]

Is it text or a date formatted in this manner..you can alternately share the excel file

 

[JEC8878]

i have attached the file - it is the highlighted part im needing to convert - i have a whole load of files with dates like this that need changing so anything that will help would be greatly appreciated thank you

 

[JEC8878]

or not highlighted lol - just the date pls

 

[Juniad]

Copy the formula and enter in E2 and drag it down.
=IFERROR(DATE(2024,XLOOKUP(RIGHT(A2,3),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},{1;2;3;4;5;6;7;8;9;10;11;12},""),TRIM(MID(A2,SEARCH(" ",A2)+1,2))),"")
change the cell format to Short Date

 

[JEC8878]

aw your a genius thank you

 

[Scott Huish]

=IFERROR(--SUBSTITUTE(MID(A2,5,6)," ","-"),"")

Format as Date

 

[vishal1979]

=TEXT(MID(A1,5,2)&"-"&MID(A1,9,3)&"-20"&RIGHT(A1,2), "DD/MM/YY")

 

[GraH - Guido]

Since the data is stored in a csv, I reckon there is an issue with importing the data.
If you have a version of Excel that's 2010 or beyond, there is no need for a formula.

Data -> From CSV - > Transform Data.

With the standard settings of Power Query, this is the result.



You can close and load this as a table in Excel. With a bit of extra steps, you can completely clean this up, simply by clicking some buttons in the UI.


This creates a "refreshable" solution. If you would save the new csv in the same place and with the same name, it will work just like this.

If you want to keep all the files, you can start from Data -> From File From Folder -> then as first step, click the column named Date Created and from the filter options select Date/time Filters and go for the option Is Latest. Drill down on the Binary column (click on the green word "Binary"). The following will happen.


EDIT:
You can also add a Fill Down step, for the lines where you have no date (even rows). I've done that in the uploaded excel file.