subtract a periode with another period(DATEDIF)

[Habiler]

HEllo everbody,

I have this formula :

Please Login or Register  to view this content.

How can I subtract 54 months and 26 days from this period.

In my file :

a28 = a14 - a25

Thnaks for all

HABILER

[AliGW]

To get the date you are looking for:

=EDATE(A1,-54)-26

where A1 contains the base date. Then apply your required formatting.

[Habiler]

Thanks for your answer.

The problem is that the value isn't a date but the result of a formula /

In case :

=SUMPRODUCT(DATEDIF(C10:C20;D10:D20+1;"y")) + INT((SUMPRODUCT(DATEDIF(C10:C20;D10:D20+1;"ym"))+INT(SUMPRODUCT(DATEDIF(C10:C20;D10:D20+1;"md"))/30))/12) & " years "
& MOD(SUMPRODUCT(DATEDIF(C10:C20;D10:D20+1;"ym"))+INT(SUMPRODUCT(DATEDIF(C10:C20;D10:D20+1;"md"))/30);12) & " months "
& MOD(SUMPRODUCT(DATEDIF(C10:C20;D10:D20+1;"md"));30) & " days"

[JeteMc]

Hello Habiler and Welcome to Excel Forum.
Perhaps going about this in a different manner may help.
This proposal employs 4 helper columns (H:K) which may be moved and/or hidden for aesthetic purposes.
Column H is populated using: =(DATEDIF(B4,C4,"d")+1)*D4
Column I is populated using: =ROUNDDOWN(H4/365.2422,0)
Column J is populated using: =ROUNDDOWN((H4-I4*365.2422)/30.44,0)
Column K is populated using: =ROUNDDOWN(H4-I4*365.2422-J4*30.44,0)
The final answer in cell B30 is displayed using: =I30 & " ans " &J30 & " mois " & K30 & " jours"
Let us know if you have any questions.

[Habiler]

Thanks For you answer but I have a problem with the result of Row 7.

It must be a full year.

1 year and 3 month not 29 days.

How can i resolve this ?

Thanks for all

Habiler

[JeteMc]

Let try the following:
For years: =DATEDIF(B4,(C4+1),"y")*D4
For months: =DATEDIF(B4,(C4+1),"ym")*D4
For days: =IF(B4="",0,DATEDIF(B4,(C4+1),"md"))*D4
In row 28 get the sum of each column and then, in cell K28, get a date using: =DATE(H28,I28,J28)
Using AliGW's suggestion get the final date, in cell K30, using: =EDATE(K28,-I29)-J29
Parse the final date using:
In H30: =YEAR(K30)-1900
In I30: =MONTH(K30)
In J30: =DAY(K30)
Let us know if you have any questions.