Google Sheets - Nachtschichtstunden mit Arrayformula, Wochenendstunden mit Arrayformula
Ich habe eine Google Docs-Liste: https://docs.google.com/spreadsheets/d/1gB-t47BKusiwVM53lZi1dGjaDcscILP4yCMtEdtUTao/edit?usp=sharing
Wo ich automatisch SMS-Texte vom Telefon zum Tisch bekomme. Ich habe dort schon vorher gefragt und große Hilfe bekommen, wie man einige Probleme löst, aber leider stecke ich dort wieder mit einer anderen Fomulas fest.
Ich habe einige Probleme:
Die Formeln können sich nicht in den Zellen selbst befinden, sondern müssen sich in der "Arrayformula" befinden. Da sonst die automatische SMS die Formeln beim Empfang nach unten drückt, erhalte ich dort den Rat dieser + Index- und Zählfunktion .
Nun zu meinen Themen: Ich würde dort in Spalte M Stunden durch die Nacht arbeiten müssen. Die Nachtarbeit beginnt um 22:00 Uhr und endet um 06:00 Uhr. Unten ist der Tisch und unten ist der Tisch. Ich werde die Formeln setzen, die ich habe.
| | D | F | G | I | J | M |
|----+-----------------+----------------+--------------+------------+-------------------+------------+
| 1> | Start work date | Start of work |End work date |End of work | Total hours worked| Night shift|
| 2> | 11.09.2020 | 16:00 | 12.09.2020 | 04:00 | | |
| 3> | 10.09.2020 | 07:00 | 12.09.2020 | 07:00 | | |
| | N |
|----+--------------------+
| 1> |Worked over weekend |
| 2> | |
| 3> | |
Für die geleisteten Arbeitsstunden (Spalte J) habe ich diese Formel, aber das Problem dabei ist, dass sie nicht länger als 24 Stunden dauert. Die Gesamtsumme für J3 sollte 48 Stunden betragen.
=ARRAYFORMULA({"Hours worked";--(DATEVALUE(G2:INDEX(G:G,Counta(D:D)))+TIMEVALUE(I2:INDEX(I:I,Counta(I:I))))-(DATEVALUE(D2:INDEX(D:D,Counta(D:D)))+TIMEVALUE(F2:INDEX(F:F,COUNTA(F:F))))})
Dann habe ich das, aber es zählt nicht richtig (Spalte M) Die Nachtstunden sollten 22: 00-06: 00 sein.
=(J2-(
If(F2<I2;
MAX(0;MIN(I2;TIMEVALUE("22:00"))-MAX(F2;TIMEVALUE("06:00")));
MAX(0;TIMEVALUE("22:00")-F2)+MAX(0;I2-TIMEVALUE("06:00"))
)
)*24)
M2 sollte 06:00 sein. M3 sollte 16 sein
Ich bin mir nicht sicher, wie Zellen gebildet werden sollen. Es ist in der Tabelle oben verlinkt. Und die Wochenendstunden sind von Samstag bis Sonntag. Ich habe gelernt, dass ich es mit wenn Bedingungen tun würde, da ich in der Tabelle Tage von Wochen habe, wenn das möglich wäre? Die Wochenendstunden für N2 sollten 4 Stunden betragen.
Für jedes Feedback würde ich mich sehr freuen.
Antworten
Ich denke, das grundlegende Problem, das Sie haben, ist, dass Sie versuchen, mit Text zu arbeiten, anstatt sofort in Zahlen umzuwandeln , um Zeit und Datum zu verfolgen. Ich habe auf Ihrem Blatt eine neue Registerkarte mit dem Namen MK.help erstellt und die folgende Formel in Zelle M1 eingefügt.
=ARRAYFORMULA({"night shift";IF(C2:C="";;IF((1-F2:F)*(I2:I<F2:F)>2/24;2/24*(I2:I<F2:F);(1-F2:F)*(I2:I<F2:F))+IF(I2:I*(I2:I<F2:F)>6/24;6/24*(I2:I<F2:F);I2:I*(I2:I<F2:F)))})
Sie werden feststellen, dass ich alle anderen Formeln geändert habe, die Sie ebenfalls verwendet haben, um die Zeiten und Daten aus Ihrem System zu extrahieren. Ihre Formeln waren übertrieben und sie schienen eher auf viel Text als auf Zahlenmanipulation zu beruhen.
M1:
={"Night shift";ARRAY_CONSTRAIN(ARRAYFORMULA(IF(G2:G-D2:D>=1,IF(--F2:F>--"22:00",1-F2:F,"2:00")+IF(--I2:I<--"6:00",I2:I,"6:00")+(G2:G-D2:D-1)*"8:00")),COUNTA(D:D)-1,1)}
Erläuterung:
={"Night shift";
ARRAY_CONSTRAIN( //constrain output
ARRAYFORMULA( //enforce array context
IF(G2:G-D2:D>=1, //if end work date > start
IF(--F2:F>--"22:00",1-F2:F,"2:00")//if start time >22, get hours left else 2 hours
+IF(--I2:I<--"6:00",I2:I,"6:00") //if end time < 6 use hours worked else cap it
+(G2:G-D2:D-1)*"8:00" //8 hours for full days
)
)
,COUNTA(D:D)-1,1) //constrain rows to count
}
Anmerkungen:
- INDEX/COUNTAwird immer noch bevorzugt, aber ich habe
ARRAY_CONSTRAINstattdessen verwendet, um die Formelgröße zu begrenzen. - Sie sollten die resultierende Spalte als "Dauer" formatieren.
- Alle Datums- und Uhrzeitspalten sollten formatiert und als Datum und Uhrzeit erkannt werden