Formule Excel pour calculer la durée du temps en fonction d'une chaîne de texte de 9 h à 12 h 30

J'essaie de développer une formule Excel qui calcule la durée réelle d'une chaîne d'un intervalle de temps du format suivant: 1:00am – 3:00am

Selon cet article, il semble qu'il soit aussi simple que de soustraire les deux fois. Par exemple, pour obtenir les heures, les minutes et les secondes entre deux fois (4:55:00), l'article dit de faire ce qui suit:

 =TEXT(B2-A2,"h:mm:ss") 

Voici à quel point j'ai eu:

 =TEXT(RIGHT(B2,SEARCH(" – ",B2))-LEFT(B2,SEARCH(" – ",B2)),"h:mm:ss") 

Je me demande si le problème pourrait être que ma chaîne de texte n'a pas de composant «date» en plus de l'heure. Est-ce la seule solution pour pouvoir effectuer l'opération de soustraction pour "fudge" une date dans les cordes? Ou y a-t-il une autre approche alternative, autre que simplement décomposer individuellement les composantes heure et minute et calculer la durée de cette façon? J'aimerais également compenser tout éventuel délai de temps de pause, tel que 9:00 pm – 12:00 am si possible.

5 Solutions collect form web for “Formule Excel pour calculer la durée du temps en fonction d'une chaîne de texte de 9 h à 12 h 30”

Voici une des solutions compliquées. Il est probablement préférable de coller ceci et d'espérer le meilleur plutôt que d'essayer de s'asseoir et de l'analyser.

 =INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)) & ":" & TEXT((MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)-INT(MOD(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60+IF(MID(B2,FIND("m",B2)-1,1)=MID(B2,FIND("m",B2,FIND("-",B2))-1,1),IF(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)+(MID(B2,FIND(":",B2,FIND("-",B2))+1,2) - MID(B2,FIND(":",B2)+1,2))/60<0,24-IF(LEFT(B2,2)="12",12,0),0),12-IF(AND(MID(B2,FIND(":",B2,FIND("-",B2))-2,2) - LEFT(B2,FIND(":",B2)-1)<>0,MID(B2,FIND(":",B2,FIND("-",B2))-2,2)="12"),12,0)),24)))*60,"00") 

Edit: Correction de la formule pour la dernière fois.

Commentaire d'adressage:

Pour ajouter ces résultats, pour ajouter une gamme de ces sorties, vous pouvez utiliser la formule suivante:

 =TEXT(SUM(VALUE(B2:B3)),"h:mm:ss") 

Vous devez entrer ceci comme une formule de tableau. Pour ce faire, appuyez sur Ctrl + Maj + Entrée lorsque vous entrez la formule.

Si vous pouvez mettre un espace avant le "am" ou "pm", cela fonctionnera:

 =TEXT(RIGHT(B2,LEN(B2)-SEARCH(" - ",B2)-2) - LEFT(B2,SEARCH(" - ",B2)-1),"h:mm:ss") 

Voici un élément de base qui ne gérera pas d'espace avant le matin / heure, mais ce n'est pas robuste ou complet, car il ne gère pas beaucoup de cas de bord, il suffit de l'exposer à différentes façons de faire. Nous devrions connaître tous les types possibles de chaînes que vous analyserez pour proposer une solution vraiment complète:

 =TEXT((MID(B2,SEARCH(" - ",B2)+3, LEN(B2)-SEARCH(" - ",B2)-4) & IF(ISERROR(SEARCH("am",RIGHT(B2,8))), " pm", " am")) - (LEFT(B2,SEARCH(" - ", B2)-3) & IF(ISERROR(SEARCH("am",LEFT(B2,8)))," pm"," am")), "h:mm:ss") 

Vous avez besoin de l'espace avant "am" ou "pm", comme l'a dit Lance Roberts.

Vous pouvez utiliser cette formule:

 =SUBSTITUTE(SUBSTITUTE(B2; "am"; " am"); "pm"; " pm") 

Ensuite, essayez ceci:

 =TEXT(TIMEVALUE(RIGHT(B2;SEARCH(" - ";B2;1)))-TIMEVALUE(LEFT(B2;SEARCH(" - ";B2;1)));"h:mm:ss") 

Essayez cette formule:

 =A2+(A1>A2)-A1 

A1 contient votre temps "De"
A2 contient votre temps "To"

Vous pouvez ensuite utiliser la fonction TEXT pour la formater dans une chaîne,
Ou utilisez le format de numéro [h]:mm:ss .

Il devrait fonctionner même avec des délais qui dépassent minuit (c.-à-d 9:34 PM à 3:45 AM)


Tout d'abord, comme l'a mentionné Lance, vous devez ajouter un espace entre l'heure et les notations AM / PM dans votre chaîne de temps.

La façon la plus simple de le faire est la fonctionnalité Find and Replace de Excel. Appuyez sur Ctrl + H , tapez "am" dans Find what , puis tapez "am" (avec un espace unique avant) dans Replace with . Faites de même avec "pm".

Edit: [supprimé SUBSTITUTs imbriqués] Nesting SUBSTITUTES serait une mauvaise idée après tout. Au moins non sans addition de TRIM.


Voici mon autre approche de débordement.

Créez un nom ("tt") en utilisant la formule ci-dessous (où $ A1: $ A10 est la gamme qui contient vos chaînes temporelles.

 =TRIM(SUBSTITUTE(SUBSTITUTE($A1:$A10,"a"," a"),"p"," p")) 

Ensuite, utilisez cette formule pour obtenir la durée (en fonction de la première formule ci-dessus):

 =RIGHT(tt,LEN(tt)-FIND("-",tt,1)-1)+ (LEFT(tt,FIND("-",tt,1)-2)>RIGHT(tt,LEN(tt)-FIND("-",tt,1)-1))- LEFT(tt,FIND("-",tt,1)-2) 

Le grand problème est que le temps nécessite un espace compris entre les minutes et le matin / matin: de 1:00 am - 3:00 am ou de 9:00 pm - 12:00 am .

Avec cela, vous pouvez utiliser la formule:

 =TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))+ IF(TIMEVALUE(RIGHT(A1,SEARCH(" - ",A1)))-TIMEVALUE(LEFT(A1,SEARCH(" - ",A1)))<0,1,0) 

Et formatez la cellule sous forme de mise en forme personnalisée h:mm:ss .

  • Afficher les informations du filtre de colonne Excel dans les cellules
  • Utilisation de MAX () dans Excel contre une liste de dates en utilisant plusieurs critères
  • Calculer une durée dans Excel à partir de la date et de l'heure
  • Dupliquer les lignes en fonction de ses données
  • Excel compte cellules colorées
  • Où est la plus longue chaîne
  • Enregistrement d'un fichier séparé par tabulation dans Excel sans guillemets
  • Transposer la table de style matriciel à 3 colonnes dans Excel
  • Dans Excel, un modèle par défaut personnalisé sera-t-il appliqué aux ** tous les fichiers ** (ou seulement aux nouveaux, vierges)?
  • Comment tracer les valeurs positives et négatives des deux côtés de l'axe dans Excel?
  • Formule Excel pour extraire des devis
  • Soyons le génie de l'ordinateur et du réseau.