MS-Office-Forum
Google
   

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads Der Renner, 11 Entwicklertools für Access, Tipps & Trick und offene Datenbanken zum einzigartigen Preis.
Themen-Optionen Ansicht
Alt 08.09.2009, 16:41   #1
chris-kaiser
MOF Guru
MOF Guru
Strahlen Hinweis - Datum und Uhrzeit

Hallo allerseits

Die Fragen nach Datum und Uhrzeit sind schon das ein oder andere Mal aufgetaucht , darum habe ich mich mal aufgerafft und eine kleine Zusammenstellung gemacht die ich Euch gerne zur Verfügung stellen möchte.

Vorweg:
ALLES IST ZAHL
Die Excelzeitrechnung beginnt mit dem 00.1.1900 und das soll angeblich ein Samstag sein
Nun ja, es gibt keinen 00.01 genauso wenig wie den 29.02.1900 den Excel aber als richtig anerkennt. (Nobody is perfect ^^)
Die Zahl 0 wird eben mit 00.01.1900 im Datumsformat angezeigt
Die Zahl 1 mit 01.1.1900
Die Zahl 2 mit 02.01.1900
usw.
Daraus folgt 1 ist für Excel ein Tag, ein Datum ist nichts anderes als eine fortlaufende Zahl
Welchen Sinn hat das ->
Damit mit einem Datum gerechnet werden kann
z.B Bestelldatum minus Lieferdatum ergibt die Anzahl der Tage die seit der Bestellung vergangen sind
Möchte ich zu einem Datum 10 Tage dazuaddieren
=Zelle_mit_Datum+10

Heute (08.09.2009) haben wir laut Excelzeitrechnung 40064 (40064 Tage seit 1.1.1900)

Wie sieht es dann mit Stunden aus?
Da ja 1 ein Tag ist ergibt sich daraus das eine Std 1/24 ist
12:00 ist als Dezimalzahl 0,5 (ein halber Tag)
08:00 ist als Dezimal 0,333333 (ein Drittel)

Sind in Excel Stunden in Verwendung( Beginnzeit 08:00 Ende 16:00) kann die Stundenanzahl ermittelt werden mit =Ende-Beginn -> ergibt hier 08:00 würde das aber jetzt mit einem Stundensatz von sagen wir 30 € berechnet werden, kommt als Ergebnis 10 € raus
Warum blos, 08:00 sind ja als Zahl 0,333333, multipliziere ich 0,33333333 mit 30 kommt 10 heraus.
Immer wenn ich von einer Uhrzeit auf die Dezimalzeit kommen möchte MUSS die Uhrzeit mit 24 multipliziert werden. Im obigen Fall = Zelle_mit_der_Zeit*24*Stundensatz
Und die Ergebniszelle mit z.B € noch formatieren.

Wie sieht das dann für Minuten aus?
1 min = 1/1440
Die 1440 sind 24 *60 (ein Tag hat 24 Std eine Std 60 min)

Wie sieht das dann für Sekunden aus? (ich vermute mal Sie wissen was jetzt kommt )
1 sec =1/86400
Die 86400 sind 24*60*60

Hier noch Fragen die manchmal auftauchen

Frage: Warum wird mein Datum nicht richtig angezeigt es erscheint nur eine Zahl
Antwort: das Format der Zelle ist nicht auf Datum eingestellt. Klicken Sie die Zelle(n) an und ändern sie das Format der Zelle auf Datumsformat

Frage: Beim Kopieren von einem Datum aus einer anderen Mappe ändert sich das Datum um za. 4 Jahre.
Antwort: in einer dieser Mappen ist das 1904 Datumsformat eingestellt.(Extras- Optionen -> Berechnung -> Haken bei 1904 Datumsformat) , um das Datum wieder richtig auszugeben muss entweder das Datum +1462 gerechnet werden bzw. abgezogen werden , dies ist Abhängig davon in welche Mappe das Datum kopiert wird.
Fügen Sie das Datum aus einer Mappe ein in der das Format 1904 eingestellt war, in eine Mappe in der es nicht eingestellt ist, müssen die 1462 dazugerechnet werden, ist es umgekehrt die 1462 abgezogen werden.

