Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1280to1284
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zeit aus eine Zeile/Spalte berechnen

Zeit aus eine Zeile/Spalte berechnen
16.10.2012 22:20:16
Daniel
Hallo lieber Excel Freunde,
habe eine spezielle Frage zu Zeit Berechnung:
- kann man Anfangs und End Zeit aus eine Zeile bzw. Spalte berechnen?
z.B. in „C3“ steht 7:15-15:15“ Anfangs und Endzeit die durch „-„ getrennt sind.
Hier meine Beispiels Tabelle:
Tabellenblattname: Tabelle1
 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

 

SUCHE LÖSUNG FÜR DIESEN TABELLEN FORMAT

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2

 

 

24.09.2012

25.09.2012

26.09.2012

27.09.2012

28.09.2012

29.09.2012

30.09.2012

 

 

 

 

 

 

 

3

Employees

Start date

MON

TUE

WED

THU

FRI

SAT

SUN

Hours

 

 

 

 

 

 

4

MA1

01.09.2012

7:15-15:15

 

 

 

 

 

 

8:00 AM

In "J4 " sollte das Ergebnis so aussehen

 

 

 

 

 

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Benutzte Formeln:
D2: =C2+1
E2: =D2+1
F2: =E2+1
G2: =F2+1
H2: =G2+1
I2: =H2+1

Tabellendarstellung in Foren
Andere Variante mit getrennten Anfangs und Endzeiten kenne ich.
Das wäre in diesen Fall diese Tabelle:
Tabellenblattname: Tabelle1
 

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

7

 

 

24.09.2012

Wenn der obere Tabelle nicht funktioeniert diese Lösung würde ich benutzen

 

 

 

 

 

 

 

 

 

 

 

 

8

 

 

Sunday

 

Monday

 

Tuesday

 

Wednesday

 

Thursday

 

Friday

 

Saturday

 

9

Employees

 

25.09.2012

 

26.09.2012

 

27.09.2012

 

28.09.2012

 

29.09.2012

 

30.09.2012

 

01.10.2012

 

10

MA1

 

11:15 AM

Total

 

Total

 

Total

 

Total

 

Total

 

Total

 

Total

11

MA1

 

7:15 PM

 

 

 

 

 

 

 

 

 

 

 

 

 

12

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Benutzte Formeln:
C9: =C7+1
E9: =C9+1
G9: =E9+1
I9: =G9+1
K9: =I9+1
M9: =K9+1
O9: =M9+1

