Counting between dates to also include weeks Hi all This formula works great and

Question

Counting between dates to also include weeks

Hi all

This formula works great and shows the days, months and years is it possible to include weeks in this formula?

in A4 is the Due date and in B4 is the date completed:

=IF(DATEDIF(A4,B4,"y")=0,"",IF(DATEDIF(A4,B4,"y")=1,
DATEDIF(A4,B4,"y")&"year",DATEDIF(A4,B4,"y")&"years"))
&IF(AND(DATEDIF(A4,B4,"y")<>0,DATEDIF(A4,B4,"ym")<>0),", ","")
&IF(DATEDIF(A4,B4,"ym")=0,"",IF(DATEDIF(A4,B4,"ym")=1,
DATEDIF(A4,B4,"ym")&" month",DATEDIF(A4,B4,"ym")&" months"))
&IF(AND(OR(DATEDIF(A4,B4,"y")<>0,DATEDIF(A4,B4,"ym")<>0),
DATEDIF(A4,B4,"md")<>0),", ","")&IF(DATEDIF(A4,B4,"md")=0,"",
IF(DATEDIF(A4,B4,"md")=1,DATEDIF(A4,B4,"md")&" day",
DATEDIF(A4,B4,"md")&" days"))

Thanks guys

Explanation / Answer

Try it like this. Note I haven't trapped for zero as in your formula.

=DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"y")&" Years, "&DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"ym")&" Months, "&INT(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"md")/7)&" Weeks, "&MOD(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"md"),7)&" Days "

Here it is trapped for zero years, months, weeks or days

=IF(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"y")=0,"",DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"y")&" Years ")&IF(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"ym")=0,"",DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"ym")&" Months ")&IF(INT(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"md")/7)=0,"",INT(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"md")/7)&" Weeks ")&IF(MOD(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"md"),7)=0,"",MOD(DATEDIF(A4,B4 -(MOD(A4,1)>MOD(B4,1)),"md"),7)&" Days ")

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects