Change a column of data that contain 2 different date formats

You should be able to adapt the below to your actual data:

The code below assumes your data comes from an Excel table. It should work whether your data is stored as numbers and formatted dates, or as text. And a simple change in the first lines can be made if the data source is a *.csv file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 #"Add Reformatted Dates" = Table.AddColumn(#"Source","Reformatted Dates", (r)=>
        [a=try Number.From(r[Dates]) otherwise DateTime.From(r[Dates],"en-150"),
         b=try Date.ToText(Date.From(a),"MMMyy")
            otherwise [yr=Number.IntegerDivide(a,10000),
                       m=Number.IntegerDivide(Number.Mod(a,10000),100),
                       d=Number.Mod(a,100),
                       dt=#date(yr,m,d),
                       out=Date.ToText(dt,"ddMMMyy")][out]
         
         ][b], type text)
in
    #"Add Reformatted Dates"

You can try to use a custom text strings with this command :

Date.ToText( #date( yyyy, mm, dd ), [ Format = "ddmmmyy" ] )

This dashboard can help you for the format that you want !

Format  Description                 December 31, 2023          February 1, 2003
%d  Single Digit Day (1-31)         31                     1
dd  Double Digit Day (01-31)    31                     01
ddd Short Weekday Name          Sun                    Sat
dddd    Full Weekday Name           Sunday                     Saturday
%M  Single Digit Month (1-12)   12                     2
MM  Double Digit Month (01-12)  12                     02
MMM Short Month Name            Dec                    Feb
MMMM    Full Month Name                 December               February
%y  Year (0-99)                 23                     3
yy  Year (00-99)                    23                     03
yyy Year with at least three digits 2023                       2003
yyyy    Four-Digit Year                 2023                       2003
yyyyy   Five-Digit Year                 02023                      02003
m, M    Day followed by Full Month Name December 31            February 1
y, Y    Standard Long Date          December 2023              February 2003
d   Standard Short Date         12/31/2023             2/1/2003
D   Full Long Date                  Sunday, December 31, 2023  Saturday, February 1, 2003
%g, gg  The Period of an Era            A.D.                       A.D.

Convert Date Text to Date Text with Different Format

• This works for me. It required searching through many webpages. I'm not complaining, but emphasizing how much of a newb I am. There may be better methods, but I hope this helps you meet your requirements.

Sample Data in .csv File

Expected Result

M-Code

let
// setup (hardly relevant)
    Source = Csv.Document(File.Contents("C:\Test\Sample.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Promoted Headers",1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Bottom Rows", "Dates", "Dates - Copy"),
    #"Renamed Dupe Column" = Table.RenameColumns(#"Duplicated Column",{{"Dates - Copy", "Initial"}}),

// Transform Dates

// Format1 = "yyyymmdd" to "ddmmmyy"
// Format2 = "dd/mm/yyyy hh:mm:ss" to "mmmyy" ' the time is irrelevant in this solution

// 'Test.Range' retrieves the two digits for each date part which is enough for the day and year.
// To get the month in 'MMM' format, the digits are converted to a number using 'Number.FromText'.
// which is fed into '#date' which is fed into 'Date.ToText'.

// Define constants.
DateDelimiter = "-", // looks nicer with a dash (you decide)
MonthFormat = "MMM",

// Replace "Renamed Dupe Column" with your previous step, and "Dates" with your column title.
    #"Transformed Dates" = Table.TransformColumns(#"Renamed Dupe Column",
    {"Dates",
        each if Text.Length(_) = 8 then Text.Combine({
            Text.Range(_,6,2) & DateDelimiter,
            Date.ToText(#date(1,Number.FromText(Text.Range(_,4,2)),1),MonthFormat) & DateDelimiter,
            Text.Range(_,2,2)})
        else if Text.Length(_) = 19 then Text.Combine({
            Date.ToText(#date(1,Number.FromText(Text.Range(_,3,2)),1),MonthFormat) & DateDelimiter,
            Text.Range(_,8,2)})
        else _ 
    })
// Don't forget the trailing comma above if it's not the last step!

// hardly relevant   
in
    #"Transformed Dates"

Power Query

Excel