="Du "&TEXTE(DATE(ANNEE(AUJOURDHUI());1;-2)-JOURSEM(DATE(ANNEE(AUJOURDHUI());1;3))+A3*7;"jjjj jj mmmm aaaa")&" au "&TEXTE(DATE(ANNEE(AUJOURDHUI());1;-2)-JOURSEM(DATE(ANNEE(AUJOURDHUI());1;3))+(A3*7)+6;"jjjj jj mmmm aaaa")
Sub Macro1()
Range("A2").FormulaR1C1 = "=YEAR(TODAY())"
Range("A3") = "40"
ActiveWorkbook.Names.Add Name:="pLundi", RefersToR1C1:= _
"=MAX(DATE(Feuil1!R2C1,1,1),DATE(Feuil1!R2C1,1,1)-WEEKDAY(DATE(Feuil1!R2C1,1,1),2)+(Feuil1!R3C1-1)*7+1)"
Range("B3").FormulaR1C1 = _
"=""Du ""&TEXT(pLundi,""jjjj jj mmmm aaaa"")&"" au ""&TEXT(pLundi+6,""jjjj jj mmmm aaaaa"")"
End Sub