Zeitumstellung berücksichtigen

[bigcuno]

Hallo, 

ich kämpf mit dem Problem, dass ich gerne eine Zeitreihe hätte die sich dynamisch an das Jahr anpasst welches in Zelle A1 steht: zB. 2025

in Spalte B stehen dann sämtliche Stundenwerte dieses Jahres: 


01.01.2025 01:00
01.01.2025 02:00
01.01.2025 03:00
01.01.2025 04:00
...bis einschließlich
31.12.2025 23:00 


Doch jetzt wird es kompliziert. Die Zeitreihe muss die Sommerzeit- und die Winterzeitumstellung mitberücksichtigen. 

Sommerzeitumstellung ist immer der letzte Sonntag im März. Für die Ermittlung des letzten Sonntag im März habe ich folgende Formel definiert
 =DATUM(A1;4;0)-WOCHENTAG(DATUM(A1;4;0))+1
 
- hier wird die Stunde 02:00 übersprungen. 
Folglich würde es wie folgt aussehen: 

30.03.2025 00:00
30.03.2025 01:00
30.03.2025 03:00
30.03.2025 04:00

Winterzeitumstellung ist immer der letzte Sonntag im Oktober. Für die Ermittlung des letzten Sonntag im Oktober habe ich folgende Formel definiert  
=DATUM(A1;11;0)-WOCHENTAG(DATUM(A1;11;0))+1

 - hier wird die Stunde 02:00 doppelt gezählt. 
Folglich würde es wie folgt aussehen: 

26.10.2025 00:00
26.10.2025 01:00
26.10.2025 02:00 
26.10.2025 02:00
26.10.2025 03:00

Hat hier jemand Tipps? VBA ist leider keine Alternative da das Excel für die Arbeit benötigt wird und bei vielen Makros am Arbeits-PC gesperrt sind. 

  Zeitumstellung - Stunden.xlsx (Größe: 109,61 KB / Downloads: 12)

Vielen Dank im Voraus.

[slowboarder]

Hi

Ermittle in A3 und A4 Datum + Uhrzeit des Umschaltens:
Also

A3: =DATUM(A1;4;0)-WOCHENTAG(DATUM(A1;4;0))+1+Zeit(2;0;0)
A4: analog

Verwende dann in Spalte B diese Formel:

=LET(a;DATUM($A$1;1;1)+ZEILE()/24;a+(a>$A$3)*(a<$A$4)/24)

Gruß Daniel

[Andreas Killer]

Code:

=LET(
SZ;DATUM(A1;4;)-WOCHENTAG(DATUM(A1;4;))+1+ZEIT(2;0;0);
WZ;DATUM(A1;11;)-WOCHENTAG(DATUM(A1;11;))+1+ZEIT(2;0;0);
JD;SEQUENZ((DATUM(A1+1;1;1)-DATUM(A1;1;1))*24;;DATUM(A1;1;1);ZEIT(1;0;0));
JD+((JD>=SZ)*(JD<=WZ))*ZEIT(1;0;0))

Ich denke das müsste für jedes Jahr passen, bitte kontrollieren.

Andreas.

[EA1950]

Hallo,

PHP-Code:

=LET(
    j; $A$1;
    Anf; DATUM(j; 1; 1);
    Nxt; DATUM(j + 1; 1; 1);
    ZtÄnd; LAMBDA(j; m;
        LET(dt; DATUM(j; m; 0); dt - WOCHENTAG(dt) + 26 / 24)
    );
    SoZt; ZtÄnd(j; 4);
    WiZt; ZtÄnd(j; 11);
    NACHZEILE(
        SEQUENZ((Nxt - Anf) * 24; ; 0);
        LAMBDA(h;
            LET(ts; Anf + h / 24; ts + (ts >= SoZt) * (ts < WiZt) / 24)
        )
    )
) 


meine Version umgeht die Rundungsproblematik, die entsteht, wenn man 365*24 mal 1/24 (=ZEIT(1;0;0)) addiert.

[slowboarder]

@Anton

wäre es nicht einfacher, ein RUNDEN um die Datums+Zeitberechnung zu stellen?