Tabellendarstellung in Foren
Dann würde in "D11" Formel mit wenn und oder und Zeitformat von 24 Stunden benutzen.
Vielan Dank für kommende Hinweise.
Daniel

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zeit aus eine Zeile/Spalte berechnen
16.10.2012 22:26:12
Daniel
... Formel für "D11" wäre...

  • =WENN(UND(A1<12;B1>14);"WAHR";"FALSCH")
    Hier folgt jetzt ein beliebiger Text, dann kommt die nächste Formel:
    =WENN(DATUM() < GESTERN; "Kann nicht sein")

  • Da gibt's mehrere Möglichkeiten, ...
    17.10.2012 04:10:06
    Luc:-?
    …Daniel;
    da ich vermute, dass du ein Xl mit US-/englischen Lokal-Einstellungen benutzt, dürfte die „klassische“ Variante eine der kürzeren sein:
    J4:=RIGHT(C4,LEN(C4)-SEARCH("-",C4))-LEFT(C4,SEARCH("-",C4)-1)
    Eine etwas exotischere Lösung bezöge sich auf die XLM-Fkt EVALUATE (dt AUSWERTEN), die nur in benannten Fmln verwendet wdn kann. Das würde allerdings das Speichern der Mappe als .xlsm oder .xlsb erforderl machen. Dazu muss aus dem Ausdruck in C4 eine Matrixkonstante in Textform erzeugt wdn. Das Ganze sähe dann so aus:
    Name Hours:={-1,1}*TIMEVALUE(EVALUATE("{"""&SUBSTITUTE(C4,"-",""",""")&"""}"))
    ⇒dann wäre J4:=SUM(Hours)
    Bei einem dt Excel müsste natürlich in beiden Fällen noch die Darstellungsform geändert wdn, wodurch die 2.Fml sogar etwas kürzer ausfallen würde als die 1.
    Gruß Luc :-?

    Anzeige
    AW: Da gibt's mehrere Möglichkeiten, ...
    17.10.2012 14:38:37
    Daniel
    Hallo Luc,
    Vielen Dank für Deine Hilfe. Bei Dein Vorschlag haut mir Excel die Fehlermeldung „ die eingegebene Formel enthält ein Fehler“ in „C4,LEN“ aus.
    Muss betonnen das ich zu Hause Excel in deutsche Version benutze und in Arbeit englische Excel Version. Vielleicht liegt dran. Habe auch Dein Formel auf Deutsch umgewandelt „=RECHTS(C4,LÄNGE(C4)-SUCHEN("-",C4))-LINKS(C4,SUCHEN("-",C4)-1)“.Hier auch derselbe Fehlermeldung.
    Auf jeden Fall mir wurde so eine einfache Formel gefallen die mir die Arbeitsstunden von Montag bis Sonntag ausrechnen wurde. Da ich leider kein Fachmann bin, in Herber Forum bekam ich sehr oft professionelle Hilfe. Dafür bin ich sehr dankbar an Menschen die hier Ihr Wissen weiter geben.
    Für alle weiteren Anregungen habe ich immer offenes Ohr.
    Danke
    Daniel

    Anzeige
    Bei dt Excel musst du auch , in ; wandeln, ...
    17.10.2012 16:09:18
    Luc:-?
    …Daniel,
    denn das ist idR hier das lokale Listentrennzeichen (falls du nichts anderes eingestellt hast). Außerdem würde ein Zeitformat mit . statt : idR auch nicht fktionieren. Da es aber bei deinem FirmenXl zu fktionieren scheint (ggf . eingestellt), habe ich keine Ersetzungen [analog Variante2 SUBSTITUTE(C4,".",":"), dt WECHSELN(C4;".";":")] in die engl Fml eingefügt, die ansonsten erforderlich wären, damit sie akzeptiert wird.
    Gruß Luc :-?

    AW: Bei dt Excel musst du auch , in ; wandeln, ...
    18.10.2012 04:07:20
    Daniel
    Hallo Luc,
    vielen Dank vorerst.
    Die Formel funktioniert mit Deut. Excel "=RECHTS(C4;LÄNGE(C4)-FINDEN("-";C4))-LINKS(C4;FINDEN("-";C4)-1)". Muss auch mit engl. Excel testen, bin gespannt ob es funktionieren wird.
    Wie könnte man die Formel für die ganze Woche anpassen?
    Die funktioniert leider nicht:
    "=RECHTS(C4:I4;LÄNGE(C4:I4)-FINDEN("-";C4:I4)-LINKS(C4:I4;FINDEN("-";C4:I4)-1))"
    viele Dank für Deine Hilfe Luc.
    Daniel

    Anzeige
    Wo soll diese Fml denn stehen, ...
    18.10.2012 11:00:04
    Luc:-?
    …Daniel?
    Üblicherweise kopiert man sie ja in jede Folgezeile (durch sog Ziehen, falls die Zellen unmittelbar aufeinanderfolgen). Dadurch passt sie sich an die neue ArgumentQuelle an. Wenn du die Fml aber so schreiben willst, wie von dir angegeben, muss sie natürlich als sog MatrixFormel eingegeben wdn → wie man das macht, steht in der xlHilfe. Auf diese Weise liefert sie dann keinen Einzelwert, sondern hier einen ganzen WerteVektor.
    Falls du aber eine Zusammenfassung aller Einzelwerte zu einem einzigen erreichen willst, wäre das eine SUM[ME], ebenfalls als MxFml mit deiner Fml als Argument, oder eine ProduktSumme (SUMPRODUCT, falsch übersetzt als SUMMENPRODUKT bekannt), die hier als normale Fml eingebbar wäre, obwohl sie im Grunde ja auch eine WerteMatrix (bzw hier einen -Vektor) verarbeitet.
    Gruß Luc :-?

    Anzeige
    AW: ... in "J4"......
    18.10.2012 15:51:15
    Daniel
    Hallo Luc,
    vielen Dank für ausführliche und professionelle Erklärung. Davon habe ich leider bisschen weniger Ahnung (Vektor Werte und Matrix Fml). Mit Matrix hatte ich weniger zu tun das einzige was ich noch weiß dass man Matrix mit Tasten Kombination „STRG+SHIFT+RETURN“ abschließen muss.
    Mit kopieren in jede Folgezeile wäre auch eine Möglichkeit aber dadurch wird die Tabelle breiter. Habe eigentlich nichts dagegen weil man die Zeilen danach ausblenden kann. Falls es machbar wäre mit eine Fml das wäre mir persönlich lieber (gegen Makro Lösung habe ich auch nichts dagegen falls es für Dich einfacher wäre). Diese Formel hilft mir für besseren Überblick zu haben wenn man Dienstpläne schreibt. Und Dienstpläne sind leider sehr variabel. Die ändern sich durch Wochen und Schicht Arbeiten.
    Vielen Dank für Deine Hilfe
    Daniel

    Anzeige
    Ich hatte deine Tabelle nicht mehr so ...
    18.10.2012 17:04:55
    Luc:-?
    …genau im Kopf, Daniel;
    die Tage einer Woche standen wohl alle hintereinander in einer Zeile. Standort J4: heißt das nun 'ne Summe über die Woche oder alle Tageswerte einzeln, aber in einer Zelle, nämlich J4?!
    Gruß Luc :-?

    AW: Ich hatte deine Tabelle nicht mehr so ...
    19.10.2012 11:01:59
    Daniel
    Hallo Luc,
    die Tage einer Woche standen wohl alle hintereinander in einer Zeile. Standort J4: heißt das nun 'ne Summe über die Woche oder alle Tageswerte einzeln, aber in einer Zelle, nämlich J4?!
    Genau das dachte ich auch, eine Summe von alle Tagen hintereinander in eine Zeile "J4- von Mon. bis Son." zu haben.
    Der Übersicht sehr wichtig. Daher eine einfache und kleine Tabelle mit speziellen Formel die die Summe von Arbeitsstunden von Montag bis Sonntag zusammenrechnet. Wenn ich den Dienstplan schreibe so hat man in eine Spalte „J“ Stunden Anzahl für geplante Woche.
    Mir ist noch was eingefallen: ist es möglich eine Buchstabe zuzuweisen vorm(oder in der Mitte oder am Ende)des Uhrzeit?
    In „C4 steht die Uhrzeit 7:15-15:15“. Wäre es möglich für Excel eine Buchstabe vor oder danach oder statt Minus Zeichen „-„ da eine Buchstabe „W“ zu haben. Statt (C4)„7:15-15:15“, (C4)„W7:15-15:15“ oder (C4)„7:15W15:15“ oder (C4)„7:15-15:15W“ zu haben.
    Zur Buchstabe Erklärung; „W“ deutet mir die Station wo der Mitarbeiter in geplante Woche arbeiten soll. Ich kenne leider nicht so spezielle Excel Formel daher wenn das sehr kompliziert ist bitte lass es aus. Mit so einer einfachen Formel bin auch sehr zufrieden.
    Danke
    Daniel

    Anzeige
    Die einfache AWort hast du schon, ...
    19.10.2012 15:33:53
    Luc:-?
    …Daniel,
    die Fml als Argument von SUMMENPRODUKT (SUMPRODUCT) verwenden, also =SUMPRODUCT(…dieformelohenführendesgleichheitszeichen…) und dabei die Adressbereiche der Fml auf alle Tagesstundenspalten erweitern, also C4:I4 statt nur C4 schreiben. Über das Andere denke ich noch nach.
    Gruß+schöWE, Luc :-?

    ...finde leider kein Ansatz....
    20.10.2012 01:10:04
    Daniel
    Hallo Luc,
    viele Dank für Diene Hilfe.Irgendwas stimmte nicht mit Fml-Fehler "#Wert"
    “ =SUMMENPRODUKT(RECHTS(C4:I4;LÄNGE(C4:I4)-FINDEN("-";C4:I4))-LINKS(C4:I4;FINDEN("-";C4:I4)-1))“.
    Tut mir leid das ich es nicht kapiere.
    Ich lade mal die Mappe hoch:
    https://www.herber.de/bbs/user/82212.xls
    In Spalten "J:O" sind die Fml die ich ausprobierte, leider ohne Erfolg.
    Es wäre toll eine Formel zu haben die Arbeitsstunden durch die ganze Woche(Spalte J) ausrechen kann.
    Vielen Dank für die kommende Hinweise oder Hilfe.
    Daniel

    Anzeige
    Naja, I4 ist natürlich nicht OK, wenn da ...
    20.10.2012 04:14:25
    Luc:-?
    …nichts drinsteht, Daniel;
    dann liefern SEARCH und FIND einen Fehler, was das ganze Ergebnis versaut. Ansonsten war bis auf die fehlende bzw unrichtige Formatierung nichts falsch. Musste allerdings noch TIMEVALUE einsetzen, weil ich gerade unter Linux im Netz bin und LO das mit dem SUMPRODUCT-Argument-Vektor nicht anders akzeptieren wollte (für nur eine Zelle aber schon). In Xl müsste es eigentl auch in der Summe ohne TIMEVALUE gehen, wenn nicht, dann so belassen.
    Anbei hier mal deine Datei zurück. Anderes später.
    SchöWE, Luc :-?

    Anzeige
    AW: Naja, I4 ist natürlich nicht OK, wenn da ...
    20.10.2012 23:16:34
    Daniel
    Hallo Luc,
    Erst vielen Dank für Deine Hilfe und Mühe. Es sieht so aus als Du der einzige bist der sich damit auskennt. Bin gerade dabei um die Fml zu ausprobieren. Dein Vorschlag ist fast nahezu genial.
    Ich erhalte die Fehler Meldung (#NV) wenn eine oder mehreren Zeilen (C4:I4) leer sind. Sobald ich die Zahlen "0:00-0:00" reintippe (in die Zeilen wo die MA frei sind) dann rechnet die Formel den richtigen Sunden Anzahl.
    Habe vergessen zu betonen das pro Woche die MA 2 Tagen frei haben, also entweder nehme ich mir vor den Zahl als frei "0:00-0:00" dann passt alles.
    Ansonsten hätte ich mir gedacht vielleicht kann Excel leichter rechnen wenn ich als Beispiel in „C4“ so was reinschreibe: AM7:15-15:15. Wie bereits betonnt wenn das zu kompliziert ist mit Deine Formel bekomme ich zumindest eine Lösung.
    Vielen Dank Luc und Dir auch schönes WE
    Daniel

    Anzeige
    Nee, das macht es nicht leichter, ...
    21.10.2012 05:35:38
    Luc:-?
    …Daniel,
    und das Leerlassen von Zellen sehe ich noch vor → dachte ursprgl, dass keine Leerzellen vorkommen.
    Nee, bin bestimmt nicht der Einzige hier, der dir helfen könnte – Ähnliches hatten wir schon (s.Archiv), weshalb möglicherweise sonst Keiner so recht Lust hatte, das anzufangen, und jetzt ist es ja ohnehin auf dem richtigen Weg. Denke, bis morgen abend wird's schon wdn.
    Gruß + schöSo, Luc :-?

    AW: Nee, das macht es nicht leichter, ...
    21.10.2012 20:09:23
    Daniel
    Hallo Luc,
    alles klar, Zeit spielt nicht so große Rolle.
    Ich hoffe dass Du das irgendwie hinbekommst.
    Trotzdem nochmal vielen Dank für Deine große Hilfe.
    Daniel

    So, das dürfte nun deine Fml sein, ...
    21.10.2012 22:08:49
    Luc:-?
    …Daniel:
    J4:{=SUM(IF(ISBLANK(C4:I4),0,RIGHT(C4:I4,LEN(C4:I4)-SEARCH("-",C4:I4))-LEFT(C4:I4,SEARCH("-",C4:I4)-1)}
    Da es jetzt (wg IF) doch eine MxFml wird, kann statt SUMPRODUCT auch das kürzere SUM verwendet wdn. Durch die Ergänzung wdn jetzt leere Zellen nicht mehr berücksichtigt → du musst sie also nicht mit 0-Uhrzeiten füllen, sie müssen nur wirklich ganz leer sein! Falls das auch anders sein kann, müsste ISBLANK durch ISNUMBER ersetzt wdn und die Argumente 2 und 3 von IF müssten ihre Plätze in der Notationsreihenfolge tauschen, also 0 nach hinten.
    Das mit dem zusätzl Buchstaben geht zwar, aber das ist ja wohl nicht immer derselbe, was die Fml nur unnötig komplizierter machen würde (zumindest, wenn er statt "-" verwendet wdn würde. Am Anfang oder am Ende geht eher → da müsste der nur für die Berechnung per Fml weggelassen wdn. Besser dürfte es aber sein, hierfür eine separate Spalte (zumindest, wenn die Station sich nicht täglich ändert) bzw Zeile vorzusehen.
    Falls erforderlich und Thread noch im Forum, einfach noch mal fragen, anderenfalls bei Bedarf neuen Frage-Thread eröffnen und auf diesen hier (dann im Archiv) verlinken, damit die Vorgeschichte ohne Suchen zugänglich ist.
    Gruß + schöWo, Luc :-?

    AW: So, das dürfte nun deine Fml sein, ...
    22.10.2012 02:15:45
    Daniel
    Hallo Luc,
    Vielen Dank für Deine Hilfe. Habe irgendwie wieder ein Fehler.
    Hier noch mal die Mappe;
    https://www.herber.de/bbs/user/82246.xls
    Habe Zeile "5" gelb markiert. Die Formel rechnet mind. 8 oder max. 16 Stunden.
    Probier mal wen Du Möglichkeit hast copy-paste mit Zeiten dann wirst Du es sehen.
    Vielleicht habe ich die Formel falsch auf deutsches Excel Version angepasst?!?!?
    Daniel

    Keine Ahnung, fktt alles, sogar ...
    22.10.2012 22:04:57
    Luc:-?
    …unter LibreOffice (dann mit TIMEVALUE), Daniel;
    in LO kann ich deinen Fehler nicht sehen, vermute , statt ; o.ä. bzw falsches ZellFormat. Dt Fassung fktt bei mir auch unter Xl12, aber das ist ja leider keine Garantie für Xl14 und mehr als Xl12 kann ich dir nun mal nicht bieten!
    Dt MxFml f.J4 ({} nicht eintragen, aber du weißt ja wie's geht!):
    {=SUMME(WENN(ISTLEER(C4:I4);0;RECHTS(C4:I4;LÄNGE(C4:I4)-SUCHEN("-";C4:I4))-LINKS(C4:I4;SUCHEN("-"; C4:I4)-1) }
    Achtung! Forumssoftware baut leider zusätzliche Leerzeichen, die aber nicht weiter stören sollten, in eine dt Fml ein.
    Gruß Luc :-?

    Hätte es mir ja denken können, ...
    22.10.2012 23:07:22
    Luc:-?
    …Daniel,
    du hast das falsche Format eingestellt! hh:mm und Analoges stellt eine Uhrzeit dar und weiter als bis 23:59:59 geht's nun mal nicht, dann kommt nämlich wieder 0:00:00! ;-)
    Um Stunden mit Minuten zu erhalten, musst du das benutzerdefinierte Format [h]:mm angeben. Dann ist da auch das FmlErgebnis von 56 Stunden sichtbar.
    Gruß Luc :-?

    302 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige