Lil Stinker
Board Regular
- Joined
- Feb 16, 2022
- Messages
- 142
- 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$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?