Formule pour calculer heures et minutes

%3CLINGO-SUB%20id%3D%22lingo-sub-2391970%22%20slang%3D%22fr-FR%22%3EFormula%20for%20calculating%20hours%20and%20minutes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391970%22%20slang%3D%22fr-FR%22%3E%3CP%3EGood%20morning%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20try%20to%20add%20up%20hours%20and%20minutes%20but%20when%20there%20are%20fractions%20ex%3A%2012h15%20minutes%20and%2015.5%20hours%2C%20it%20gives%20me%2027.65.I%20would%20like%20my%20total%20figure%20to%20be%20in%20actual%20hours.%3C%2FP%3E%3CP%3ECan%20you%20help%20me%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDiana%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2391970%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2392031%22%20slang%3D%22en-US%22%3ERe%3A%20Formule%20pour%20calculer%20heures%20et%20minutes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2392031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064875%22%20target%3D%22_blank%22%3E%40Diane820%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20best%20solution%20is%20to%20enter%20the%20values%20as%20real%20times%3A%2012%3A15%20and%2015%3A50.%3C%2FP%3E%0A%3CP%3EApply%20the%20custom%20format%20%5Bh%5D%3Amm%20to%20the%20cell(s)%20with%20the%20sum%20formula.%20The%20addition%20will%20return%20the%20correct%20value.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOtherwise%2C%20you'll%20have%20to%20use%20a%20formula%20such%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DENT(A1)%2BENT(A2)%2BQUOTIENT(100*(MOD(A1%3B1)%2BMOD(A2%3B1))%3B60)%2BMOD(100*(MOD(A1%3B1)%2BMOD(A2%3B1))%3B60)%2F100%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%20add%2012.15%20in%20A1%20and%2015.5%20in%20A2.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Bonjour,

 

J'essaie d'additionner des heures et des minutes mais lorsqu'il y a des fractions ex: 12h15 minutes et 15.5heures, ça me donne 27.65.  J'aimerais que mon chiffre de total soit en heures minutes réelles.

Pouvez-vous m'aider s.v.p.

 

Diane

1 Reply

@Diane820 

The best solution is to enter the values as real times: 12:15 and 15:50.

Apply the custom format [h]:mm to the cell(s) with the sum formula. The addition will return the correct value.

 

Otherwise, you'll have to use a formula such as

 

=ENT(A1)+ENT(A2)+QUOTIENT(100*(MOD(A1;1)+MOD(A2;1));60)+MOD(100*(MOD(A1;1)+MOD(A2;1));60)/100

 

to add 12.15 in A1 and 15.5 in A2.