=Let(a;RUNDEN(Datum($A$1;1;1)+Zeile()/24;6);...

Gruß Daniel

[schauan]

... hier wäre mal ein VBA-Code. Baut auch auf SEQUENZ und die Datumsberechnung auf.
Match sollte dafür sorgen, dass die Zeit keine Rolle spielt.

Code:

Option Explicit
Option Private Module

Sub Makro1()
Dim iCol%, DayOfYear%
'Anzahl Tage ermitteln
DayOfYear = DateValue([a1] + 1 & "/1/1") - DateValue([a1] & "/1/1")
'Eintrag ab aktiver Zelle - Bitte in dieser Version in Zeile 1
With ActiveCell
  'Spalte in Variable uebernehmen
  iCol = .Column
  'Sequenz-Formel eintragen
  .Formula2 = "=SEQUENCE(" & DayOfYear & "*24,,0,1/24)+DATE($A$1,1,1)"
  'Formel durch Werte ersetzen
  .Resize(DayOfYear * 24).Value = .Resize(DayOfYear * 24).Value
End With

'Sommerzeit feststellen und Daten eine Zeile nach oben kopieren und einfuegen
With Cells(Application.Match(CLng(Sommerzeit([a1])), Columns(iCol), 1), iCol)
  Range(.Offset(4, 0), Cells(DayOfYear * 24, iCol)).Copy
  .Offset(3).PasteSpecial
End With

'Winterzeit feststellen und Daten eine Zeile nach unten kopieren und einfuegen
With Cells(Application.Match(CLng(Winterzeit([a1])), Columns(iCol), 1), iCol)
  Range(.Offset(3, 0), Cells(DayOfYear * 24, iCol)).Copy
  .Offset(4).PasteSpecial
End With

'Unterste doppelte Zelle - Inhalt entfernen
Cells(DayOfYear * 24 + 1, iCol) = ""

End Sub

Public Function Sommerzeit(Optional ByVal Jahr As Long = -1)
Dim D  As Date
  If Jahr < 0 Then Jahr = Year(Date)
  Sommerzeit = Null
  If Jahr >= 1980 Then
    D = DateSerial(Jahr, 4, 1)
    Sommerzeit = DateValue(DateAdd("d", -DatePart("w", D, vbMonday), D))
  End If
End Function

Public Function Winterzeit(Optional ByVal Jahr As Long = -1)
Dim D As Date
  Winterzeit = Null
  If Jahr < 0 Then Jahr = Year(Date)
  If Jahr >= 1980 Then
    D = DateSerial(Jahr, 11, 1)
    If Jahr <= 1995 Then D = DateSerial(Jahr, 10, 1)
    Winterzeit = DateAdd("d", -DatePart("w", D, vbMonday), D)
  End If
End Function

[Sigi.21]

Hallo bigcuno,

zu den bisherigen Lösungen, habe ich als Alternative eine Lambda-Funktion. (s. Mappe)

Gruß Sigi

[Andreas Killer]

wäre es nicht einfacher, ein RUNDEN um die Datums+Zeitberechnung zu stellen?

Hallo Daniel,

Klares ja. Allerdings ist die Frage ob das überhaupt relevant ist. Um den "Fehler" zu sehen muss man schon die tausendstel der Sekunde sichtbar machen.
   

C1:  =LET(
SZ;DATUM(A1;4;)-WOCHENTAG(DATUM(A1;4;))+1+ZEIT(2;0;0);
WZ;DATUM(A1;11;)-WOCHENTAG(DATUM(A1;11;))+1+ZEIT(2;0;0);
JD;RUNDEN(SEQUENZ((DATUM(A1+1;1;1)-DATUM(A1;1;1))*24;;DATUM(A1;1;1);ZEIT(1;0;0))*86400;0)/86400;
JD+((JD>=SZ)*(JD<=WZ))*ZEIT(1;0;0))

BTW, ein sehr schöner Beitrag zu dem Thema kommt von der NASA:
How Many Decimals of Pi Do We Really Need?

Die viel relevantere Frage ist wozu das überhaupt gut sein soll, ich habe das in über 30 Jahren noch nie gebraucht...
Aber man lernt ja bekanntermaßen nie aus.  

Andreas.

[BoskoBiati]

Hallo,

wenn man in A1 ein komplettes Datum einsetzt, dann geht es auch so:


  Zeitumstellung - Stunden.xlsx (Größe: 161,28 KB / Downloads: 10)

[EA1950]

Hallo,

@slowboarder:

wäre es nicht einfacher, ein RUNDEN um die Datums+Zeitberechnung zu stellen?

in der beiliegenden Datei habe ich in den Zellen C2, F2 und G3 drei Formeln eingefügt, die die internen Verhältnisse verdeutlichen sollen. 

Dabei ist in C2 die Formel, die ich schon hatte, ergänzt um die Zeitart in Spalte D und um eine bedingte Formatierung (roter Trennstrich zwischen den Zeitarten) für Spalte C+D.
Die Werte in Spalte C und F sind identisch, nur eben anders formatiert, während in Spalte G die Differenzen zwischen den Zeilen von Spalte F (bzw. Spalte C) enthalten sind.

Wie man sieht, entstehen keine Rundungsdifferenzen, wenn man die Uhrzeiten vergleicht bei Verwendung von

    NACHZEILE(SEQUENZ((jNxt - jAnf) * 24; ; 0);
    LAMBDA(h; LET(ts; jAnf + h/24; ts+(ts>=SoZt)*(ts<WiZt)/24)));

zwischen Spalte C und F:

0 Uhr bleibt ...,0
3 Uhr bleibt ...,125
6 Uhr bleibt ...,25
...
12 Uhr bleibt ...,5
...
21 Uhr bleibt ...,875
... usw.

Das bleibt das ganze Jahr über so erhalten und es kommt nicht zu unschönen Differenzen für zB. 0 Uhr mit ...,9999973.. oder so ähnlich gegen Ende des Jahres, wenn man mit SEQUENZ((Nxt-Anf)*24;1; 0; ZEIT(1;0;0)) arbeitet.