=ENT(A1)&" h "&(MOD(A1;1)*100)&" mn"
Pas sans passer par VBA, mais peut-être une alternative avec le correcteur orthographique :est il possible de garder le format 3.3?
=SUBSTITUE(TEXTE(SI(ESTNUM(-SUBSTITUE(A2;".";","));SUBSTITUE(A2;".";",");SUBSTITUE(A2;",";"."));GAUCHE(1/1000;4));STXT(1/10;2;1);":")+SUBSTITUE(TEXTE(SI(ESTNUM(-SUBSTITUE(B2;".";","));SUBSTITUE(B2;".";",");SUBSTITUE(B2;",";"."));GAUCHE(1/1000;4));STXT(1/10;2;1);":")
=--SUBSTITUE(TEXTE(SUBSTITUE(TEXTE(SI(ESTNUM(-SUBSTITUE(A2;".";","));SUBSTITUE(A2;".";",");SUBSTITUE(A2;",";"."));GAUCHE(1/1000;4));STXT(1/10;2;1);":")+SUBSTITUE(TEXTE(SI(ESTNUM(-SUBSTITUE(B2;".";","));SUBSTITUE(B2;".";",");SUBSTITUE(B2;",";"."));GAUCHE(1/1000;4));STXT(1/10;2;1);":");"[h]:mm");":";STXT(1/10;2;1))
=(-1)^(GAUCHE(A2)="-")*SUBSTITUE(TEXTE(ABS(SI(ESTNUM(-SUBSTITUE(A2;".";","));SUBSTITUE(A2;".";",");SUBSTITUE(A2;",";".")));GAUCHE(1/1000;4));STXT(1/10;2;1);":")+(-1)^(GAUCHE(B2)="-")*SUBSTITUE(TEXTE(ABS(SI(ESTNUM(-SUBSTITUE(B2;".";","));SUBSTITUE(B2;".";",");SUBSTITUE(B2;",";".")));GAUCHE(1/1000;4));STXT(1/10;2;1);":")
=--SUBSTITUE(TEXTE((-1)^(GAUCHE(A2)="-")*SUBSTITUE(TEXTE(ABS(SI(ESTNUM(-SUBSTITUE(A2;".";","));SUBSTITUE(A2;".";",");SUBSTITUE(A2;",";".")));GAUCHE(1/1000;4));STXT(1/10;2;1);":")+(-1)^(GAUCHE(B2)="-")*SUBSTITUE(TEXTE(ABS(SI(ESTNUM(-SUBSTITUE(B2;".";","));SUBSTITUE(B2;".";",");SUBSTITUE(B2;",";".")));GAUCHE(1/1000;4));STXT(1/10;2;1);":");"[h]:mm");":";STXT(1/10;2;1))
cela semble trop compliqué.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, h#
Set r = Intersect(Target, Range("A2:B" & Rows.Count), Me.UsedRange)
If r Is Nothing Then Exit Sub
For Each r In r 'si entrées multiples
Cells(r.Row, 3).Resize(, 2) = "" 'RAZ
If Cells(r.Row, 1) & Cells(r.Row, 2) <> "" Then
h = Heure(Cells(r.Row, 1)) + Heure(Cells(r.Row, 2))
Cells(r.Row, 3) = h
Cells(r.Row, 4) = Heure(h, True)
End If
Next
End Sub
Function Heure(v, Optional op As Boolean = False)
Dim sg, p
sg = IIf(Left(CStr(v), 1) = "-", -1, 1)
v = Abs(Val(Replace(CStr(v), ",", ".")))
If op Then
v = sg * (Int(v * 24) + (Round(v * 1440) - 60 * Int(v * 24)) / 100)
Else
v = Format(v, "0.00")
p = InStr(v, Mid(0.1, 2, 1)) 'position du séparateur décimal
v = sg * (Left(v, p - 1) / 24 + Mid(v, p + 1) / 1440)
End If
Heure = v
End Function