Lil Stinker
Board Regular
 Joined
 Feb 16, 2022
 Messages
 50
 Office Version

 2019
 Platform

 Windows
I have this formula that displays two different results in a cell based on the selection chosen in a data validation drop down list.
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?
=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$7EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7)&"w,"&MOD($A$7EDATE($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?