Frage: Beim Einfügen einer Datumsspalte aus einer Datenbank (oder anderer externen Datei) wird das Datum nicht als Datum erkannt, auch wenn ich die Zellen formatiere ändert sich das Zellformat nicht)
Antwort: Das Datum war in der externen Datei als Text abgelegt in Excel wird es nun ebenfalls als Text angesehen, wenn die Zelle nun als Datum formatiert wird und ein Doppelklick in der Zelle vorgenommen wird, erkennt Excel diese Zelle wieder als Datum
Dies kann aber bei hunderten von Daten sehr mühsam sein.
Schnelle Abhilfen wären:
Spalte markieren -> Daten -> Text in Spalten -> Schaltfläche -> Ok
Eine leere Zelle markieren -> kopieren -> Spalte mit den Daten markieren -> Inhalte einfügen -> Vorgang addieren und ggf. danach die Zellen mit Datumsformat versehen

Frage: Mein Excel spinnt, „normale“ Zahlen werden als Datum angezeigt.
Antwort: die Zelle(n) liegen im Datumsformat vor, ändern sie das Format auf Standard, Zahl oder ein anderes beliebiges Zahlenformat.

Frage: Ich bekomme Uhrzeiten als Industriezeit wie kann ich diese als „normale“ Zeit anzeigen lassen. z.B 1,5 sollten als 01:30 angezeigt werden
Antwort: die Industriezeit dividiert durch 24 und das Ergebnis als Uhrzeit formatieren

Frage: wie kann ich eine Uhrzeit als Industriezeit ausgeben 01:30 soll 1,5 werden
Antwort: die Uhrzeit mit 24 Multiplizieren und das Ergebnis als Zahl formatieren.

Frage: ich addiere Zeiten zusammen dabei erscheint immer das falsche Ergebnis, es sollten über 24:00 angezeigt werden nur Excel macht das einfach nicht
Antwort: für Excel sind das nicht Stunden sondern Uhrzeiten und wie bei einer Uhr fängt Excel bei 24:00 wieder mit 00:00 an um mehr als 24:00 Stunden anzeigen zu lassen, ändern Sie das Format der Zelle Benutzerdefiniert auf [hh]:mm

Anbei noch eine Linkliste (alles über Datum und Uhrzeit was es sonst noch gibt)

http://www.online-excel.de/excel/singsel.php?f=128
http://bert-koern.de/excel/excel_tips_datum.htm
http://www.excelformeln.de/formeln.html?gruppe=1

__________________

Gruß Chris

Feedback nicht vergessen,
p.s Bitte keine PN (persönliche Nachrichten) mit Aufgabenstellungen schicken, Probleme sollten im Forum gelöst werden!
3a2920576572206973742064656e20646120736f206e65756769657269672e

Geändert von chris-kaiser (08.09.2009 um 18:04 Uhr).
chris-kaiser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 08.09.2009, 16:57   #2
EarlFred
MOF Guru
MOF Guru
Standard

Hallo Chris,

tolle Arbeit mal wieder! *Daumenhoch*

-> linked

Grüße
EarlFred

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 3 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,023% per 05.12.2017) - eine tolle Geste!
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 08.09.2009, 18:16   #3
Gerhard H
MOF Guru
MOF Guru
Standard

Hallo Chris,

das ist didaktisch ausgereift :-)
Hab ich mir zur weiteren Verwendung abgespeichert.

Apropos.. was ich schon immer einmal wissen wollte:
Ich kenne das Format [h]:mm und das Format [hh]:mm. Gibt es da einen Unterschied? Mir fällt keiner auf.

__________________

