Help with Month, Week, Daily formula

[Lil Stinker]

I am using this crazy long formula to display a period of time between two dates on our invoices. It currently displays #m, #w, #d (number of months, number of weeks, number of days). The issue I have with what it displays, is if the time period comes out to one month and a few days, it displays 1m,0w,3d. Or a month long period it displays, 1m,0w,0d.

What I would like is for it to display 1m,3d if the calculation comes out to zero weeks. Or simply 1m, if it is just one month, no weeks and no days. I only know enough about Excel to be dangerous. Not enough to wrap my brain around custom formulas.

=IF(ISBLANK(C3),"", 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)))))

Cell E1 is a drop down box with the list "[ Term ], Months, Weeks" so depending on your selection, a different result will display.
Cell C3 refers to a quantity column.
Cell A5 = the start date
Cell A7 = the end date

Can anyone suggest a modification to the formula that can achieve the type of result I'm looking for?

 

[jtakw]

Hi,

Can you show a few sample data with expected results, please?

 

[Peter_SSs]

I have not tried to analyse your formula to see if there is a better way, but you could try this

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&your_existing_formula,",0d",""),",0w","")," 0m,",""))

 

[Lil Stinker]

Hi,

Can you show a few sample data with expected results, please?

Hello-
Basically, whenever the formula results in zero, I don't want the zero value to display in the cell. So whatever the date range is, if it equals 1m, 3w, 2d (1 month, 3 weeks and 2 days), that's great. However, if the date range comes out to 0m, 3w, 2d (3 weeks and 2 days), I only want it to display 3w, 2d. If it comes out to just one month, I would like it to display 1m, rather than 1m, 0w, 0d.

Here are a couple examples:

ORDER FORM -formula tests- 2022.xlsx
	A	B	C	D	E	F
1		Description	QTY	Unit Rate	Months	Item Subtotal
2	Pickup Date	WiFi Router	2	200.00	1m,0w,0d	$400.00
3	02/18/22	MiFi Hotspot	1	150.00	1m,0w,0d	$150.00
4	Billing Start	Walkie Talkie	50	20.00	1m,0w,0d	$1,000.00
5	02/22/22
6	Billing End	would like it to display-->			1m
7	03/22/22
8	Return Date
9	03/28/22
10
11					Subtotal	$1,550.00
12					Discount	$0.00
13					Net Amount	$1,550.00
14					TOTAL AMOUNT	$1,550.00
Sheet1

Cell Formulas
Range	Formula
E7:E10,E2:E5	E2	=IF(ISBLANK(C2),"", 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)))))
F2:F10	F2	=IF(ISBLANK(C2),"",(D2*H7+K7*I7+L7*J7)*C2)
F11	F11	=SUBTOTAL(109,Table2[Item Subtotal])
F13	F13	=(F11-(F11*F12))
F14	F14	=F13

ORDER FORM -formula tests- 2022.xlsx
	A	B	C	D	E	F
1		Description	QTY	Unit Rate	Months	Item Subtotal
2	Pickup Date	WiFi Router	2	200.00	0m,4w,2d	$428.56
3	03/01/22	MiFi Hotspot	1	150.00	0m,4w,2d	$160.72
4	Billing Start	Walkie Talkie	50	20.00	0m,4w,2d	$1,071.00
5	03/01/22
6	Billing End	would like it to display-->			4w,2d
7	03/31/22
8	Return Date
9	04/01/22
10
11					Subtotal	$1,660.28
12					Discount	$0.00
13					Net Amount	$1,660.28
14					TOTAL AMOUNT	$1,660.28
Sheet1

Cell Formulas
Range	Formula
E7:E10,E2:E5	E2	=IF(ISBLANK(C2),"", 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)))))
F2:F10	F2	=IF(ISBLANK(C2),"",(D2*H7+K7*I7+L7*J7)*C2)
F11	F11	=SUBTOTAL(109,Table2[Item Subtotal])
F13	F13	=(F11-(F11*F12))
F14	F14	=F13

 

[Lil Stinker]

I have not tried to analyse your formula to see if there is a better way, but you could try this

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&your_existing_formula,",0d",""),",0w","")," 0m,",""))

Hello Peter-
Thank you for the response! When I try to plug in your suggestion, I get the error 'you've entered too many arguments for this function'.
Would it be possible to type out the full formula the way you intend so I can see where I'm making the mistake?

Many thanks!

 

[Peter_SSs]

Would it be possible to type out the full formula the way you intend so I can see where I'm making the mistake?

For your E2 formula it would be
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&IF(ISBLANK(C2),"", 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,",""))

Result:

22 02 23.xlsm
	A	B	C	D	E
1		Description	QTY	Unit Rate	Months
2	Pickup Date	WiFi Router	2	200	4w,2d
3	44621	MiFi Hotspot	1	150	4w,2d
4	Billing Start	Walkie Talkie	50	20	4w,2d
5	01-Mar-22
6	Billing End
7	31-Mar-22
8	Return Date
9	01-Apr-22
mwd

Cell Formulas
Range	Formula
E2:E9	E2	=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&IF(ISBLANK(C2),"", 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,",""))

 

[Lil Stinker]

Brilliant! Works perfectly! Thank you for the clarification. I wasn't wrapping the formula along with the brackets.

 

[Peter_SSs]

You're welcome.

 

[bebo021999]

@Peter, I afraid your formula in #6 may get trouble with end date in next year:
31-Dec-22 To 01-Mar-23: => "2m, -1w,5d"
Also, if E1 = "Weeks", => 8.57 (should be "8w,4d") although OP expect it to be week count in fraction, but I suggest it to be "#w,#d"

I would refer my solution:

=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",""))

 

[Peter_SSs]

@Peter, I afraid your formula in #6 may get trouble with end date in next year:

It is not my formula, I only added the part to stop zero values showing.

I have not tried to analyse your formula ...., but you could try this

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&your_existing_formula,",0d",""),",0w","")," 0m,",""))