Récemment, j’ai aidé l’un de mes lecteurs à concaténer correctement l’heure de début avec l’heure de fin dans Google Sheets, selon sa préférence. Sa préférence était de combiner une heure de début et une heure de fin au format 24 heures, puis de le formater au format 12 heures en conservant également l’indication « am » ou « pm » dans le résultat. Cette concaténation de valeurs temporelles implique également la suppression de certaines sous-chaînes / caractères indésirables du résultat (répétition de « am »), que j’expliquerai plus tard. Dans ce nouveau tutoriel Google Sheets, je vais expliquer comment concaténer correctement une heure de début avec une heure de fin dans Google Sheets.
Problème de mise en forme lors de la concaténation de deux cellules temporelles
Voici un conseil de formatage important associé à la concaténation de cellules temporelles. Normalement, lorsque nous concaténons une heure de début avec une heure de fin en utilisant l’esperluette, le résultat peut manquer du formatage horaire. Regardez l’exemple ci-dessous :
Heure de début Heure de fin
Si nous concaténons l’heure de début ci-dessus dans la cellule A2 avec l’heure de fin dans la cellule B2 dans Google Sheets, la formule et le résultat seront les suivants :
=A2&" "&B2
Résultat : 0.416956018518519 0.892361111111111
Frustrant, n’est-ce pas ? Le résultat sera différent si nous concaténons une heure de début avec une heure de fin en utilisant l’une des fonctions JOIN ou TEXTJOIN.
=join(" - ",A2:B2)
=textjoin(" - ",true,A2:B2)
Résultat : 10:00:25 – 21:25:00
Ce résultat est correct jusqu’à un certain point. Mais ce que je veux, c’est quelque chose de différent, comme suit :
Résultat attendu : 10 am – 09:25 pm
Je ne veux pas afficher les secondes et supprimer les minutes si elles sont à 00. De plus, je ne veux pas répéter « am » si l’heure de début et l’heure de fin se situent dans la première moitié de la journée (première période de l’horloge de 12 heures). Vous ne comprenez pas ?
Regardez la 4ème et la 5ème ligne de l’exemple ci-dessous :
Concaténation de l’heure de début au format 12 heures ou 24 heures avec l’heure de fin en utilisant la méthode normale
Nous pouvons utiliser la fonction TEXT pour combiner une heure de début au format 12 heures ou 24 heures avec une heure de fin au format 12 heures ou 24 heures dans Google Sheets.
Voici comment j’utilise la fonction TEXT avec la fonction JOIN ainsi qu’avec l’esperluette pour concaténer une heure de début avec une heure de fin dans Google Sheets :
Mise en forme de l’heure et combinaison à l’aide de TEXT et de l’esperluette :
=text(A2,"hh:mm am/pm")&" - "&text(B2,"hh:mm am/pm")
Mise en forme de l’heure et combinaison à l’aide de TEXT et de JOIN :
=join(" - ",text(A2,"hh:mm am/pm"),text(B2,"hh:mm am/pm"))
J’applique la dernière formule dans la cellule D2 et la fait glisser vers le bas pour copier la formule. En utilisant une formule de tableau dans la cellule D2, la formule s’étendra automatiquement à la plage D2:D7. Dans ce cas, nous pouvons utiliser uniquement la combinaison Texte et Esperluette.
=ArrayFormula(text(A2:A7,"hh:mm am/pm")&" - "&text(B2:B7,"hh:mm am/pm"))
Oui ! La concaténation de l’heure de début avec l’heure de fin basée sur l’esperluette a l’avantage de la formule de tableau par rapport à la combinaison TEXT et JOIN !
Voyez comment la sortie dans la colonne D est différente de la sortie attendue dans la colonne C. Je pense que les heures de début et de fin combinées dans la colonne C sont plus conviviales pour les lecteurs.
Êtes-vous intéressé à concaténer correctement une colonne d’heures de début avec une colonne d’heures de fin dans Google Sheets comme dans la colonne C ci-dessus ? Alors lisez la suite pour obtenir la formule, étape par étape.
Suppression des chaînes supplémentaires après la concaténation de l’heure de début avec l’heure de fin dans Google Sheets
D’abord, nous allons concaténer correctement l’heure de début dans la cellule A2 avec l’heure de fin dans la cellule B2. Ensuite, nous pouvons appliquer la formule à l’ensemble des colonnes A et B.
Il y a quatre étapes impliquées. Dans ces quatre étapes, un total de 5 formules sont nécessaires pour combiner l’heure de début et l’heure de fin de manière personnalisée dans Google Sheets.
1. Supprimer les minutes de l’heure si les minutes sont à zéro
La formule TEXT suivante dans Google Sheets formate conditionnellement l’heure dans la cellule A2 au format 12 heures :
=text(A2,si(minute(A2)=0,"HH am/pm","HH:MM am/pm"))
La formule supprime les minutes si elles sont à 00, sinon elle conserve les minutes. La fonction SI le fait. Ensuite, la fonction TEXT formate l’heure de début au format 12 heures.
Nous pouvons appliquer la même chose à l’heure de fin dans la cellule B2. La formule dans la cellule F2 est donc :
=text(B2,si(minute(B2)=0,"HH am/pm","HH:MM am/pm"))
2. Concaténer l’heure de début au format texte avec l’heure de fin
À cette étape, dans la cellule G2, nous combinons simplement/l’horaire de début partiellement formatée ci-dessus avec l’heure de fin.
=E2&" - "&F2
3. Extraction de ‘am’ et ‘pm’ en utilisant les expressions régulières pour personnalisation
=regexreplace(G2,"[0-9:]+","")
La formule REGEXREPLACE ci-dessus dans la cellule H2 supprime tous les chiffres (heures, minutes et secondes) d’une heure de 12 heures et renvoie la période (am/pm).
C’est une étape importante. Savez-vous pourquoi c’est important ? Vous obtiendrez la réponse à l’étape suivante.
4. Le rôle de la logique SI dans la concaténation correcte de l’heure de début avec l’heure de fin dans Google Sheets
Maintenant, nous avons quatre formules dans les cellules E2, F2, G2 et H2, n’est-ce pas ? Faites glisser chaque formule jusqu’à la ligne 7. Je veux dire de E2 à E7, de F2 à F7, etc.
Dans la cellule I2, j’ai la formule suivante :
=si(H2=" am - am",regexreplace(G2,"am.",""),G2)
La fonction SI vérifie si la valeur dans la cellule H2 est « am – am ». Si le test renvoie VRAI, la formule renverra l’heure combinée de la cellule G2 après avoir supprimé le premier ‘am’ et le caractère espace qui suit. La fonction REGEXREPLACE fait cela.
Mais dans la cellule I2, la formule renverra la valeur de la cellule G2 telle quelle, car le test SI renvoie FAUX ici.
Il suffit de faire glisser la formule I2 vers I7. Ensuite, regardez la valeur des cellules H4 et I4.
Ici, le test I4 renverra VRAI. Donc la sortie ne sera pas la valeur G4, mais la valeur G4 après avoir supprimé le premier ‘am’ et l’espace qui l’accompagne.
De cette manière, nous pouvons concaténer correctement une heure de début avec une heure de fin dans Google Sheets.
Comment puis-je combiner les formules ci-dessus en une formule de tableau ?
Nous avons seulement besoin des formules dans la plage E2:I2. Donc, d’abord, supprimez toutes les formules de la plage E3:I7. Ensuite, suivez les étapes ci-dessous.
Étapes :
Dans la formule de la cellule G2, remplacez les références de cellules E2 et F2 par les formules correspondantes de la cellule E2 et de la cellule F2.
La formule G2 maintenant :
=text(A2,si(minute(A2)=0,"HH am/pm","HH:MM am/pm"))&" - "&text(B2,si(minute(B2)=0,"HH am/pm","HH:MM am/pm"))
Maintenant, vous pouvez rendre les cellules E2 et F2 vides.
Allez à la cellule H2. Remplacez la référence G2 dans cette formule par la formule de G2 elle-même.
La formule H2 maintenant :
=REGEXREPLACE(TEXT(A2,SI(MINUTE(A2)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2,SI(MINUTE(B2)=0,"HH am/pm","HH:MM am/pm")),"[0-9:]+","")
Allez à la cellule I2.
Remplacez la référence à la cellule H2 par la formule de la cellule H2 et remplacez également la référence à la cellule G2 (apparaît deux fois) par la formule de la cellule G2.
=si(REGEXREPLACE(TEXT(A2,SI(MINUTE(A2)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2,SI(MINUTE(B2)=0,"HH am/pm","HH:MM am/pm")),"[0-9:]+","")=" am - am",REGEXREPLACE(TEXT(A2,SI(MINUTE(A2)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2,SI(MINUTE(B2)=0,"HH am/pm","HH:MM am/pm")),"am.",""),TEXT(A2,SI(MINUTE(A2)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2,SI(MINUTE(B2)=0,"HH am/pm","HH:MM am/pm")))
Maintenant, rendez les cellules G2 et H2 vides.
Dans la formule ci-dessus, remplacez A2 par A2:A7 et B2 par B2:B7. Ensuite, entourez la formule entière avec la fonction ArrayFormula et voilà !
=ArrayFormula(si(REGEXREPLACE(TEXT(A2:A7,SI(MINUTE(A2:A7)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2:B7,SI(MINUTE(B2:B7)=0,"HH am/pm","HH:MM am/pm")),"[0-9:]+","")=" am - am",REGEXREPLACE(TEXT(A2:A7,SI(MINUTE(A2:A7)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2:B7,SI(MINUTE(B2:B7)=0,"HH am/pm","HH:MM am/pm")),"am.",""),TEXT(A2:A7,SI(MINUTE(A2:A7)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2:B7,SI(MINUTE(B2:B7)=0,"HH am/pm","HH:MM am/pm"))))
Pour la colonne A2:A et B2:B en entier, c’est-à-dire en incluant des plages ouvertes de colonnes, vous devez remplacer A2:A7 par A2:A et B2:B7 par B2:B.
Aussi, pour limiter l’expansion aux lignes non vides, incluez if(A2:A= » », au tout début de la formule ArrayFormula.
=ArrayFormula(si(A2:A="",(si(REGEXREPLACE(TEXT(A2:A,SI(MINUTE(A2:A)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2:B,SI(MINUTE(B2:B)=0,"HH am/pm","HH:MM am/pm")),"[0-9:]+","")=" am - am",REGEXREPLACE(TEXT(A2:A,SI(MINUTE(A2:A)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2:B,SI(MINUTE(B2:B)=0,"HH am/pm","HH:MM am/pm")),"am.",""),TEXT(A2:A,SI(MINUTE(A2:A)=0,"HH am/pm","HH:MM am/pm"))&" - "&TEXT(B2:B,SI(MINUTE(B2:B)=0,"HH am/pm","HH:MM am/pm")))))))
Ressources connexes (rares)
- Comment convertir l’heure militaire dans Google Sheets
- Jours écoulés et temps entre deux dates dans Google Sheets
- Créer un compte à rebours en utilisant des fonctions intégrées dans Google Sheets
- Comment ajouter des heures, des minutes et des secondes à une heure dans Google Sheets
- Comment formater l’heure au format milliseconde dans Google Sheets
- Convertir un horodatage Unix en date et heure locale et vice versa dans Google Sheets
- Comment convertir un horodatage en millisecondes dans Google Sheets
