Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1104to1108
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Datum-Format umrechnen

Datum-Format umrechnen
Jochen
Hallo zusammen,
ich würde mir gerne zwei Funktionen per VBA definieren, um das Datum zwischen zwei Foramten umzurechnen. Leider fehlt mir gerade jeglicher Ansatz dazu.
Folgende Formate möchte ich hin und her umrechnen:
1) 938.6 (9= Jahr (also 2009), 38 ist die Kalnderwoche und .6 ist der Wochentag, also Samstag)
2) 19.09.2009
Ob bei Format 1) nun "9" oder "09" steht, sollte der Funktion egal sein.
Könnt ihr mir evtl helfen?
Herzlichen Dank sagt schon jetzt
Jochen

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
So gehts
19.09.2009 22:55:56
Backowe
Hi Jochen,
die Basisformel findest Du unter: http://www.excelformeln.de/formeln.html?welcher=186
 AB
1938.619.09.2009

Formeln der Tabelle
ZelleFormel
B1=("4.1."&LINKS(A1;1)+2000)+TEIL(A1;2;2)*7-7-REST("2.1."&LINKS(A1;1)+2000;7)+5


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Jürgen
Anzeige
Noch etwas genauer!
19.09.2009 23:04:24
Backowe
Hi Jochen
 AB
1938.619.09.2009

Formeln der Tabelle
ZelleFormel
B1=("4.1."&LINKS(A1;1)+2000)+TEIL(A1;2;2)*7-7-REST("2.1."&LINKS(A1;1)+2000;7)+RECHTS(A1;1)-1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Jürgen
Anzeige
Sorry, VBA überlesen
19.09.2009 23:32:04
Backowe
Hi Jochen,
VBA-Code:
Function DatBerechnung(Datum As String)
DatBerechnung = DateSerial(IIf(Len(Datum) = 5, Left(Datum, 1) + 2000, Left(Datum, 2) + 2000), 1, 4) + _
  IIf(Len(Datum) = 5, Mid(Datum, 2, 2), Mid(Datum, 3, 2)) * 7 - 7 - _
  DateSerial(IIf(Len(Datum) = 5, Left(Datum, 1) + 2000, Left(Datum, 2) + 2000), 1, 2) Mod 7 + Right(Datum, 1) - 1
End Function
Gruß Jürgen
AW: Datum-Format umrechnen
Josef

Hallo Jochen,
diese Funktion in ein Modul.
Public Function switchDate(Text As String) As Variant
  Dim lngYear As Long, lngMonth As Long, lngDay As Long, lngWeek As Long
  Dim tmp As Date
  
  If Right(Text, 1) = "0" Then Text = Mid(Text, 99)
  
  If Len(Text) - Len(Replace(Text, ".", "")) = 1 Then
    lngDay = Clng(Right(Text, 1)) - 1
    lngWeek = Clng(Left(Right(Text, 4), 2))
    lngYear = 2000 + Clng(Left(Text, Len(Text) - 4))
    switchDate = CDate(DateSerial(lngYear, 1, 4) + lngWeek * 7 - 7 - (DateSerial(lngYear, 1, 2) Mod 7)) + lngDay
  ElseIf IsDate(Text) Then
    lngYear = Year(Text)
    lngDay = Weekday(Text, 2)
    tmp = DateSerial(Year(CDate(Text) + (8 - Weekday(Text)) Mod 7 - 3), 1, 1)
    lngWeek = ((CDate(Text) - tmp - 3 + (Weekday(tmp) + 1) Mod 7)) \ 7 + 1
    
    switchDate = CStr(Right(lngYear, 2)) & CStr(lngWeek) & "." & CStr(lngDay)
    If Left(switchDate, 1) = "0" Then switchDate = Mid(switchDate, 2, 99)
  Else
    switchDate = CVErr(xlErrValue)
  End If
  
End Function

In der Tabelle wird es so angewandt.
Tabelle3

 ABC
1938.6Sa 19.09.2009938.6
2951.1Mo 14.12.2009951.1
3914.5Fr 03.04.2009914.5

