I need to convert this string:
Donnerstag, Juni 05 2014
into a usable excel date. I'm trying to use something like:
DATEVALUE(MID(A1,FIND(",",A1)+2,LEN(A1))))+RIGHT(A1,11)
but cannot work it out. Can someone help?
Thanks.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI need to convert this string:
Donnerstag, Juni 05 2014
into a usable excel date. I'm trying to use something like:
DATEVALUE(MID(A1,FIND(",",A1)+2,LEN(A1))))+RIGHT(A1,11)
but cannot work it out. Can someone help?
Thanks.
Select the cells you wish to convert and run this short macro:
Sub dural()
Dim dy As Long, mnth As Long, yr As Long
Dim t As String, U As Long, d As Date
Dim r As Range
bry = Split("Januar,Februar,März,April,Mai,Juni,Juli,August,September,Oktober,November,Dezember", ",")
For Each r In Selection
t = r.Text
ary = Split(t, " ")
U = UBound(ary)
yr = CLng(ary(U))
dy = CLng(ary(U - 1))
For i = 0 To 11
If ary(U - 2) = bry(i) Then
mnth = i + 1
End If
Next i
d = DateSerial(yr, mnth, dy)
r.Value = d
Next r
End Sub
Then apply any desired formatting to resulting values.
EDIT#1:
To use this conversion as a function (UDF()), use the following code:
Public Function EngDate(inpt As String) As Date
Dim dy As Long, mnth As Long, yr As Long
Dim t As String, U As Long, d As Date
bry = Split("Januar,Februar,März,April,Mai,Juni,Juli,August,September,Oktober,November,Dezember", ",")
t = inpt
ary = Split(t, " ")
U = UBound(ary)
yr = CLng(ary(U))
dy = CLng(ary(U - 1))
For i = 0 To 11
If ary(U - 2) = bry(i) Then
mnth = i + 1
End If
Next i
d = DateSerial(yr, mnth, dy)
EngDate = d
End Function
I would use the Power Query Add-In for this. From Excel 2016, Power Query is built in to the Data ribbon under "Get and Transform" section.
PQ has built-in date conversion functions that usually just work without any coding. In the PQ Editor, just select the column and set the Data Type to Date.
I have English Excel, but I was able to process an equivalent date string "Thursday, June 05 2014" and add columns to get Week of Year an Week Day (number and text).
You can build this just by clicking around in the PQ UI, or here is a sample PQ script to get you going (assumes your input is in a table called "Table1", with a Column heading of "Input Date").
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input Date", type date}}),
#"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "WeekOfYear", each Date.WeekOfYear([Input Date]), type number),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Week of Year", "DayOfWeek", each Date.DayOfWeek([Input Date]), type number),
#"Added Day of Week Name" = Table.AddColumn(#"Inserted Day of Week", "Day of Week Name", each Date.ToText([Input Date], "dddd"))
in
#"Added Day of Week Name"
Via trial and error, I found a solution using this formula:
=DATUM((WERT(RECHTS(RECHTS(G5;LÄNGE(G5)-FINDEN(" ";G5));4)));
(MONAT(DATWERT(TEXT(LINKS(RECHTS(G5;LÄNGE(G5)-FINDEN(" ";G5));FINDEN(" ";RECHTS(G5;LÄNGE(G5)-FINDEN(" ";G5)))-1);1)&" 1")));
(WERT(TEIL(RECHTS(G5;LÄNGE(G5)-FINDEN(" ";G5));FINDEN(" ";RECHTS(G5;LÄNGE(G5)-FINDEN(" ";G5)))+1;2))))
code is in German, but you get the idea. I just extracted each part of the date that I needed and put it back together again.
Thanks for the other sugguestions - all of them good. But I wanted to use a normal formula ;)