Day,Month,Year Conversion

[JDeMaro22]

Hi,

I'm simply looking for the most convenient way to transform dates such as "Thursday, June 17, 2021" to "6/7/21. Is there a formula I can use for this?

Thanks,

Joshua

[HansV]

With such a text in D2:

=DATEVALUE(MID(D2,FIND(" ",D2,FIND(",",D2)+2)+1,FIND(",",D2,FIND(" ",D2,FIND(",",D2)+2)+1)-FIND(" ",D2,FIND(",",D2)+2)-1)&"-"&MID(D2,FIND(",",D2)+2,FIND(" ",D2,FIND(",",D2)+2)-FIND(",",D2)-2)&"-"&RIGHT(D2,4))

This can be filled down.

[hamster]

you can try Power Query

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Parsed Date" = Table.AddColumn(Source, "Parse", each Date.From(DateTimeZone.From([date])), type date),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Parsed Date",{"Parse"})
in
    #"Removed Other Columns"

d2d.png

[p45cal]

More recent version of Excel?

Code: Select all

=DATEVALUE(TEXTJOIN("-",TRUE,INDEX(TEXTSPLIT(SUBSTITUTE(TRIM(D2),",","")," "),{3,2,4})))

or try:

Code: Select all

=DATEVALUE(CONCAT(INDEX(TEXTSPLIT(SUBSTITUTE(TRIM(D2),",","")," "),{3,2,4})))

edit 4th Aug 2021:
even shorter:

Code: Select all

=DATEVALUE(CONCAT(INDEX(TEXTSPLIT(TRIM(D2),{" ",","}),{4,3,6})))

[p45cal]

Hi-jacked hamster's workbook to add a named lambda version of my formula. See cell D2.

2023-08-03_114048.jpg

[snb]

A UDF:

Code: Select all

Function F_snb(c00)
  sn = Split(c00)
  F_snb = --Format(Val(sn(2)) & "-" & sn(1) & "-" & sn(3), "0")
End Function

In A1: "Thursday, June 17, 2021"

In a cell: =F_snb(A1)

[p45cal]

Also:

Code: Select all

F_snb = CDate(Val(sn(2)) & "-" & sn(1) & "-" & sn(3))

[snb]

@p45

Quite right !

So even this will suffice:

Code: Select all

Function F_snb(c00)
  F_snb = CDate(Mid(c00, InStr(c00, " ")))
End Function