1

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.

  • I don't have a German language pack or version of excel, so can't experiment, but I would right click the cell (or range) and play with the cell formatting--you should be able to get excel to recognize it as a date. Or is the problem your excel does not speak German? (Like mine) – Tyson Oct 5 '15 at 13:28
  • My excel is actually German, so shouldn't be an issue. The cell says it is a date, but isn't. Its basically a text string. – egg Oct 5 '15 at 14:18
  • Your question is not supporting good answers. First, this already is a "usable Excel date", although it's not exactly in Gemran format. It merely mixed English format with German vocabulary, which is not the same. Second, if your Excel really refuses to process it, some easy-to-use UN*X/Cygnus shell commands would be able to do the job, but you're not giving any hints regarding your available toolbox. And third, you're not saying which result format you need. – Run CMD Oct 5 '15 at 14:21
  • its not a usual date, in the sense that when you change the date custom format - nothing changes. Its not possible to reformat it. Ideally I'd like an incell formula to convert to a usable short date. At the end, I'm looking for the week number. At the moment, a weeknum just delivers an error. – egg Oct 5 '15 at 15:16
2

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

enter image description here

| improve this answer | |
  • thanks... if theres a way to do that via an in cell formula, would be great... as this needs to be emeded into various reports. – egg Oct 5 '15 at 15:15
  • @egg Yes see my EDIT#1. – Gary's Student Oct 5 '15 at 15:25
  • many thanks. You'll see I found another solution - but thanks for this! – egg Oct 6 '15 at 6:08
0

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"

| improve this answer | |
  • thanks. I'm going to use my formula solution, but thanks for this... i might try it anyway! :) – egg Oct 6 '15 at 6:11
0

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 ;)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.