Formeln der Tabelle
ZelleFormel
B1=switchdate(A1)
C1=switchdate(B1)
B2=switchdate(A2)
C2=switchdate(B2)
B3=switchdate(A3)
C3=switchdate(B3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Die KW muss zweistellig angegeben werden!
Gruß Sepp

AW: Datum-Format umrechnen
Peterchen

Hallo,
bei Jahresübergängen geht das aber in die Hose ;)
Beispiel:
901.2 -> 30.12.2008 -> 801.2
Das Jahr der Woche hängt immer vom Donnerstag der Woche ab.
Hier eine Version wo das berücksichtigt ist, und auch Jahre kleiner 2000 möglich sind.
Public Function SwitchDate(ByVal DateS As String) As String Dim DayI As Integer Dim WeekI As Integer Dim YearI As Integer Dim D As Date If Len(DateS) - Len(Replace$(DateS, ".", "")) = 1 Then If Len(DateS) = 5 Then DateS = "0" & DateS DayI = CInt(Right$(DateS, 1)) WeekI = CInt(Mid$(DateS, 3, 2)) YearI = CInt(Left$(DateS, 2)) If YearI > 50 Then 'Falls 51-99 -> 1951-1999 werden sollen YearI = YearI + 1900 Else YearI = YearI + 2000 End If D = DateSerial(YearI, 1, 4) '1.4.nnnn gehoert immer zu KW1 vom Jahr D = D + (WeekI - 1) * 7 + DayI - Weekday(D, vbMonday) SwitchDate = Format$(D, "dd.mm.yyyy") ElseIf IsDate(DateS) Then D = CDate(DateS) DayI = Weekday(D, vbMonday) WeekI = WeekNr(D) YearI = Year(D - DayI + 4) 'Das Jahr haengt vom Donnerstag der Woche ab. If YearI Public Function WeekNr(DayD As Date) As Integer Dim ThursdayAtWeekD As Date 'Donnerstag der Woche Dim DayWeekOneD As Date 'Tag erste Woche Dim ThursdayWeekOneD As Date 'Donnerstag erste Woche ThursdayAtWeekD = DayD - Weekday(DayD, vbMonday) + 4 DayWeekOneD = DateSerial(Year(ThursdayAtWeekD), 1, 4) ThursdayWeekOneD = DayWeekOneD - Weekday(DayWeekOneD, vbMonday) + 4 WeekNr = (ThursdayAtWeekD - ThursdayWeekOneD) \ 7 + 1 End Function
Die Hilfsfunktion WeekNr könnte man gleich einbauen, aber die Wochennummer braucht man ja öfter mal.
Gruß Peter
AW: Datum-Format umrechnen
Jochen

Hallo zusammen,
zuerst einmal herzlcihen Dank für Eure Mühe, dei ihr alle investiert habt. Leider komme ich erst jetzt dazu mich zu bedanken, da ich unterwegs war.
Ein Problem habe ich allerdings: Bei der Umrechnung in ein "normales Datum" gibt es Probleme.
SwitchDate = Format$(D, "dd.mm.yyyy")
Die Zeile liefert zwar das korrekte Ergebnis, aber es scheint als text gespeichert zu sein. Wenn cih die Zelle als einfach Zahl formatiere, dann bleibt immer noch z.B. 22.09.2009 dort stehen und nicht 40078
Kann mir da bitte noch mal einer von Euch helfen? Ich möchte mit dem Datum gerne weiter rechnen, was so aber nicht funktioniert.
Vielen Dank
Jochen
War schon da...
Erich

Hi Jochen,
du hast dir meinen Vorschlag nicht wirklich angesehen, oder?
Ich lasse den Thread auch offen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: War schon da...
Jochen

Hallo Erich,
doch, mir gefiel allerdings der Vorschlag von Josef und Peterchen, der mit nur einer Funktion hin und wieder zurück rechnet.
Wenn ich es aber richtig sehe, dann liegt das Problem schon in der Definition der Funktion: Entweder ich gebe einen Str zurück oder ein Date, beides geht in einer Funktion aber nicht, oder hast Du einen Vorschlag.
Ansonsten werde cih auf Deine Funktionen wechseln, denn dort hab ich beides.
Grüße
Jochen
Klare Funktionen
Erich

Hi Jochen,
sicher könnte man die "SwitchDate" umschreiben, mit Variant als Argument und als Wert.
Aber sollte man das tun? Ich würde das lassen, denn eierlegende Wollmilchsauen beißen manchmal zurück...
Meinen Vorschlag hab ich ja schon gemacht. (Mir gefällt der besser.)
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: Klare Funktionen
Jochen

Hallo Erich,
OK, Du hast mich überzeugt. Abgesehen davon hatte ich ursprünglich ja auch nach zwei Funktionen gefragt und wollte es nicht in einer haben.
Werde aber evtl. in Deinem Vorschalg noch etwas "herum basteln" um z.B. leere zellen abzufangen etc.
Vielen Dank aber für Deine Hilfe.
Jochen
Datum in Woche und umgekehrt (VBA)
Erich

Hi Jochen,
noch ein Vorschlag:

