Help adjusting elaborate DATEDIF formula

Lil Stinker

Board Regular
Joined
Feb 16, 2022
Messages
50
Office Version
  1. 2019
Platform
  1. 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.

=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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,770
Office Version
  1. 2016
I wonder if it is exactly the same issue, from your last thread that I had put an end.
Choose month in E1:
Book1
ABCDE
1DescriptionQTYUnit RateMonths
2Pickup DateWiFi Router22001m,2w,1d
301/03/2022MiFi Hotspot11501m,2w,1d
4Billing StartWalkie Talkie50201m,2w,1d
501/03/2022 
6Billing End 
716/04/2022 
8Return Date 
901/04/2022 
Sheet3
Cell Formulas
RangeFormula
E2:E4E2=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:E9E5=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
ABCDE
1DescriptionQTYUnit RateWeeks
2Pickup DateWiFi Router22006w,4d
301/03/2022MiFi Hotspot11506w,4d
4Billing StartWalkie Talkie50206w,4d
501/03/2022 
6Billing End 
716/04/2022 
8Return Date 
901/04/2022 
Sheet3
Cell Formulas
RangeFormula
E2:E4E2=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:E9E5=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?
 
Solution

Lil Stinker

Board Regular
Joined
Feb 16, 2022
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,175,733
Messages
5,899,161
Members
434,750
Latest member
XLPandit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top