Gruß
Gerhard
Gerhard H ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 08.09.2009, 18:21   #4
chris-kaiser
Threadstarter Threadstarter
MOF Guru
MOF Guru
Standard

Hallo EarlFred, Hallo Gerhard

Danke erstmal

@ Gerhard
falls einmal nicht mehr als 10 Std sind z.B 04:00 + 02:00 +01:00 +02:00
würde bei [h]:mm als Ergebnis 9:00 rauskommen
bei [hh]:mm ist das Ergebnis immer mit führender 0 (09:00)

ansonsten gibt es keinen Unterschied.

__________________

Gruß Chris

Feedback nicht vergessen,
p.s Bitte keine PN (persönliche Nachrichten) mit Aufgabenstellungen schicken, Probleme sollten im Forum gelöst werden!
3a2920576572206973742064656e20646120736f206e65756769657269672e
chris-kaiser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.09.2009, 11:46   #5
Mykmaster
Neuer Benutzer
Neuer Benutzer
Standard

Einfach genial.
Sollte wirklich alles einmal gesammelt werden.....

Danke, Chris.
Mykmaster ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.02.2010, 16:00   #6
chris-kaiser
Threadstarter Threadstarter
MOF Guru
MOF Guru
Strahlen Ergänzung: Eingabe von einem Datum ohne Trenner

Hallo Leute

Da die Frage immer wieder auftaucht

Zitat:

"Wie kann ich ein Datum ohne Punkt, Bindestrich oder Minus eingeben"

folgender Code liegt im jeweiligen Klassenmoduls des Tabellenblattes

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'by Kaiser 2010
'Folgende Eingaben werden erkannt
'102 -> 01.02.10
'1112-> 11.12.10
'1114 -> Achtung das würde den 14.11.10 ergeben!
'10199 -> 01.01.99
'10022002 -> 01.02.02
If Target.Count = 1 Then
    Dim my_time As Date
    Dim my_format(3) As String
    my_format(0) = "dd/mm/yy"
    my_format(1) = "dd/mmm/yy"
    my_format(2) = "dd/mmmm/yy"
    my_format(3) = "dd/mm/yyyy"
    Dim my_Index As Integer
    my_Index = 3 'Hier den gewünschten Index der Formatierung bekanntgeben
    Dim my_Range As Range, dpl_target As Double, len_target As Integer
    Set my_Range = Columns("A:B") 'Bereich anpassen z.B Range("A1:F100")
    If Not Intersect(Target, my_Range) Is Nothing Then
        If Not IsDate(Target) Then
        'Nur Zellen ohne ein bestehendes Datumsformat werden bearbeitet
        'steht schon ein Datum in der Zelle dieses bitte zuerst löschen (Entfernen Taste)
            If IsNumeric(Target.Value) Then
                On Error GoTo ErrMsg
                Application.EnableEvents = False
                Target.NumberFormat = "General"
                 dpl_target = CDbl(Target.Value)
                 len_target = Len(Target.Value)
                 Target.Value = CDate(CheckTarget(dpl_target, len_target))
                 Target.NumberFormat = my_format(my_Index)
            End If
        End If
    End If
    Application.EnableEvents = True
End If
Exit Sub
ErrMsg:
Application.EnableEvents = True
End Sub

Function CheckTarget(Dbl_Date As Double, int_l As Integer) As String
Dim Str_Temp As String
Select Case int_l
    Case 3, 4
    Str_Temp = Left(Dbl_Date, int_l - 2) & "/" & Right(Dbl_Date, 2) & "/" & Year(Date)
    Case 5, 6
    Str_Temp = Left(Dbl_Date, int_l - 4) & "/" & Mid(Dbl_Date, int_l - 3, 2) & "/" & Right(Dbl_Date, 2)
    Case 7, 8
    Str_Temp = Left(Dbl_Date, int_l - 6) & "/" & Mid(Dbl_Date, int_l - 5, 2) & "/" & Right(Dbl_Date, 4)
