Zahl oder Buchstabenwert gleichzeitig nutzen Excel?

Ich erstelle gerade einen Dienstplan

Ich möchte in Zelle A (steht für arbeitsbeginn) entweder eine Zahlenwert (z.B. "13:00") eingeben, alternative auch eine Kürzel (z.B. "s" - steht für Spätschicht) eingeben In Zelle B soll ähnliches passieren wie in Zelle A, nur das es nicht um den Beginn sondern ums Ende geht (also entweder "20:00" oder "L" (für langer Dienst)

Bei der Berechnung in Zelle C soll die Summer der Arbeitszeit erscheinen Also : C1: =SUMME B1 - A1

Nun soll also egal ob ich den Zahlen wert eingeben haben, oder das Kürzel, es soll immer das richtige Ergebniss kommen

BSp

 A          B          C   

1 13:00 20:00 07:00h

2 S 20:00 07:00h

3 13:00 L 07:00h

4 S L 07:00h

gibts so eine Formel?

Danke

in einer andere?

Answer 1

[DeeDee07]

Ich persönlich würde zwei Zusatzspalten einfügen für die Einträge S und L. Dann kannst du über eine WENN-Abfrage prüfen, ob diese Spalten diese Einträge enthalten und davon abhängig die Uhrzeit ausgeben lassen.

Falls du keine Zusatzspalten haben willst, geht es wohl auch so

=WENN(ISTZAHL(B1);B1;20/24)-WENN(ISTZAHL(A1);A1;13/24)

Hierbei ist es egal, welche Buchstaben drinstehen, solange sie in den entsprechenden Spalten stehen. Falls ein Ergebnis nur dann erscheinen soll, wenn beide Zellen ausgefüllt sind, dann:

=WENN(ANZAHL2(A1:B1)=2;
WENN(ISTZAHL(B1);B1;20/24)-WENN(ISTZAHL(A1);A1;13/24);"")

Answer 2

[daCypher]

Du könntest Zwischenspalten einbauen, in denen der Buchstabe in eine Zeit übersetzt wird.

Beispiel: Spalte A ist die eingegebene Von-Zeit. Spalte B ist die berechnete Von-Zeit mit der Formel =WENN(A1="S";ZEITWERT("13:00");A1) Spalte C ist die eingegebene Bis-Zeit, Spalte D ist die berechnete Bis-Zeit mit der Formel =WENN(C1="L";ZEITWERT("20:00");C1) Spalte E ist dann die getätigte Arbeitszeit mit der Formel =D1-B1 und dem Format "Uhrzeit".

Die Spalten B und D kannst du dann ausblenden und hast genau die Übersicht, die du haben wolltest.

Answer 3

[PWolff]

Da bräuchtest du mehrere Fallunterscheidungen mit WENN und ggf. SVERWEIS.

Um herauszufinden, ob Zahl oder Buchstabe: ISTZAHL

Um z. B. 7 Stunden / 07:00 Uhr in DatumZeit umzuwandeln: 7/24

Comments

[PWolff]

Für diesen Fall Formel für C1:

=WENN(ISTZAHL(B1);B1;WENN(B1="L";20/24))-WENN(ISTZAHL(A1);A1;WENN(A1="S";13/24))

Wenn jeweils mehrere Buchstaben möglich sind, SVERWEIS oder geschachtelte WENN.

[Danielblabla]

@PWolff

das war die Lösung, danke PWolff

[Danielblabla]

@PWolff

Jetzt habe ich noch folgendes Problem:

ich hatte davor eine Formel, die angeben hat, wenn ein eine weiteren Spalte eine U (Urlaub) oder ein K (Krankheit) angeben war, dann hat er automatisch die Tagesstundensollzahl eingetragen:

=WENN(ODER(E50="U";E50="F";E50="K");($J$9/24);SUMME(G50-F50-H50))

dazu kam jetzt noch die Formel von Ihnen für die Werte bei Arbeitszeit Beginn/Ende

=WENN(ISTZAHL(G50);G50;WENN(G50="N";24/24))-WENN(ISTZAHL(F50);F50;WENN(F50="N";4,5/24))

kann ich beide Formeln irgendwie verknüpfen:

Also wenn in Spalte E50 etwas steht dann soll er die erste Formel anwenden, wenn nicht dann die zweite?

[Danielblabla]

@Danielblabla

Hallo nun habe ich noch folgendes Problem:

ich hatte davor eine Formel, die angeben hat, wenn ein eine weiteren Spalte eine U (Urlaub) oder ein K (Krankheit) angeben war, dann hat er automatisch die Tagesstundensollzahl eingetragen:

=WENN(ODER(E50="U";E50="F";E50="K");($J$9/24);SUMME(G50-F50-H50))

dazu kam jetzt noch die Formel von Ihnen für die Werte bei Arbeitszeit Beginn/Ende

=WENN(ISTZAHL(G50);G50;WENN(G50="N";24/24))-WENN(ISTZAHL(F50);F50;WENN(F50="N";4,5/24))-H50

kann ich beide Formeln irgendwie verknüpfen:

Also wenn in Spalte E50 etwas steht dann soll er die erste Formel anwenden, wenn nicht dann die zweite?

Beispiel 1. Formel:

       Kürzel   Beginn   Ende  Pause  Summe

         E           F          G         H         I

50    u             ___     ___      ___       6

        

       Kürzel   Beginn   Ende  Pause  Summe

         E           F          G         H         I

50                   N         N                     19,5

Gruß Daniel

[PWolff]

@Danielblabla

Einfach in

=WENN(ODER(E50="U";E50="F";E50="K");($J$9/24);SUMME(G50-F50-H50))

den zu berechnenden Term

SUMME(G50-F50-H50)

durch den Term der Formel

WENN(ISTZAHL(G50);G50;WENN(G50="N";24/24))-WENN(ISTZAHL(F50);F50;WENN(F50="N";4,5/24))

ersetzen:

=WENN(ODER(E50="U";E50="F";E50="K");($J$9/24);WENN(ISTZAHL(G50);G50;WENN(G50="N";24/24))-WENN(ISTZAHL(F50);F50;WENN(F50="N";4,5/24)))

Wie bei Excel üblich, werden die Formeln mit zunehmender Schachtelungstiefe immer unübersichtlicher.

Answer 4

[Suboptimierer]

Füge zwei zusätzliche Spalten ein. Wenn A deine Eingabespalte ist, dann bestimmst du in Spalte B den zugehörigen Zahlenwert, in C dann die andere Eingabe, in D den in eine Zahl umgewandelten Wert aus C und in E dann die Summe.