Zeiterfassung in Excel...?
Ist es möglich, dass ich in Excel z.B. in Zelle A1 eine Zahl eingebe, dann in Zelle B1 die aktuelle Uhrzeit erscheint und beim zweiten eingeben der selben Zahl in einer ganz anderen Zelle (z.B. A38), dies automatisch erkannt wird und in C1 wieder die aktuelle Uhrzeit erscheint. Es geht um Einfahrt- und Ausfahrtszeiten... Erst wenn beim dritten Mal die selbe Zahl eingegeben wird, soll in einer neuen Zeile, das wieder mit der Einfahrtzeit begonnen werden.
3 Antworten
Wenn ich die Infos aus den anderen Antworten und Kommentaren richtig verstehe, erfasst Du doch (manuell) in Excel einen Code. Eine UserForm würde die Steuerung nochmals erleichtern. Ich habe mir für das Beispiel einfach auf die Userform eine TextBox und einen Button gepackt. Man könnte bspw. noch ein Label dazunehmen, Änderungen der TextBox überwachen und noch vor Knopfdruck den Namen der Firma und des Mitarbeiters im Label anzeigen (aber eher Spielerei).
Zum nachstellen brauchst Du besagte UserForm mit Textbox1 und Button1
Ich habe drei Tabellenblätter:
1. Logbuch (hier wird jede Erfassung mit Zeit mitgeschrieben),
2. Firmen: Spalte A der Code (X1/1 etc.), Spalte B Name der Firma und Spalte C optional ein Mitarbeiter. Ich kenne an der Stelle die Logik der Codes nicht: Gibt es immer einen Mitarbeiter oder wären auch Eingaben X3 möglich? Dann müsste man zum Plausbilisieren noch etwas herumbasteln
3. Zutritte
Die Namen sind egal, das kannst Du im Code hinterlegen (Zeile 10-12)
Als Makro beim Betätigen des Buttons folgenden Code:
Private Sub CommandButton1_Click()
'logbuch schreiben
Dim wo_log As Long
Dim wo_zutritt As Long
Dim zutritt As Long
Dim logbuch As String
Dim zutritte As String
Dim firmen As String
' die drei folgenden Einträge in "" durch Deine Tabellennamen ersetzen
logbuch = "Logbuch"
zutritte = "Zutritte"
firmendaten = "Firmen"
wo_log = Sheets(logbuch).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(logbuch).Cells(wo_log, 1).Value = TextBox1.Value
Sheets(logbuch).Cells(wo_log, 2).Value = Now()
'ein- und ausfahrt prüfen
wo_zutritt = Sheets(zutritte).Cells(Rows.Count, 1).End(xlUp).Row + 1
If WorksheetFunction.CountIf(Sheets(zutritte).Range("A:A"), TextBox1.Value) = 0 Then
Sheets(zutritte).Cells(wo_zutritt, 1).Value = TextBox1.Value
Sheets(zutritte).Cells(wo_zutritt, 4).Value = Now()
On Error Resume Next
Sheets(zutritte).Cells(wo_zutritt, 2).Value = WorksheetFunction.VLookup(TextBox1.Value, Sheets(firmendaten).Range("A:B"), 2, False)
Sheets(zutritte).Cells(wo_zutritt, 3).Value = WorksheetFunction.VLookup(TextBox1.Value, Sheets(firmendaten).Range("A:C"), 3, False)
Exit Sub
End If
'wenn es einen Treffer gibt, diesen suchen
For i = wo_zutritt To 2 Step -1 'rückwärts um den ältesten zu erst zu finden
If Sheets(zutritte).Cells(i, 1).Value = TextBox1.Value And Sheets(zutritte).Cells(i, 5).Value = "" Then 'ausfahrt
Sheets(zutritte).Cells(i, 5).Value = Now()
Exit Sub
End If
If Sheets(zutritte).Cells(i, 1).Value = TextBox1.Value Then 'einfahrt
Sheets(zutritte).Cells(wo_zutritt, 1).Value = TextBox1.Value
Sheets(zutritte).Cells(wo_zutritt, 4).Value = Now()
On Error Resume Next
Sheets(zutritte).Cells(wo_zutritt, 2).Value = WorksheetFunction.VLookup(TextBox1.Value, Sheets(firmendaten).Range("A:B"), 2, False)
Sheets(zutritte).Cells(wo_zutritt, 3).Value = WorksheetFunction.VLookup(TextBox1.Value, Sheets(firmendaten).Range("A:C"), 3, False)
Exit Sub
End If
Next
End Sub
Zum Einblenden der Userform
Sub zeigdich()
UserForm1.Show
End Sub
Das kannst Du auch über einen Button auf einem Tabellenblatt machen (Steuerelement).
Die Userform/Button entfällt das Überwachen des Tabellenblatts auf Änderungen. Man könnte zu Beginn auch eine Prüfung einbauen, ob es den eingegebenen Code überhaupt in der Tabelle Firmen gibt.
Zur Nachvollziehbarkeit:
Zunächst wird einfach ein Logbucheintrag erzeugt. Dann die Prüfung ob eine Ein- oder Ausfahrt vorliegt. Gibt es den Code noch gar nicht, muss es auf jeden Fall eine Einfahrt sein -> neuen Eintrag erzeugen.
Gibt es einen Eintrag wird von hinten her die Liste durchgesehen. Der neueste Eintrag wird damit zu erst gefunden. Eintrag ohne Austrittszeit = Ausfahrt, ansonsten bleibt ja nur Einfahrt. Das VLookup ist quasi sverweis in VBA, um die Firmen und Mitarbeiternamen zu übernehmen.
Aktuell kann man auch unbekannte Codes eingeben. Daher das on error resume next, da VLookup sonst eine Fehlermeldung ausgibt. Eine Prüfung direkt zu Beginn wäre schon sauberer.
erst mal DH für die Lösung!
ich hab mich gestern daran versucht (mit find, auch rückwärts, -ich steh damit auf Kriegsfuß und wollts endlich mal hinkriegen-) und bin dann auf die Merkwürdigkeit gestoßen, dass VBA in einem Worksheet_Change-µ schon bei
Debug.Print Target.Address & " <-1"
if Not Intersect(Target, Range("B2")) Is Nothing Then
Debug.Print Target.Address & " <-2" 'leer
aussteigt (B2=Eingabezelle anstatt einer extra Form): ist mir noch nie passiert!
Werde das dank Deiner Lösung jetzt auf die lange Bank schieben.
@ Ninombre: noch was:
Gibt es den Code noch gar nicht, muss es auf jeden Fall eine Einfahrt sein -> neuen Eintrag erzeugen.
könnte Schwierigkeiten geben, wenn gestern schon oder -am selben Tag- zeitweise vorher(und sei es nur zum Werkzeug/Brotzeit holen. Es muss auf alle Fälle getestet werden, ob ein letzter Besuch mit diesem Code eine Ausfahrt aufweist. also ein abgeschlossener Besuch ist.
@Michi: Was, wenn zwei oder 3 Personen im Auto sitzen? Dann 2 oder 3 Einträge? Oder geht die Verantwortlichkeit dann an die einfahrende Fa? Muttu klären!
1. Das Löschen des Textfeldes fehlt in der Tat, sehr nervig und fehleranfällig. Vor jede der drei Stellen mit Exit Sub muss aber nur diese Zeile rein
UserForm1.TextBox1.Value = ""
2. Prüfung auf ungültige Codes - ich bin davon ausgegangen, dass alles ungültig ist, was nicht explizit in der Liste Firmendaten steht. Man könnte alternativ dort auch eine Spalte erweitern mit "Zugang noch erlaubt". Erstmal die Variante, dass nur gültig ist was in der Liste steht:
Du brauchst auf der Userform noch ein Label-Element (das große A, nennt sich Bezeichnungsfeld in der Werkzeugsammlung).
Im Code direkt hinter
zutritte="Zutritt"
firmendaten="Firma"
Dim fa_name As String
If InStr(TextBox1.Value, "/") > 0 Then fa_name = Left(TextBox1.Value, InStr(TextBox1.Value, "/") - 1) Else: fa_name = TextBox1.Value
If WorksheetFunction.CountIf(Sheets(firmendaten).Range("A:A"), fa_name) = 0 Then
Label1.Caption = "ACHTUNG - UNBEKANNTE FIRMA!"
Beep
Exit Sub
End If
If WorksheetFunction.CountIf(Sheets(firmendaten).Range("A:A"), fa_name) > 0 And WorksheetFunction.CountIf(Sheets(firmendaten).Range("A:A"), TextBox1.Value) = 0 Then
Label1.Caption = "ACHTUNG - UNBEKANNTER MITARBEITER!" & WorksheetFunction.VLookup(fa_name, Sheets(firmendaten).Range("A:B"), 2, False)
Beep
Exit Sub
End If
Da kommt eben das Label zum Einsatz und das Beep erzeugt zumindest einen Ton. Ob auch eine Sirene geht müsste ich recherchieren ;-)
3. In dieser Prüfung wird der Teil abgefragt der vor dem / steht. Damit das klappt, sollten die Daten bei Firmen so gepflegt sein, dass immer auch X1 ohne /1... da steht, also der Eintrag nur für Firma. Zusätzlich können nach Belieben noch X1/1, X1/2 etc. da stehen, aber einmal die reine Firma.
Wäre auch eine Prüfung notwendig, ob die Kombination aus Firma und Mitarbeiter unbekannt ist? Aktuell wird wie gesagt nur die Firma selbst geprüft.
4. Was Du noch einbauen könntest, wenn das Labelfeld ohnehin da ist wäre eine eigene Function, die Firma und Mitarbeiter anzeigt
Private Sub TextBox1_Change()
Dim fa_name As String
If InStr(TextBox1.Value, "/") > 0 Then fa_name = Left(TextBox1.Value, InStr(TextBox1.Value, "/") - 1) Else: fa_name = TextBox1.Value
If Len(TextBox1.Value) < 2 Then
Label1.Caption = ""
Exit Sub
End If
On Error Resume Next
Label1.Caption = WorksheetFunction.VLookup(fa_name, Sheets("Firmen").Range("A:B"), 2, False) & " " & WorksheetFunction.VLookup(TextBox1.Value, Sheets("Firmen").Range("A:C"), 3, False)
End Sub
Da kd
Michi: Bzgl. unbekannter Mitarbeiter (Firma aber bekannt) wird in der aktuellen Version auch der Eintrag abgebrochen. Du müsstest aber festlegen, ob das so sein soll. Man könnte auch nur einen Hinweis ausgeben und trotzdem die Ein-/Ausfahrt vermerken.
Der Punkt von Iamiam ist auch wichtig: Wird die Liste tageweise geführt? Dann könnte es theoretisch zumindest eine Lücke geben, wenn am Ende des Tages (=wenn eine neue Liste begonnen wird), nicht alle Fahrzeuge/Mitarbeiter raus sind. Die Funktion findet keinen Eintrag und geht daher von einer Einfahrt aus. Die Einträge ohne Ausfahrt müsste man beim Löschen der Liste manuell übernehmen (oder eben per Makro)- Du müsstest beurteilen, ob diese Fälle möglich sind.
Danke, schon mal! Ich hab es heute Nacht nicht geschafft weiter zu basteln, werde mich aber heute Nacht dran versuchen. Werde auf jeden Fall Rückmeldung geben.
Da dürfen ruhig mehrere Einträge erzeugt werden.
Vielen Dank, ich versuche mich noch daran, aber ich glaube das ganze ist mir doch zu hoch. Ich schaue schon die ganze Zeit Videos in Youtube und jetzt weiß ich auch endlich wie man einen Button macht :´D vllt klappt´s ja noch...
Ach, richtiges Hexenwerk ist das nicht. Es braucht zwar schon einen gewissen Aufwand, um grundlegend die Funktionsweise von Excel und VBA zu verstehen. Danach kann man die Informationen ganz gut "zusammen-googlen". Im Zweifelsfall nochmal eine Frage stellen (ich hab soviel von anderen gelernt, dass ich mich auch verpflichtet fühle, nach Möglichkeit selbst zu helfen - geht anderen wohl auch so)
Es hat jetzt nach einigen Stunden endlich funktioniert =D vielen, vielen Dank nochmals. Wenn Du mir noch bei einer Frage helfen könntest, dann wäre ich echt glücklich.
Und zwar wäre es klasse, wenn sich der Inhalt des Textfeldes, nach Betätigen des Buttons von selbst löschen würde.
Gute Nacht noch! ...ich habe auch bald Feierabend ;-)
Das Problem ist eher, dass Excel keine fixierten Zeitfunktionen kennt. Das zweite Eingeben würde eine Neuberechnung aller Zellen bewirken, die die aktuelle Zeit (bspw. mit JETZT() ) beziehen.
Lösen lässt sich das nur mittels Makro / VBA.
Ich habe mal die Themen zur Frage ergänzt. Die Makro-Spezis tauchen bestimmt gleich auf ;)
Ok, mein Ziel ist es, dass ich über einen NFC-Tag, auf dem ein Text gespeichert ist (wie z.B. B1/1 - B1 steht für die Firma und /1 für den Angestellten), es hinbekomme, dass beim ersten einlesen die Einfahrtszeit und beim zweiten einlesen die Ausfahrtszeit in der Tabelle erscheint. Ohne dass ich immer wieder die Zelle suchen muss in der die Person eingefahren ist und sie anklicken muss, damit in der Zeile auch wieder die Ausfahrtszeit steht. Ist schwierig dass so zu erklären aber ich hoffe ihr wisst was ich meine...
ah, du suchst also einen Wert x ohne Kenntnis, ob und wenn ja wo der Wert das letzte Mal getriggert wurde zzgl. ob es ein- oder ausfahrt war.
Heißt du musst eine Spalte durchsuchen nach Wert x und 2 weitere Spalten nach zugehörigem Zeitpunkt (nur um festzustellen, ob aktueller Trigger für ein- oder ausf. steht)
Ich finde den Ansatz mit gerade und ungerade immer noch spannend.
Zugleich könnte mit der Verweisformel aus
http://www.excelformeln.de/formeln.html?welcher=30
der (bei mehreren der letzte) Treffer aufgespürt werden, damit nicht alle Zelle zugleich befüllt werden.
Aber ich bring das um die Uhrzeit grad nicht mehr zusammen. Oder aber ich steh schon dermaßen auf'm Schlauch, dass ich wieder um zuviele Ecken denk und die Lösung ganz trivial ist ...
Gute Nacht erst einmal
Man kann hier keine Screenshots einstellen, damit ich es besser erklären könnt, oder? Ich blick mittlerweile auch bald selbst nicht mehr durch... Ich versuche es aber nochmal einfach zu formulieren:
X1/5 ist auf einem NFC-Tag als Text gespeichert. Durch draufhalten auf den NFC-Reader wird X1/5 in eine Zelle in der ersten Spalte eingefügt.
In den Zellen daneben, sollen dann automatisch Name der Firma ("X1" = zugeordnet zu "Fa. Gerüstbau Muster"; "5" = dem Mitarbeiter zugeordnet muss aber nicht mit Name erscheinen!), Datum und Einfahrtszeit erscheinen. Und bei der Ausfahrt bzw. dem zweiten Mal draufhalten des NFC-Tags, noch die Ausfahrtszeit.
Ein Button wäre eine Alternative , die ganz einfach umgesetzt wäre.
So wie es bisher beschrieben ist, kann ich noch keine klare Regel erkennen. Du müsstest schon definieren, ob bestimmte Zellen, Spalten oder Zeilen überwacht werden sollen oder bestimmte Werte. Irgendeine Zahl irgendwo ist nicht so präzise.
Das Problem hab ich schon gelöst, dass die Zeiten fixiert bleiben...
Na dann...
Da die Eingabe ja anscheinend irgendwo in Spalte A erfolgen kann, muss diese durchsucht werden. Ein einfaches wenn A? = ... reicht nicht.
Da immer Ein- und Ausfahrt, ist die Anzahl der Zahl bei Einfahrt immer ungerade und bei Ausfahrt immer gerade. Lös das doch über ZÄHLENWENN() und ISTGERADE() bzw ISTUNGERADE()
Angenommen die inzugebende Zahl ist 5
wenn(istgerade(zählenwenn(A:A;"=5")); deine uhrzeitermittlung ; "")
bzw wenn(istungerade( ...
Leider ist der Aufbau deiner Tabelle unbekannt.
Denn ab der zweiten Überprüfung muss zusätzlich noch abgefragt werden, ob in der zugehörigen vorigen Ein-/Ausfahrt-Zeile schon etwas steht. Sonst wird bei allen Ein- bzw. Ausfahrten zugleich die Zeit gesetzt.
Der Ansatz ist sicherlich noch nicht ganz ausgereift/durchdacht. So abstrakt ohne Tabelle fällt mir das nicht so leicht.
Du willst Dich als Parkwächter oder Privatdetektiv betätigen? Meinetwegen.
Dein Vorschlag ist noch nicht optimal. Vllt wäre sowas besser:
Du gibst in zB E2 die Autonummer ein. Diese wird durch ein Ereignismakro auf die erste freie Zelle in Sp. A übertragen, zusätzlich daneben die Uhrzeit gesetzt.
Beim nächsten Eintrag (einer anderen Nummer in derselben Zelle E2) dasselbe.
Das Makro testet aber vorher, ob die Nummer schon erfasst ist und -wenn ja- Spalte C (Ausfahrtszeit) noch leer ist. Ist sie leer, wird die Ausfahrtszeit eingesetzt. Wenn nicht, wird das als neue Einfahrt eingetragen, samt Uhrzeit.
Ist das das, was Du wolltest?
weiß nicht, ob ich morgen dazukomme (und nur nach Rückmeldung!), heute jedenfalls nicht mehr.
Könntest Du mir übrigens sagen, WIE Du die Zeiten fixiert hast? Ich befürchte nämlich, dass Du eine xl-Einstellung verwendest, die riskant ist, weil sie ALLE Dateien erfasst (Iteration).
Wäre bei µ überflüssig.
Du liegst fast richtig mit Deiner Vermutung, ich bin seit einigen Jahren im Wach- und Sicherheitsgewerbe und habe auch die 3 jahrige Ausbildung zur Fachkraft für Schutz und Sicherheit hinter mir. Nun möchte ich auf meinem jetzigen Objekt eine Betretungsliste mit Excel erstellen, welche uns die Arbeit sehr erleichtern würde. Da momentan noch alles handschriftlich erfasst wird und anschließend in eine Excel Tabelle eingetragen.
So wie du das oben beschrieben hast, stell ich mir das auch vor, nur dass ich die Kennzeichen nicht brauche (da die Personen oft mit verschiedenen Fahrzeugen kommen), sondern nur die Firmen plus die Nummer die wir den Personen zugeordnet haben, Datum und Ein- und Ausfahrtszeit. Für Verbesserungsvorschläge bin ich dennoch offen und dankbar!
Jetzt steh ich aber auch noch vor dem Problem, dass wenn ich z.B. X1/1, X1/2 usw. in eine Zelle eingebe, Excel nicht mehr erkennt, dass es sich dabei um die auf X1 zugeordnete Firma handelt. Es sollten die Nummern /1, /2 usw. trotzdem dabei stehen, da wir diese an die jeweiligen Mitarbeiter der Firmen vergeben haben und somit nachvollziehen können, welche Mitarbeiter der Firmen sich auf dem Objekt aufgehalten haben, falls etwas abhanden kommt.
Zu Deiner Frage wie ich die Zeiten fixiert habe:
Sub Worksheet_change(ByVal Target As Range)'Sub Datum_Uhrzeit_daneben_setzen(ByVal Target As Range) 'wäre für Knopfzuweisungif not intersect(Target, Columns(1)) is nothing thenif Target <> "" then Target.offset(0, 1) = date: Target.offset(0, 2) = timeend ifend sub
das hattest Du mir in einer vorherigen Frage schon so beantwortet und funktioniert bisher auch ganz gut.
Vorerst wäre es das, mach Dir wegen mir aber keinen Stress deswegen, wenn es halt erst in ein paar Tagen klappt ist´s auch nicht so wild. Ich bin froh, dass man mir da überhaupt weiter hilft. Vielen Dank schon mal!
Jetzt ist mir doch noch was eingefallen... Wenn ich eine Firma habe, die nicht mehr Zufahrtsberechtigt ist, kann diese dann direkt rot hinterlegt werden oder sogar noch ein Signalton ertönen? Und wie könnte ich es hin bekommen, dass X1/1, X1/2, X1/3 usw alle als Firma mit dem Code X1 erkannt und trotzdem mit X1/1 in dem Blatt Zutritte so eingefügt werden, Ohne dass man jeden Code einzeln in die Liste "Firmen" eintragen muss sondern wie gehabt, nur X1 Fa. Mustermann?