End Select
    If IsDate(Str_Temp) Then
        CheckTarget = CDate(Str_Temp)
    End If
End Function
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
    Dim my_Range As Range
        Set my_Range = Columns("A:B") 'Bereich anpassen z.B Range("A1:F100")
         If Not Intersect(Target, my_Range) Is Nothing Then
            If Target.Value = "" Then Target.NumberFormat = "General"
         End If
End If
End Sub
Angehängte Dateien
Dateityp: xls datum.xls (45,0 KB, 42x aufgerufen)

__________________

Gruß Chris

Feedback nicht vergessen,
p.s Bitte keine PN (persönliche Nachrichten) mit Aufgabenstellungen schicken, Probleme sollten im Forum gelöst werden!
3a2920576572206973742064656e20646120736f206e65756769657269672e
chris-kaiser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 03.03.2010, 10:50   #7
chris-kaiser
Threadstarter Threadstarter
MOF Guru
MOF Guru
Jeck [Ergänzung2] Was ist die Mehrzahl (Plural) von einem Datum

Hallo an Alle

Der Plural von Datum ist Daten
Was aber anscheinend selten jemand verwenden will

Um in Foren keine Missverständisse hervorzurufen (damit es sofort ersichtlich ist, dass es sich um ein Datumsproblem handelt), würde ich in der Betreffszeile folgendes empfehlen

Ausdrücke wie:
Zellen die ein Datum beinhalten
Datumszellen
Zellen die ein Datum beinhalten
Spalte oder Zeile mit Datumsangaben (oder auch -eingaben)

und nicht
(ist glaube ich schon das ein oder andere Mal aufgedaucht)
Datume
Datums
Datumse
Datümer
Datumer
Datum's
Datüme
Datumen

__________________

Gruß Chris

Feedback nicht vergessen,
p.s Bitte keine PN (persönliche Nachrichten) mit Aufgabenstellungen schicken, Probleme sollten im Forum gelöst werden!
3a2920576572206973742064656e20646120736f206e65756769657269672e
chris-kaiser ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 19.07.2010, 17:49   #8
EarlFred
MOF Guru
MOF Guru
Standard

Hallo zusammen,

als Ergänzung zum Thema Datum & Uhrzeit (um letztere geht es):

In letzter Zeit kommt wieder häufiger die Frage auf, warum ein Sverweis, ein Vergleich oder ähnliche Funktionen mit Uhrzeiten „nicht funktionieren“.
Das heißt: Trotz augenscheinlicher Übereinstimmung der gesuchten Uhrzeit (=Suchkriterium) mit einer Uhrzeit aus dem Bereich, der durchsucht werden soll (=Suchmatrix), wird kein Wert bzw. ein Fehlerwert zurückgegeben oder der direkte Vergleich von Zeit1 = Zeit2 ergibt „FALSCH“.

Da Excel, wie eingangs durch Chris erläutert, Uhrzeiten als Fließkommawert berechnet und zudem die Wege, Uhrzeiten einzugeben oder zu errechnen, sehr unterschiedlich sein können, ergeben sich häufig Rundungsdifferenzen, so dass „gleich aussehende Zeiten“ eben keine exakte Übereinstimmung mehr besitzen (müssen).

Wer’s nicht glaubt, der schreibe in eine beliebige Zelle den Wert
0,701388888888889 und in eine andere den Wert
0,701389888888889
Der Vergleich beider Zellwerte ergibt logischerweise eine Differenz; als Uhrzeit formatiert (auch mit Sekundengenauigkeit) erscheinen die Werte jedoch gleich.

Wie diese Klippen umschiffen?
Ein paar Grundideen:

(1) Nutzen der Funktion =Zeit(Stunde;Minute;Sekunde)
(2) Runden der Uhrzeiten
(3) Umwandeln in Text


