Google Sheets - heures de travail de nuit avec Arrayformula, heures de week-end avec Arrayformula

Question

J'ai une liste de documents Google: https://docs.google.com/spreadsheets/d/1gB-t47BKusiwVM53lZi1dGjaDcscILP4yCMtEdtUTao/edit?usp=sharing

Où je reçois automatiquement des SMS du téléphone à la table. J'y ai déjà demandé, et j'ai obtenu une grande aide, comment résoudre certains problèmes, mais malheureusement, je suis à nouveau coincé là-bas avec un autre fomulas.

J'ai quelques problèmes:

Les formules ne peuvent pas être dans les cellules elles-mêmes, mais doivent être dans la "Arrayformula" Parce qu'autrement le SMS automatique pousse les formules vers le bas quand elles sont reçues, donc j'obtiens là les conseils de cette fonction + Index and count .

Passons maintenant à mes problèmes: il faudrait qu'il y ait dans la colonne M les heures travaillées pendant la nuit. Le travail de nuit commence à: 22h00 et se termine à 06h00. Ci-dessous est le tableau et sous le tableau je vais mettre les formules que j'ai sofar.

|    | 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> |                    |

Pour les heures travaillées (colonne J), ​​j'ai cette formule, mais le problème est qu'elle ne dépassera pas 24 heures. Le total pour J3 devrait être de 48 heures travaillées.

=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))))})

Alors j'ai ceci, mais il ne comptera pas correctement (colonne M) Les heures de nuit devraient être 22: 00-06: 00.

=(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 doit être 06h00. M3 devrait être 16

Je ne suis pas vraiment sûr de la mise en forme de celles, ets. C'est dans le tableau ci-dessus lié. Et les heures de week-end sont du samedi au dimanche. J'ai pensé que je le ferais avec des conditions si, puisque j'ai dans la table des jours de semaines si cela serait possible? Les heures de fin de semaine pour N2 devraient être de 4 heures.

Pour tout commentaire, je serais vraiment heureux.

Answer 1

Le problème fondamental que vous rencontrez, je pense, c'est que vous essayez de travailler avec du texte plutôt que de le convertir immédiatement en chiffres pour garder une trace de l'heure et des dates. J'ai fait un nouvel onglet sur votre feuille appelé MK.help et j'ai mis la formule suivante dans la cellule M1.

=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)))})

Vous noterez que j'ai également modifié toutes les autres formules que vous utilisiez pour extraire les heures et les dates de votre système. Vos formules étaient exagérées et elles semblaient reposer sur beaucoup de texte, plutôt que sur la manipulation des nombres.

Answer 2

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)}

Explication:

={"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                    
}                                                            

Remarques:

• INDEX/COUNTAest toujours préféré, mais j'ai utilisé à la ARRAY_CONSTRAINplace pour limiter la taille de la formule.
• Vous devez mettre en forme la colonne résultante comme "Durée"
• Toutes les colonnes de date et d'heure doivent être formatées et reconnues comme date et heure