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
Day,Month,Year Conversion
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Day,Month,Year Conversion
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.
=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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Day,Month,Year Conversion
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"
You do not have the required permissions to view the files attached to this post.
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Day,Month,Year Conversion
More recent version of Excel?
or try:
edit 4th Aug 2021:
even shorter:
Code: Select all
=DATEVALUE(TEXTJOIN("-",TRUE,INDEX(TEXTSPLIT(SUBSTITUTE(TRIM(D2),",","")," "),{3,2,4})))
Code: Select all
=DATEVALUE(CONCAT(INDEX(TEXTSPLIT(SUBSTITUTE(TRIM(D2),",","")," "),{3,2,4})))
even shorter:
Code: Select all
=DATEVALUE(CONCAT(INDEX(TEXTSPLIT(TRIM(D2),{" ",","}),{4,3,6})))
Last edited by p45cal on 04 Aug 2023, 15:39, edited 4 times in total.
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Day,Month,Year Conversion
Hi-jacked hamster's workbook to add a named lambda version of my formula. See cell D2.
You do not have the required permissions to view the files attached to this post.
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: Day,Month,Year Conversion
A UDF:
In A1: "Thursday, June 17, 2021"
In a cell: =F_snb(A1)
Code: Select all
Function F_snb(c00)
sn = Split(c00)
F_snb = --Format(Val(sn(2)) & "-" & sn(1) & "-" & sn(3), "0")
End Function
In a cell: =F_snb(A1)
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Day,Month,Year Conversion
Also:
Code: Select all
F_snb = CDate(Val(sn(2)) & "-" & sn(1) & "-" & sn(3))
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: Day,Month,Year Conversion
@p45
Quite right !
So even this will suffice:
Quite right !
So even this will suffice:
Code: Select all
Function F_snb(c00)
F_snb = CDate(Mid(c00, InStr(c00, " ")))
End Function