Datumsabfrage/Sverweis

Computer: Software & Programmierung Office Software
#1

Hallo, ich habe folgende Formel:

=WENN(ISTFEHLER(SVERWEIS(A7;Mittelwerte!$L$2:blush:M$1060;2;WAHR));„Nein“;SVERWEIS(A7;Mittelwerte!$L$2:blush:N$1060;3;WAHR))

In A7 ist ein Datum mit Zeitstempel, das heißt z.B. der 1.1.2013 00:00, dann in A8 der 1.1.2013 0:15 usw. jeweils in 15 Minuten Abstand. Durch die oben angegebene Formel fragt Excel ab, ob es sich bei dem Datum in A7 um einen Feiertag handelt. Wenn ja, soll er dies aus dem Tabellenblatt „Mittelwerte“ automatisch ziehen, wenn nicht soll „Nein“ in der Zelle stehen. Das klappt soweit auch!
Jetzt zu dem Problem: Die Abfrage Feiertag ja oder nein klappt nur dann, wenn es 0:00 an einem Feiertag ist, um 0:15 wird mir sofort nein angezeigt, obwohl der Tag ja immer noch der gleiche wie um 0:00 Uhr ist. Deshalb hatte ich die Idee, in der Formel das A7 durch A7=Tag oder so ähnlich zu erweitern, dass Excel nur schaut welcher Tag ist in A7 und nicht welcher Tag + Uhrzeit, weil in der Feiertagsliste in „Mittelwerte“ habe ich natürlich nicht jeden Feiertag von 0:00 Uhr bis 23:45 angegeben.

Hat jemand eine Idee, wie ich das machen kann? Eine eigene Formel schreiben habe ich auch schon versucht, das hat geklappt allerdings dauert der Kalkulationsprozess dadurch deutlich länger, weshalb ich gerne mit dieser WENN / SVERWEIS Formel weiterkommen würde.

Danke für die Hilfe!

#2

Hallo Aktivos,

kannst du mal bitte eine Beispielmappe (keine 1000 Zeilen) hochladen mit fileupload, s. FAQ:2606

Gruß
Reinhard

#3

hallo
ein Datum mit Uhrzeit setzt sich aus dem Datum [Vorkommaanteil]
und Uhrzeit [Dezimalwert] zusammen.
zB 28.10.2013 6:00 entspricht 41575,25

>zwischen dem 1.1.1900 0:00 und dem 28.10.2013 6:00 liegen genau 41575,25 Tage.

Lage Rede kurze Lösung:
Ersetze A7 durch GANZZAHL(A7)

Gruß Holger

#4

Hallo,

wenn Du die Datumswert als Dezimalwerte formatierst, wirst Du feststellen, dass ein Tag dem Wert 1 entspricht, 12 Stunden dem Wert 0,5 usw.
Wie wäre es, wenn Du also noch eine FIX-Funktion verwendest, um einfach den dezimalen Teil „wegzuschneiden“?

HTH & Gruß
DF

#5

Wie wäre es, wenn Du also noch eine FIX-Funktion verwendest,
um einfach den dezimalen Teil „wegzuschneiden“?

Hallo DF,

was genau meinst du damit? Im engl. Excel gibt es wohl die Fixed() Funktion die im dt. Excel Fest() entspricht, meinst du das?
Oder z.B. das was Holger nahm, Ganzzahl() ?

Gruß
Reinhard

#6

Ich habe ein ähnliches Problem gelöst, indem ich eine Zusatztabelle mit den Feiertagen angelegt habe (Ostern nach dem Ansatz von Gauss, alles Andere kann man von dort aus berechnen, oder sind zu einem festen Datum). Dann eine (zugegebenermaßen lange) einfache Wenn-Dann Abfrage und Alles ist schön :wink:

#7

Hallo Martin,

Ich habe ein ähnliches Problem gelöst, indem ich eine
Zusatztabelle mit den Feiertagen angelegt habe (Ostern nach
dem Ansatz von Gauss, alles Andere kann man von dort aus
berechnen, oder sind zu einem festen Datum).

ja, so geht man vor. Dazu ggfs. noch Feiertage je nach Bundesland. Ggfs. auch Urlaubszeiten von Mitarbeitern…

Dann eine
(zugegebenermaßen lange) einfache Wenn-Dann Abfrage und Alles
ist schön :wink:

Warum muss die lang sein, wie lang isse denn, zeig doch mal.
Gern auch mit Beispielmappe.

Gruß
Reinhard

#8

Hallo Aktivos,

Ein Datum ist eigentlich nur Zahl der Tage seit dem Stichtag  ( 1.1.1980 - oder so ), die Uhrzeit wird als Nachkommastelle interpretiert  ( also jeweils ein Sechsundneunzigstel pro Viertelstunde ). Deshalb waren die bereits von anderen Tipp-Gebern genannten Angaben des Kürzens auf den Ganzzahl-Anteil korrekt.

Zusätzlich solltest Du dafür sorgen, dass der  SVERWEIS  identisch ist sowohl in der  ISTFEHLER - Abfrage als auch in der eigentlichen Routine

Dann heißt die Abfrage also 
( Achtung - leichte Unterschiede in der Schreibweise, da ich OpenOffice-Calc verwende ) :

=WENN(ISTFEHLER(SVERWEIS(GANZZAHL(A7);Mittelwerte.$L$2:blush:M$1060;2;0));„Nein“;SVERWEIS(GANZZAHL(A7);Mittelwerte.$L$2:blush:M$1060;2;0))

oder

=WENN(ISTFEHLER(SVERWEIS(ABRUNDEN(A7;0);Mittelwerte.$L$2:blush:M$1060;2;0));„Nein“;SVERWEIS(ABRUNDEN(A7;0);Mittelwerte.$L$2:blush:M$1060;2;0))

Mit Gruß,
                   Frank-Thomas