Excel-Formel gesucht: Verfügbarkeit nach Datum UND Uhrzeit prüfen?
Hallo zusammen,
Folgendes Problem: Ich habe eine Excel-Liste, wo angegeben wird, wie Fahrräder verfügbar, bzw vergeben sind.
Nun soll es so sein, dass ich in eine neue Zeile Datum und Uhrzeit eingebe (Spalte F - I) und mir dann automatisch in Spalte J - N angezeigt wird, ob das Fahrrad zur angegebenen Zeit schon vergeben ist oder noch zur Verfügung steht.
Hat da jemand eine Formel für mich?
Ich hatte schon überlegt, hinter jedem Datum und jeder Zeit eine Spalte mit Zählenwenn anzulegen und bei Werten über 1 ist das Fahrrad vergeben, aber das geht ja nicht, da...
- Datum und Uhrzeit verknüpft sind
- Wenn ein Fahrrad am 17.05.16 ausgeliehen werden soll und es schon eine Buchung vom 16.05.16 - 18.05.16 gibt, zeigt er an dass das Fahrrad zur Verfügung steht.
Hat also jemand eine andere Idee?
Danke im Voraus!
Hier noch zwei Screenshots mit "vorher-" und "nachher"-Ansicht
4 Antworten
Du brauchst ja auch noch die Wunschzeiten!
Dann musst Du die Anfangs- und Endzeiten wechselseitig verschränkt abfragen:
Also schreib in A3 (wie schon Ausgeliehen von) zB: 15.05.2016 00:00:00
und in B3 (ausgeliehen Bis) zB: 16.05.2016 00:00:00
in C3 (wie Call for): zB: 16.05.2016 00:00:00
und in D3 (wie Dsurück) zB: 18.05.2016 00:00:00
Die Eingabe der Uhrzeit kannst Du dir sparen, wenn es um ganze Tage geht und Du den Folgetag der Rückgabe einträgst. Mit Uhrzeiten gehts aber selbstverständlich auch. Bei der Rückgabe solltest du einen Sicherheitszuschlag (nach Ermessen/Erfahrung) einplanen.
Formel: '=WENN((A3>C3)*(A3◄D3)+(B3>C3)*(B3◄D3)+(C3>A3)*(C3◄B3)+(D3>A3)*(D3◄B3);"sorry";"geht")
gleichbedeutend mit: '=WENN(ODER(UND(A3>C3;A3◄D3);UND(B3>C3;B3◄D3);UND(C3>A3;C3◄B3);UND(D3>A3;D3◄B3));"sorry";"geht")
Die ◄ musst Du alle durch < ersetzen, der GF-Editor lässt mich das nicht reinkopieren. Hinterher das Hochkomma vor dem = entfernen.
Wenn du nun die Zeiten probeweise variierst, kannst du alle möglichen Situationen nachstellen.
Wir deine Anfrage vereinbart als Ausleihe, kannst Du in zB A5 =C3 und in B5 =D3 schreiben (kopierbar, wenn auch nur geringe Erleichterung) oder die Formel 2 nach rechts kopieren und in E und F neue Prüfanfrage-Zeiten eintragen
Am besten verschiebst Du die Spalte mit dem individuellen Fahrradnamen noch nach ganz links, dann kommt sich nichts in die Quere!
eine einfachere Lösung fällt mir nicht ein!
Vielen lieben Dank für deine Mühen an Heiligabend!
Deine Formel klingt an sich ja echt spitze, aber wenn ich dir richtig deute, vergleicht die nur die Wunschzeiten mit dem einen Datensatz in Zeile 3.
Ich muss aber ja die Wunschzeiten mit jedem Datensatz abgleichen.
Also A2:A1000, B2:B1000 u.s.w.
Allerdings könnte ich das so machen, dass ich die Wunschzeiten aus der letzten Zeile in alle anderen kopiere und danach mit VBA nach einer Zeile mit "sorry" suche.
wenn Du mit meiner Datenanordnung zurechtkommst, wie beschrieben, werde ich mich aufgrund dessen des Themas nochmal annehmen, allerdings erst heute spät am Abend. Mit Matrixformeln sollte da was gehen, hab aber noch keine rechte Vorstellung, wie.
(ich dachte, Du wolltest immer die Anfrage nach einem bestimmten" Ausleihfahrrad befriedigen. Wie Unterscheidest Du Gefährte gleichen Typs? Kannst du Zyp und einzelnummer in 2 verschiedene Zellen schreiben? Also zB Fahrrad Siebengang Kette Elektro | 123 oder noch besser/allgemeiner abfragbar Fahrrad | Siebengang | Kette | Elektro | 123 (natürlich sind alternativ auch Kürzel möglich, nur nicht gemischt Kürzel<->lang!) Man müsste allerdings entweder leer lassen oder nach etwas bestimmtewm fragen, zwi Eigenschaften derselben Kategorie (7- oder 21-Gang) wären nur mit 2 Formeln bzw einer sehr komplizierten Einzelformel möglich)
Letzteres gäbe zwar eine große (nicht allzu komplizierte) Formel, könnte aber alle Wünsche auf einfache Weise abfragen?
Im Folgenden tauchen viele Logik-Ergebnisse auf, da gilt generell: 1: geht, 0: geht nicht
Da ich Probleme bei der Resevierung eines Gefährts für mehrere Zeiträume vorhersehe (mit Zeit-Lücken dazwischen), habe ich das mal exemplarisch für zwei solche Zeiträume gemacht, das lässt sich relativ einfach erweitern auf noch mehr.
Folgende Anlage (kannst die Spalten ja später so platzieren, wie Du es wünscht/brauchst.(ich beziehe mich hier auf Zeile 11 -weil das für suchen/ersetzen eindeutig sein dürfte-, die Formeln und Zeiträume kann man noch oben/unten kopieren (aber aufpassen, dass Du dabei endständige Ziffern/Datümer nicht veränderst, also kopieren, nicht nach oben/unten ziehen!!):
- E3: Wunschtermin Anfang Datum , ggf mit Zeit
- F3: Wunschtermin zurückgegeben (nicht Rückgabe, sondern Folgetag=wieder verfügbar! ggf mit Zeit, dann plus Sicherheitsspielraum für verspätete Rückgabe)
- A11: vorhandene Reservierung Anfang, zB: 16.Mai 16
- B11 wieder verfügbar am (Res Ende) zB : 17.Mai 16 --- Beachte: das Rückgabedatum beinhaltet -sofern keine Zeiten eingetragen sind- den NÄCHSTEN Tag (Annahme:0:00)
- C11: vorhandene 2. Reservierung Anfang 19.5.16 (beliebiges Datumsformat)
- D11: nach vorhandener 2. Reservierung wieder verfügbar: 22.5.16 13:30
- E11/F11: leer, vorgesehen für Erweiterung auf 3 schon reservierte Zeiträume
- G1: Prüfformel für Zeitraum der 1. Reservierung (muss 0 oder 1 liefen): =WENN((A11>$E$3)*(A11◄$F$3)+(B11>$E$3)*(B11◄$F$3)+((A11+B11)/2>$E$3)*((A11+B11)/2◄$F$3)+($E$3>A11)*($E$3◄B11)+($F$3>A11)*($F$3◄B11);0;1)
- H1 zunächst leer, bei Formel in G die ◄ durch < ersetzen (überlass ich Dir)
- G1 nach i1 kopieren
- H:H LeerSpalte löschen (Den Trick mit -später gelöschter- leerer Zwischenspalte zum Kopieren der Formel kannst Du später wiederholen, wenn Du noch mehr Reservierungszeiträume pro Gefährt haben willst.)
- G:V so schmal machen, dass gerade 2 Zeichen drin Platz haben (bei mir 22 Px)
- Vor weiteren Formeln braucht es ein paar Spaltendefinitionen, ich springe zu Q11, dahinein(und in die Folgenden) kommt eine technischer Definition: zB K für Kettenschaltung (Kundenwünsche), das impliziert keine Rücktrittbremse, weitere Kürzel mkannst Du selbst definieren, zB N für Nabenschaltung, EU=Elektrische Unterstützung etc. Irgendwas MUSS in die Spalte, ggf s für sonstiges o.ä.
- Den zugehörigen Kundenwunsch trägst Du (fett?) in Q3 ein, hier eben K
- R11: Zahl der Gänge hier 3 Korrespond. Kundenwunsch: hier auch 3. Sagt der Kunde, die Eigenschaft spielt keine Rolle, kann die Zelle für Kundenwunsch R3 in allen Fällen(hier Q3:V3 auch leer bleiben
- T11: J für Jugendfahrrad (H für Herren, D für Damen etc. Kundenwunsch in T3
- Weitere Eigenschaften und ihre Kürzel kannst Du selbst hinzufügen, ggf später in neu eingefügte Spalten vor V. Du musst aber alle Zellen bis V mit irgendwas füllen! (s.o: zB s für sonstiges oder x)
- in W der Name des Gefährts, zB Hersteller,Nr 12345678 oder Trivialname, beliebig. ist nur beschreibend, wird später über Formeln ausgelesen.
- Formel in i11: =(Q11=Q$3)+(Q$3="") Diese Formel kann auch nach rechts kopiert werden. Sie prüft die Übereinstimmung von Kundenwunsch und Gefährt-Eigenschaften.
- Formel i11 kopieren bis O11 Bei obigen Vorgaben müssen jetzt lauter 1 drinstehen
- die Fo: P11: =PRODUKT(G11:O11)*ZEILE(P11) liefert die Zeilennummer, aber nur dann, wenn ALLE Bedingungen erfüllt sind, ansonsten 0
- Spalte W muss oberhalb des ersten Gefährt-Namens leer bleiben (ggf Kommentarfunktion oder kleine Textfelder nutzen). Da dieser aber ein Text ist, kannst Du die Spalte schmäler machen als der Eintrag erfordern würde.
- X3: eine 1, darunter eine 2 usw. Ist Vorgabe für die 1.-, 2.-, 3.- größte Zeilennummer übereinstimmender Einträge.(=zum Zeitraum verleihbarer Gefährte)
- Y3: Fo =KGRÖSSTE(P:P;X3)
- Z3: Fo für verleihbare Gefährte(Namen): =INDEX(W:W;Y3)
- Die Zeile, wo das Gefährt steht, entnimmst du Spalte Y
- Du kannst die wichtigen Einträge auch in eine eingefrorene Titelzeile übernehmen, Datumseinträge bedingt formatieren, wenn si in der Vergangenheit liegen und entfernt berden können uvam,.
- Ach ja, nach einer positiv beschiedene Kundenanfrage musst Du natürlich E3:F3 als Reservierung einfügen.
- Woran ich jetzt nicht gedacht habe: den Kundennnamen festhalten! aber da fällt Dir sicher selbst noch was zu ein!(ich denke da an Zwischenspalten zwischen den Leihspannen, nach Erstellung von det janze nachträglich eingefügt. Automatisches Festschreiben nur per VBA und nur, wenns in der Praxis wunschgemäß funktioniert.
Das wars auch schon ;))
Ich wünsch Dir viel Erfolg bei der konkreten Ausarbeitung, bei Problemen frag gern nochmal zurück (hier als Kommentar, damit ich das sehe) Halte Dich aber bei der Aufstellung genau an das Geschriebene, ich hab mir viel Mühe gegeben, das alles vollständig und eindeutig zu erstellen.
Ich glaube nicht dass das was wird. Ich würde MS Access nehmen und drei Tabellen anlegen.
- tbl_Fahrraeder
- tbl_Reservierungen
- tbl_Entleihungen
Ggf. Noch eine Tabelle tbl_Kunden
Dann brauchst Du ein paar Formulare, und viel VBA - ich würde für die Umsetzung etwa zwei Wochen benötigen, aber meine Lust so etwas für Andere zu entwickeln hält sich in Grenzen.
Ich kann dir nur raten dich an einen Profi zu wenden oder dich in Bezug auf Datenbankentwicklung mit Access entsprechend weiterzubilden.
Hallo,
Erstmal danke für die schnelle Antwort.
Mit Access hätte ich das Problem sehr schnell gelöst. Ich habe jedoch nicht die Möglichkeit MS Access zu nutzen, da ich die Reservierung beruflich benötige.
In Excel dürfte das auch locker möglich sein und dieses ist auch nicht in zwei Wochen Zeitaufwand zu erledigen.
Leider hat mir deine Antwort nicht wirklich geholfen.
Danke trotzdem
Also wenn du für die Imlementierung irgendwie an Access rankommst, dann kannst Du die fertige Datenbank später auch mit einer Laufzeitbibliothek auf Rechnern nutzen auf denen kein Access installiert ist.
Das einzige das in der Laufzeitbibliothek nicht funktioniert, ist die Rechtschreibkorrektur.
ich hab das in xl in 6 Tagen(nebenher) fertiggebracht - ohne VBA! (noch nicht mal Matrixformeln, dazu Extras wie mehrere Resevierungszeiträume) Ich brauche lediglich eine Menge Hilfsspalten, aber die sind in xl ja bekanntlich umsonst zu haben...
Wer mit Access umgehen kann, hat meistens sein xl-Wissen nicht aktualisiert und unterschätzt dessen Möglichkeiten! (ich machs ja umgekehrt genauso: warum sollte man sich in mehrere Programme mühsam einarbeiten, wenn es mit dem einen, das man kann, auch geht?)
Nur: in Access kriegst Du nur wenig Hilfe, ausser eben professioneller, die was kostet. Ist klar, dass Profis keine Lust haben, anderer Leute Arbeit umsonst zu machen. Für viele xl-Interessierte sind dagegen solche Aufgaben wie hier das, was für andere Leute Sudoku ist, oder Kreuzworträtsel. (wobei sich natürlich jeder über positive Rückmeldungen oder erst recht einen Stern freut)
Müsste funktionieren mit
"wenn(und(Zelle x <>"";Zelle y <>"");1;0)
Oder so ähnlich :-)
Bzw gibt's da was mit "istleer" und 0 und 1 dann tauschen
sehe grade noch einen Fehler: wenn exakt die gleichen Datümer/Zeiten verwendet werden (100%ige Überschneidung) wird das als möglich interpretiert. Die Formel wird sich dadurch verkomplizieren, hab die Lösung zwar schon im Kopf (zusätzlich noch einen Mittelwert abfragen, einmal reicht da), kann sie aber vor Hl.Abend vorr. nicht mehr übermitteln!