Help adjusting elaborate DATEDIF formula

[Lil Stinker]

I have this formula that displays two different results in a cell based on the selection chosen in a data validation drop down list.

=IF(E$1="Weeks",ROUNDDOWN((DATEDIF($A$5,$A$7,"d")/7),2),IF(OR($B2="",AND(E$1<>"Months")),"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$1="Months",DATEDIF($A$5,$A$7,"m")&"m,"&INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7)&"w,"&MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7)&"d",INT(($A$7-$A$5)/7)&"w,"&MOD($A$7-$A$5,7)&"d"),"0m,",""),"0w,",""),",0d","")))

Currently, if “Weeks” is chosen, it displays the duration of time as a decimal figure. If “Months” is chosen, it displays the duration of time as “1m,2w,3d” for one month, two weeks and three days. If it comes to an even duration of time, it will display “1m,3d” or “1m,2w”, etc.

How can I go about changing the formula to have the “Weeks” result display in the same manner that the “Months” result displays in rather than the decimal figure without affecting the accuracy of the date difference calculation?

 

[bebo021999]

I wonder if it is exactly the same issue, from your last thread that I had put an end.
Choose month in E1:

Book1
	A	B	C	D	E
1		Description	QTY	Unit Rate	Months
2	Pickup Date	WiFi Router	2	200	1m,2w,1d
3	01/03/2022	MiFi Hotspot	1	150	1m,2w,1d
4	Billing Start	Walkie Talkie	50	20	1m,2w,1d
5	01/03/2022
6	Billing End
7	16/04/2022
8	Return Date
9	01/04/2022
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=IF(OR($B2="",AND(E$1<>"Months",E$1<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$1="Months",DATEDIF($A$5,$A$7,"m")&"m,"&INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7)&"w,"&MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7)&"d", INT(($A$7-$A$5)/7)&"w,"&MOD($A$7-$A$5,7)&"d"),"0m,",""),"0w,",""),",0d",""))
E5:E9	E5	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&IF(ISBLANK(C5),"", IF($E$1="[ Term ]","",IF($E$1="Months",(DATEDIF($A$5,$A$7 -(MOD($A$5,1)>MOD($A$7,1)),"ym")&"m,"&INT(DATEDIF($A$5,$A$7 -(MOD($A$5,1)>MOD($A$7,1)),"md")/7)&"w,"&MOD(DATEDIF($A$5,$A$7 -(MOD($A$5,1)>MOD($A$7,1)),"md"),7)&"d"),IF($E$1="Weeks",ROUNDDOWN((DATEDIF($A$5,$A$7,"d")/7),2))))),",0d",""),",0w","")," 0m,",""))

And weeks:

Book1
	A	B	C	D	E
1		Description	QTY	Unit Rate	Weeks
2	Pickup Date	WiFi Router	2	200	6w,4d
3	01/03/2022	MiFi Hotspot	1	150	6w,4d
4	Billing Start	Walkie Talkie	50	20	6w,4d
5	01/03/2022
6	Billing End
7	16/04/2022
8	Return Date
9	01/04/2022
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=IF(OR($B2="",AND(E$1<>"Months",E$1<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$1="Months",DATEDIF($A$5,$A$7,"m")&"m,"&INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7)&"w,"&MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7)&"d", INT(($A$7-$A$5)/7)&"w,"&MOD($A$7-$A$5,7)&"d"),"0m,",""),"0w,",""),",0d",""))
E5:E9	E5	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&IF(ISBLANK(C5),"", IF($E$1="[ Term ]","",IF($E$1="Months",(DATEDIF($A$5,$A$7 -(MOD($A$5,1)>MOD($A$7,1)),"ym")&"m,"&INT(DATEDIF($A$5,$A$7 -(MOD($A$5,1)>MOD($A$7,1)),"md")/7)&"w,"&MOD(DATEDIF($A$5,$A$7 -(MOD($A$5,1)>MOD($A$7,1)),"md"),7)&"d"),IF($E$1="Weeks",ROUNDDOWN((DATEDIF($A$5,$A$7,"d")/7),2))))),",0d",""),",0w","")," 0m,",""))

Does it meet your new requirement?

 

[Lil Stinker]

I wonder if it is exactly the same issue, from your last thread that I had put an end.
...

Does it meet your new requirement?

You are correct! Apologies! I had forgotten you had posed the solution to this issue while addressing the original formula's DATEDIF inconsistency. I had only focused on fixing that DATEDIF inconsistency and removing the "0 weeks" and "0 days" and had not realized your suggested formula at that time converted weeks from fractions to "#w,#d". My reasoning at the time was because I was actually using the decimal figure as part of the formula calculation in the subtotal field but now I've found a workaround to that.

Thanks again!