Im Beispiel gehe ich von folgendem Tabellenaufbau aus:
A1:A32: Liste mit Uhrzeiten, in der nach Übereinstimmung gesucht wird
B1:B32: Werte, die den Uhrzeiten zugeordnet sind
C1: Uhrzeit, nach der gesucht wird

zu (1)
Ein Konstrukt unter Verwendung der Variante (1) ist meist sehr lang und umständlich; daher erspare ich mir die Ausführung hierzu.

zu (2)
Zum Runden gibt es mehrere Philosophien:
Entweder rundet man auf eine Sekunde genau (wir wissen ja, dass 1 Sekunde = 1 / 24 / 60 / 60 = 1 / 86400 Tag ist), also mit der Funktion
Code:

RUNDEN(C1*86400;0)/86400
Analog wäre die Rundung auf Minuten durch 1440 (24*60) zu erreichen.

Wollen wir nun einen „Sverweis“ ausführen, könnte der wie folgt aussehen:
Code:

{=INDEX(A1:B32;VERGLEICH(RUNDEN(C1*86400;0)/86400;RUNDEN(A1:A32*86400;0)/86400;0);2)}
(Die geschweiften Klammern nicht mit eingeben, sondern die Funktion mit Strg+Shift+Enter abschließen)

Etwas vereinfacht reicht es aus, auf 5 Stellen (da 1/86400 > 1*10^(-5)) zu runden. Für den Fall, dass mich mein mathematisches Verständnis im Stich lässt, nehmt aber lieber 6 Stellen. Und wer vorhat, mich wegen falscher Tipps zu verklagen, der soll 15 Stellen nehmen.
Dadurch wird die Funktion etwas übersichtlicher:
Code:

{=INDEX(A1:B32;VERGLEICH(RUNDEN(C1;6);RUNDEN(A1:A32;6);0);2)}
(Die geschweiften Klammern nicht mit eingeben, sondern die Funktion mit Strg+Shift+Enter abschließen)

zu (3)
Mein Favorit:
Code:

TEXT(C1;"hhmmss")
Wer es nicht so genau braucht und für den die Minute das Maß aller Dinge ist, der nimmt
Code:

TEXT(C1;"hhmm")
und spart 2 Zeichen…

Ein „Sverweis“ sähe dann wie folgt aus:
Code:

{=INDEX(A1:B32;VERGLEICH(TEXT(C1;"hhmmss");TEXT(A1:A32;"hhmmss");0);2)}
(Die geschweiften Klammern nicht mit eingeben, sondern die Funktion mit Strg+Shift+Enter abschließen)

Kurz. Knackig. Gefällt mir am besten.

Noch eine Untervariante, wenn der Rückgabewert des „Sverweises“ eine Zahl ist:
Code:

=SUMMENPRODUKT((TEXT(A1:A32;"hhmmss")=TEXT(C1;"hhmmss"))*(B1:B32))
Mit dieser Funktion lassen sich auch die Werte aus Spalte B aufaddieren, sollte die gesuchte Uhrzeit mehrfach vorkommen.


Hoffe, ausreichend Verwirrung gestiftet zu haben
und lade Euch herzlich ein, noch andere Varianten aufzuzeigen.

Grüße
EarlFred

__________________

Datum und Uhrzeit, Makrorekorder-Code entschlacken, {Matrixformeln}
Tutorials zu Pivottabellen: Kurzeinstieg; Dynamischer Datenbereich; Daten und Zeiten gruppieren
Für 3 meiner Beiträge haben sich die Hilfesuchenden mit einer Spende an Wikipedia, die Tafeln oder Hilfe für krebskranke Kinder eV bedankt (das entspricht 0,023% per 05.12.2017) - eine tolle Geste!

Geändert von EarlFred (19.07.2010 um 17:54 Uhr).
EarlFred ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 03:49 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

Copyright ©2000-2010 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günther Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.
Beachten Sie bitte auch unsere Nutzungsbedingungen.