Classer les dates de l'année en deux catégories

=SI(MOIS(A1)<=6;"01/07/2012"*1;"01/07/2013"*1)

Sub Semestre()
Range("D2") = DateSerial(Year(Range("A2")) + 40, Int((Month(Range("A2")) + 5) / 6) * 6 + 1, 1)
End Sub

Sub ListeInverses()
'Sur un exemple de Jacques Boisgontier
Set d = CreateObject("Scripting.Dictionary")
For Each c In Feuil2.Range("A2:A" & Feuil2.Range("A65536").End(xlUp).Row)
  d(DateSerial(Year(c.Value) + 40, Int((Month(c.Value) + 5) / 6) * 6 + 1, 1)) = d(DateSerial(Year(c.Value) + 40, Int((Month(c.Value) + 5) / 6) * 6 + 1, 1)) & c.Offset(, 1) & ";"
  'd(c.Value) = d(c.Value) & c.Offset(, 1) & " "
Next c
b = d.keys
For i = LBound(b) To UBound(b)
  Cells(i + 2, "D") = b(i)
  a = Split(d.Item(b(i)), ";")
  Cells(i + 2, "D").Offset(, 1).Resize(, UBound(a) + 1) = Application.Transpose(Application.Transpose(a))
Next i
Range("D1") = "Remise des médailles après 40 années de service"
Range("E1") = "Personnel"
Feuil2.Range("D2:D" & Feuil2.Range("D65536").End(xlUp).Row).NumberFormat = "mmmm yyyy"
End Sub