Public Function KwOfDate(ByVal datD As Date) As String
Dim iKW As Integer, iJJ As Integer
iJJ = Year(datD + 5 - (datD - 2) Mod 7) - 1900
KwOfDate = iJJ + 100 * (iJJ > 99) & Format(DinKW(datD), "00") & _
"." & Weekday(datD, vbMonday)
End Function
Public Function DateOfKw(ByVal strKW As String) As Date
Dim iKW As Integer, iJJ As Integer
strKW = Right("0" & strKW, 6)
If InStr(strKW, ".") = 5 And Len(Replace$(strKW, ".", "")) = 5 Then
iKW = Mid(strKW, 3, 2) - 1
iJJ = Left(strKW, 2)
iJJ = iJJ + 1900 - 100 * (iJJ  1951-1999 werden sollen
DateOfKw = DateSerial(iJJ, 1, 4)       '4.1.JJJJ gehoert immer zu KW1 vom Jahr
DateOfKw = DateOfKw + 7 * iKW + Right(strKW, 1) - Weekday(DateOfKw, vbMonday)
End If
End Function
Public Function DinKW(DD As Date) As Integer
DinKW = (DD - DateSerial(Year(DD + 3 - (DD - 2) Mod 7), 1, (DD - 2) Mod 7 - 9)) \ 7
End Function
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Datum-Format umrechnen
19.09.2009 23:39:52
Josef
Hallo Jochen,
diese Funktion in ein Modul.
Public Function switchDate(Text As String) As Variant
  Dim lngYear As Long, lngMonth As Long, lngDay As Long, lngWeek As Long
  Dim tmp As Date
  
  If Right(Text, 1) = "0" Then Text = Mid(Text, 99)
  
  If Len(Text) - Len(Replace(Text, ".", "")) = 1 Then
    lngDay = Clng(Right(Text, 1)) - 1
    lngWeek = Clng(Left(Right(Text, 4), 2))
    lngYear = 2000 + Clng(Left(Text, Len(Text) - 4))
    switchDate = CDate(DateSerial(lngYear, 1, 4) + lngWeek * 7 - 7 - (DateSerial(lngYear, 1, 2) Mod 7)) + lngDay
  ElseIf IsDate(Text) Then
    lngYear = Year(Text)
    lngDay = Weekday(Text, 2)
    tmp = DateSerial(Year(CDate(Text) + (8 - Weekday(Text)) Mod 7 - 3), 1, 1)
    lngWeek = ((CDate(Text) - tmp - 3 + (Weekday(tmp) + 1) Mod 7)) \ 7 + 1
    
    switchDate = CStr(Right(lngYear, 2)) & CStr(lngWeek) & "." & CStr(lngDay)
    If Left(switchDate, 1) = "0" Then switchDate = Mid(switchDate, 2, 99)
  Else
    switchDate = CVErr(xlErrValue)
  End If
  
End Function

In der Tabelle wird es so angewandt.
Tabelle3

 ABC
1938.6Sa 19.09.2009938.6
2951.1Mo 14.12.2009951.1
3914.5Fr 03.04.2009914.5

Formeln der Tabelle
ZelleFormel
B1=switchdate(A1)
C1=switchdate(B1)
B2=switchdate(A2)
C2=switchdate(B2)
B3=switchdate(A3)
C3=switchdate(B3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Die KW muss zweistellig angegeben werden!
Gruß Sepp

Anzeige
AW: Datum-Format umrechnen
20.09.2009 02:42:54
Peterchen
Hallo,
bei Jahresübergängen geht das aber in die Hose ;)
Beispiel:
901.2 -> 30.12.2008 -> 801.2
Das Jahr der Woche hängt immer vom Donnerstag der Woche ab.
Hier eine Version wo das berücksichtigt ist, und auch Jahre kleiner 2000 möglich sind.
Public Function SwitchDate(ByVal DateS As String) As String Dim DayI As Integer Dim WeekI As Integer Dim YearI As Integer Dim D As Date If Len(DateS) - Len(Replace$(DateS, ".", "")) = 1 Then If Len(DateS) = 5 Then DateS = "0" & DateS DayI = CInt(Right$(DateS, 1)) WeekI = CInt(Mid$(DateS, 3, 2)) YearI = CInt(Left$(DateS, 2)) If YearI > 50 Then 'Falls 51-99 -> 1951-1999 werden sollen YearI = YearI + 1900 Else YearI = YearI + 2000 End If D = DateSerial(YearI, 1, 4) '1.4.nnnn gehoert immer zu KW1 vom Jahr D = D + (WeekI - 1) * 7 + DayI - Weekday(D, vbMonday) SwitchDate = Format$(D, "dd.mm.yyyy") ElseIf IsDate(DateS) Then D = CDate(DateS) DayI = Weekday(D, vbMonday) WeekI = WeekNr(D) YearI = Year(D - DayI + 4) 'Das Jahr haengt vom Donnerstag der Woche ab. If YearI Public Function WeekNr(DayD As Date) As Integer Dim ThursdayAtWeekD As Date 'Donnerstag der Woche Dim DayWeekOneD As Date 'Tag erste Woche Dim ThursdayWeekOneD As Date 'Donnerstag erste Woche ThursdayAtWeekD = DayD - Weekday(DayD, vbMonday) + 4 DayWeekOneD = DateSerial(Year(ThursdayAtWeekD), 1, 4) ThursdayWeekOneD = DayWeekOneD - Weekday(DayWeekOneD, vbMonday) + 4 WeekNr = (ThursdayAtWeekD - ThursdayWeekOneD) \ 7 + 1 End Function
Die Hilfsfunktion WeekNr könnte man gleich einbauen, aber die Wochennummer braucht man ja öfter mal.
Gruß Peter
Anzeige
AW: Datum-Format umrechnen
22.09.2009 11:39:36
Jochen
Hallo zusammen,
zuerst einmal herzlcihen Dank für Eure Mühe, dei ihr alle investiert habt. Leider komme ich erst jetzt dazu mich zu bedanken, da ich unterwegs war.
Ein Problem habe ich allerdings: Bei der Umrechnung in ein "normales Datum" gibt es Probleme.
SwitchDate = Format$(D, "dd.mm.yyyy")
Die Zeile liefert zwar das korrekte Ergebnis, aber es scheint als text gespeichert zu sein. Wenn cih die Zelle als einfach Zahl formatiere, dann bleibt immer noch z.B. 22.09.2009 dort stehen und nicht 40078
Kann mir da bitte noch mal einer von Euch helfen? Ich möchte mit dem Datum gerne weiter rechnen, was so aber nicht funktioniert.
Vielen Dank
Jochen
Anzeige
War schon da...
22.09.2009 12:25:12
Erich
Hi Jochen,
du hast dir meinen Vorschlag nicht wirklich angesehen, oder?
Ich lasse den Thread auch offen.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: War schon da...
22.09.2009 13:25:33
Jochen
Hallo Erich,
doch, mir gefiel allerdings der Vorschlag von Josef und Peterchen, der mit nur einer Funktion hin und wieder zurück rechnet.
Wenn ich es aber richtig sehe, dann liegt das Problem schon in der Definition der Funktion: Entweder ich gebe einen Str zurück oder ein Date, beides geht in einer Funktion aber nicht, oder hast Du einen Vorschlag.
Ansonsten werde cih auf Deine Funktionen wechseln, denn dort hab ich beides.
Grüße
Jochen
Anzeige
Klare Funktionen
22.09.2009 16:06:57
Erich
Hi Jochen,
sicher könnte man die "SwitchDate" umschreiben, mit Variant als Argument und als Wert.
Aber sollte man das tun? Ich würde das lassen, denn eierlegende Wollmilchsauen beißen manchmal zurück...
Meinen Vorschlag hab ich ja schon gemacht. (Mir gefällt der besser.)
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: Klare Funktionen
22.09.2009 16:24:12
Jochen
Hallo Erich,
OK, Du hast mich überzeugt. Abgesehen davon hatte ich ursprünglich ja auch nach zwei Funktionen gefragt und wollte es nicht in einer haben.
Werde aber evtl. in Deinem Vorschalg noch etwas "herum basteln" um z.B. leere zellen abzufangen etc.
Vielen Dank aber für Deine Hilfe.
Jochen
Anzeige
Datum in Woche und umgekehrt (VBA)
20.09.2009 10:04:48
Erich
Hi Jochen,
noch ein Vorschlag:

Public Function KwOfDate(ByVal datD As Date) As String
Dim iKW As Integer, iJJ As Integer
iJJ = Year(datD + 5 - (datD - 2) Mod 7) - 1900
KwOfDate = iJJ + 100 * (iJJ > 99) & Format(DinKW(datD), "00") & _
"." & Weekday(datD, vbMonday)
End Function
Public Function DateOfKw(ByVal strKW As String) As Date
Dim iKW As Integer, iJJ As Integer
strKW = Right("0" & strKW, 6)
If InStr(strKW, ".") = 5 And Len(Replace$(strKW, ".", "")) = 5 Then
iKW = Mid(strKW, 3, 2) - 1
iJJ = Left(strKW, 2)
iJJ = iJJ + 1900 - 100 * (iJJ  1951-1999 werden sollen
DateOfKw = DateSerial(iJJ, 1, 4)       '4.1.JJJJ gehoert immer zu KW1 vom Jahr
DateOfKw = DateOfKw + 7 * iKW + Right(strKW, 1) - Weekday(DateOfKw, vbMonday)
End If
End Function
Public Function DinKW(DD As Date) As Integer
DinKW = (DD - DateSerial(Year(DD + 3 - (DD - 2) Mod 7), 1, (DD - 2) Mod 7 - 9)) \ 7
End Function
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige