Vous pouvez facilement apprendre à utiliser COUNTIFS dans une plage horaire dans Google Sheets. Ce n’est pas de la rocket science. Mais les choses se compliquent lorsque vous voulez utiliser COUNTIFS dans une colonne de date et d’heure appelée timestamp ou colonne DateTime.
Je vais vous donner les conseils nécessaires pour utiliser COUNTIFS dans une colonne d’heure ainsi que dans une colonne de date et d’heure (timestamp) pour compter une plage horaire spécifique.
En précis, la plage horaire ici signifie certains segments de temps d’une heure, de deux heures, etc. Par exemple, compter toutes les activités ou les billets qui ont été enregistrés entre 8h00 et 10h00.
Avant d’aborder cela, voyons comment utiliser le critère DateTime (Timestamp) dans Countifs.
La formule Countifs si A1:A contient DateTime :
=countifs(A1:A, ">=01/07/2018 08:05:00", A1:A, "<=01/07/2018 16:05:00")
Comment utiliser la fonction COUNTIFS dans une plage horaire dans Google Sheets
Ce tutoriel est divisé en deux parties. Dans la première partie, vous pouvez apprendre comment utiliser COUNTIFS dans une colonne d’heure pour compter la plage horaire. Dans la deuxième partie, je vous explique la même chose mais avec un timestamp ou vous pouvez dire une colonne DateTime.
L’utilisation de COUNTIFS dans une colonne d’heure
La formule ci-dessous est un exemple de la façon d’utiliser COUNTIFS dans une plage horaire dans Google Sheets.
La formule suivante compte la plage horaire de 08h30 à 09h30, y compris les deux heures. Si un moment dans la colonne A se situe dans cette plage, il est compté.
Formule 1 : Utilisation directe du critère d’heure dans la formule Countifs
=countifs(A2:A8, ">=08:30:00", A2:A8, "<=09:30:00")
Alternativement, vous pouvez saisir les critères dans les cellules E2 (08:30:00) et F2 (09:30:00) et vous y référer dans la formule.
=countifs(A2:A8, ">="&E2, A2:A8, "<="&F2)
Compter le temps par durée est aussi simple que ça. Mais les choses ne sont pas aussi simples si votre colonne A dans l’exemple ci-dessus contient des timestamps ou DateTime, et non pas seulement l’heure.
L’utilisation de COUNTIFS dans une colonne DateTime aka Timestamp
Comment compter l’intervalle de temps lorsque l’heure est au format date et heure ou timestamp ?
Ici, je peux vous suggérer deux ou trois options différentes. Tout dépend de la façon dont vous extrayez l’heure de la date.
Voici les formules d’exemple.
Formule 2 : Utilisation de Query+Split pour diviser DateTime
Comme vous pouvez le voir, la colonne A contient des timestamps. Vous ne pouvez donc pas utiliser la plage A2:A en tant que plage de critères dans COUNTIFS cette fois-ci.
Pour comprendre cela, reprenez ma Formule 1.
=countifs(A2:A8, ">=08:30:00", A2:A8, "<=09:30:00")
Maintenant, voyez la Formule 2.
=ArrayFormula(COUNTIFS(query(split(A2:A, " "), "Select Col2"), ">=8:30", query(split(A2:A, " "), "Select Col2"), "<9:30"))
Au lieu de la plage A2:A8 dans la Formule 1, j’ai utilisé une formule Query comme ci-dessous dans la Formule 2.
=ArrayFormula(query(split(A2:A, " "), "Select Col2"))
Note : La formule matricielle est déplacée au début dans la Formule 2.
Que signifie-t-il ?
J’ai utilisé la fonction SPLIT pour diviser la date et l’heure dans la colonne A en date et heure. Cela crée deux colonnes – une avec la date et une autre avec l’heure. Ensuite, j’ai utilisé Query pour sélectionner la colonne 2 qui contient l’heure.
Vous pouvez également obtenir le même résultat de différentes manières. Voyez cela dans les formules 3 et 4 ci-dessous.
Formule 3 : Utilisation de fonctions d’heure pour extraire l’heure de DateTime
Autre exemple de formule pour COUNTIFS dans une plage horaire dans Google Sheets.
Vous pouvez utiliser la même Formule 1 en remplaçant la plage A2:A par la formule suivante.
=ArrayFormula(TIME(hour(A2:A8), minute(A2:A8), second(A2:A8)))
Cette formule peut également extraire l’heure du timestamp et la formule finale serait comme suit :
=ArrayFormula(COUNTIFS(TIME(hour(A2:A8), minute(A2:A8), second(A2:A8)), ">=8:30", TIME(hour(A2:A8), minute(A2:A8), second(A2:A8)), "<9:30"))
Formule 4 : Utilisation de la formule REPLACE pour remplacer la date dans DateTime et extraire l’heure
Il s’agit de ma formule finale qui vous explique comment utiliser COUNTIFS dans une plage horaire dans une colonne de timestamp.
Je recommande cette formule lorsque vous voulez compter des segments de temps dans la colonne de date et d’heure. Pour moi, cette Formule 4 semble meilleure que les Formules 2 et 3 ci-dessus.
Là encore, la façon dont vous allez extraire l’heure de la colonne de timestamp est importante. Dans les deux exemples précédents, j’ai utilisé Query et des fonctions d’heure. Ici, je vais utiliser la fonction REPLACE pour cela. Ce code est plus propre.
Vous pouvez utiliser A2:A8 avec la formule REPLACE suivante.
=ArrayFormula(value(replace(A2:A8, 1, 11, )))
Voyez la formule finale et le résultat.
=ArrayFormula(COUNTIFS(value(replace(A2:A8, 1, 11, ))), ">=8:30", value(replace(A2:A8, 1, 11, )), "<9:30"))
Et voilà. Profitez-en !
