Fixed minimum rate for prorating calculation

L

Lil Stinker

Board Regular
Joined
Feb 16, 2022
Messages
151
Office Version
  1. 2019
Platform
  1. Windows
Hello-
I'm looking for a way to fix in a minimum rate when calculating for monthly or weekly rental terms. In the samples below, I have the same table indicating the result for a monthly rental and a weekly rental based on a drop down in column E. The idea is the user will select Months or Weeks based on the term they are choosing for the rental when placing an order. Each term however, has a minimum rental rate regardless of which is chosen. Weekly rentals have a minimum 7-day rental while Monthly has a minimum one month rental. If a weekly rental is selected but the duration is less than 7 days, it should still total whatever weekly rate is listed in the Unit Rate column rather than breaking it down by the day as it currently does. Same for when a monthly rental is selected on the form, if the duration is less than a month, it should still total the monthly rate listed in column D. It is only after the minimum is met, does the rental start to prorate.

The two tables I have off to the right are there for calculating the proration for either rental term. I have it set up this way because this seems to produce the most accurate proration of rental rates for our needs. The problem is, it does not factor in a minimum rental period. So, I don't know if a more detailed IF statement in the Item Subtotal column is the way to go or if different formulas are needed in each of the calculation tables to factor in the minimum rental rate but I do know that I'm not smart enough to figure it out.

Any help would be greatly appreciated.

(By the way, this is meant to be used for drafting quotes to our clients. The start and end dates, equipment and rental rates are not fixed and will vary per quote).

ORDER FORM -formula tests- 2022.xlsx
ABCDEFGHIJKLMNOPQRS
17DescriptionQTYUnit RateMonthsItem Subtotal[ Term ]MonthsWeeksDaysWeekly RateDaily RateSubtotalWeeksDaysDaily RateSubtotal
18Computer Monitor1200.003w,6d$192.84Months0.003.006.0050.007.14192.843.006.0028.57771.42
19Speaker Set250.003w,6d$96.48Weeks0.003.006.0012.501.7996.483.006.007.14385.68
20Start DateKeyboard & Mouse Set120.003w,6d$19.260.003.006.005.000.7119.263.006.002.8677.16
2103/01/22Computer Tower1500.003w,6d$482.160.003.006.00125.0017.86482.163.006.0071.431928.58
22End Date            
2303/28/22            
24            
25            
26            
27Subtotal$790.74  
28Discount$0.00  
29Net Amount$790.74  
30TOTAL AMOUNT$790.74  
31
32DescriptionQTYUnit RateWeeksItem Subtotal[ Term ]MonthsWeeksDaysWeekly RateDaily RateSubtotalWeeksDaysDaily RateSubtotal
33Computer Monitor1200.006d$171.42Months0.000.006.0050.007.1442.840.006.0028.57171.42
34Speaker Set250.006d$85.68Weeks0.000.006.0012.501.7921.480.006.007.1485.68
35Start DateKeyboard & Mouse Set120.006d$17.160.000.006.005.000.714.260.006.002.8617.16
3604/01/22Computer Tower1500.006d$428.580.000.006.00125.0017.86107.160.006.0071.43428.58
37End Date            
3804/07/22            
39            
40            
41            
42Subtotal$702.84  
43Discount$0.00  
44Net Amount$702.84  
45TOTAL AMOUNT$702.84  
Sheet 2
Cell Formulas
RangeFormula
I18:I26I18=IF(ISBLANK(C18),"",DATEDIF($A$21,$A$23,"M"))
J18:J26J18=IF(ISBLANK(C18),"",INT(($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")))/7))
K18:K26K18=IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))
L33:L41,L18:L26L18=IF(ISBLANK(C18),"",(ROUND(D18/4,2)))
M33:M41,M18:M26M18=IF(ISBLANK(C18),"",(ROUND(L18/7,2)))
N33:N41,N18:N26N18=IF(ISBLANK(C18),"",(D18*I18+L18*J18+M18*K18)*C18)
P18:P26P18=IF(ISBLANK(C18),"",INT((DATEDIF($A$21,$A$23,"d")/7)))
Q18:Q26Q18=IF(ISBLANK(C18),"",MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7))
R33:R41,R18:R26R18=IF(ISBLANK(C18),"",(ROUND(D18/7,2)))
S33:S41,S18:S26S18=IF(ISBLANK(C18),"",(D18*P18+R18*Q18)*C18)
E18:E26E18=IF(OR($B18="",AND(E$17<>"Months",E$17<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$17="Months",DATEDIF($A$21,$A$23,"m")&"m,"&INT(($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")))/7)&"w,"&MOD($A$23-EDATE($A$21,DATEDIF($A$21,$A$23,"m")),7)&"d", INT(($A$23-$A$21)/7)&"w,"&MOD($A$23-$A$21,7)&"d"),"0m,",""),"0w,",""),",0d",""))
F18:F26F18=IF(ISBLANK(C18),"",IF(E$17="Months",(D18*I18+L18*J18+M18*K18)*C18,IF(E$17="Weeks",((D18*P18+R18*Q18)*C18))))
F27F27=SUBTOTAL(109,Table2261013[Item Subtotal])
L42:L45,L27:L30L27=IF(ISBLANK(C22),"",(ROUND(K27/7,2)))
M42:M45,M27:M30M27=IF(ISBLANK(C22),"",(D22*H27+K27*I27+L27*J27)*C22)
F29,F44F29=(F27-(F27*F28))
F30,F45F30=F29
I33:I41I33=IF(ISBLANK(C33),"",DATEDIF($A$36,$A$38,"M"))
J33:J41J33=IF(ISBLANK(C33),"",INT(($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")))/7))
K33:K41K33=IF(ISBLANK(C33),"",MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7))
P33:P41P33=IF(ISBLANK(C33),"",INT((DATEDIF($A$36,$A$38,"d")/7)))
Q33:Q41Q33=IF(ISBLANK(C33),"",MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7))
E33:E41E33=IF(OR($B33="",AND(E$32<>"Months",E$32<>"Weeks")),"", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$32="Months",DATEDIF($A$36,$A$38,"m")&"m,"&INT(($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")))/7)&"w,"&MOD($A$38-EDATE($A$36,DATEDIF($A$36,$A$38,"m")),7)&"d", INT(($A$38-$A$36)/7)&"w,"&MOD($A$38-$A$36,7)&"d"),"0m,",""),"0w,",""),",0d",""))
F33:F41F33=IF(ISBLANK(C33),"",IF(E$32="Months",(D33*I33+L33*J33+M33*K33)*C33,IF(E$32="Weeks",((D33*P33+R33*Q33)*C33))))
F42F42=SUBTOTAL(109,Table226101316[Item Subtotal])
Cells with Data Validation
CellAllowCriteria
E17List=$H$1:$H$3
E32List=$H$1:$H$3
 
Sort by date Sort by votes
Oh, now I understand. Yeah, that's not good if it wipes out 10m because of the 0m. I have to credit Peter_SSs for coming up with the SUBSTITUTE option to remove the 0m,0w,0d's.
I'll give your method a try.

Thanks again!
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi Kirk-

I'm going to take a shot in the dark and see if you get this post since you were helping to configure this prorating table (even though it is months later).

Everything is working great as is. Now, I would like to see about changing or adding in different prorating parameters. I've been playing around with it myself and I've come close but can't seem to bring it past the finish line and I'm in a bit of a time crunch.

I'm just going to cast my line first and see if this gets to you before going any further with the post.
Fingers crossed...
 
Upvote 0
I'm available to have a look. You may want to begin a new post to draw more eyes on the problem, but if you do that, reference this thread (e.g., click on the Share icon in the upper left of the initial post or the solution post in this thread and copy the URL, then paste it into your new thread) and mention me (type @ then my username to convert the text to a user link). That way the history is available easily to anyone wishing to review details and I'll get pinged so that I know to look for it. Then describe the details for the desired version 2.0.
 
Upvote 0
Since the first change relies on this specific table and formulas, I’ll reply here first and save the second criteria which may require a different set of formulas for the new post.

What I would like to do here is add an option to the drop down (E17) that would allow for a daily rate. Once the drop down says Daily, it should calculate to have a one month minimum based on a 30-day cycle. If the time period is anywhere less than 30 days, it should display the minimum rental subtotal. If the time period extends beyond a 30-day cycle, it should then be prorated by the day.

For example:
Unit Rate = $6.67
Time period < 30 days = $200.10 minimum
Time period > 30 days = $200.10 + 6.67 x (X) days
Start 9/14/22, End 11/1/22 = $320.16

I haven’t had time to focus on making this adjustment yet but I figure, this is probably easier than the next requirement which I’ll post separately.

I hope this all makes sense.
 
Upvote 0
Lil Stinker said:
Once the drop down says Daily, it should calculate to have a one month minimum based on a 30-day cycle.
Click to expand...
Recall the sheet computes actual months, weeks, and days initially (the "original" section), then applies some additional rules based on your rental conditions (the "conditional" section). Does the quote above mean you want the "Conditional" section Num Months to show "1" or should it show "0" and then the Conditional Num Days would have a minimum of 30...and the cost would be calculated based in the daily rate and the number of days given by MAX(actual days, 30). I believe the latter would be more consistent with other rules (e.g., when "Weeks" are selected, there is no monthly rate and the number of months is set to 0). However, it appears in your most recent post that you want to treat a >30 days rental as being 1 or more months (where a month is defined as 30 days exactly) plus any remaining days (that do not form a whole month) being billed at the daily rate. I don't think it matters either way (the computation will produce the same result, but the displayed rental period will be different, as one would show months and days while the other would show only days).

For the example in your last post, wouldn't the answer be $326.83...weren't we counting the start day and the end day, rather than the intervals between (I think there is some discussion in this thread about keeping the "+1" term)? So from 9/14 to 11/1 we would have 48 intervals or 49 days, which includes the start and end days.
 
Upvote 0
A further comment about my last post...
KRice said:
I don't think it matters either way (the computation will produce the same result, but the displayed rental period will be different...
Click to expand...
It does matter in terms of spreadsheet design. The approach taken for the Subtotal column relies on this construction: SUMPRODUCT(M18:O18,P18:R18)
The "conditional" number of months, weeks, and days in M18:O18 are then used in two places: 1) for computing the total by multiplying by the corresponding monthly, weekly, and daily rates and then summing, and 2) for constructing the Rental Period text string. So the approach that would be most consistent with the worksheet development up to this point would represent a rental period from 9/14 to 11/1 as "49d" and the Subtotal computation, for a daily rate of $6.67/day, would multiply the arrays {0,0,49} days * {0,0,6.67} rates and then sum the resulting array.

In the reworked version below, I've adopted the approach just described, but review carefully to determine if this is what you want. Some explanation:
  1. I've included plain language (mostly) text descriptions of the formulas above the helper table.
  2. The table's first green row holds the revised formulas (or those left unchanged but believed to be acceptable under the new set of rules).
  3. The yellow cells (and their rows) hold the old formulas. I overwrote the column I formulas in I18:I21 to allow for easy comparison of the old vs. new formulas (yellow vs. green) and for switching through the rates (months, weeks, days) to check on Rental Period text string representations.
If the green formulas work okay, then they can be copied down (except for I18, which needs to be fixed using a formula from further below (say I24).
Mrexcel_20220915_Lil Stinker.xlsx
ABCDEFGHIJKLMNOPQRST
14if "months" then floor of date range (start to end) in months else 0 (correct for weeks and days)if "days" then 0 else QUOTIENT(date range from start + NumMonths to end in days, 7) (correct for months and weeks)date range from start + NumMonths to end in days - 7*NumWeeks (correct for months, weeks, and days)if "months" AND NumWeeks=4 then NumMonths+1 else NumMonthsif "months" AND NumWeeks=4 then 0 else NumWeeksif "days" then MAX(30,actual days) else if "months" AND NumWeeks=4 then 0 else NumDaysif "months" then use monthly "unit rate" else "" (for selection of weeks and days)if "months" then base weekly rate on monthly rate/4 else if "weeks" then "unit rate" else "" (applies to selection "days")if "days" then use daily rate directly else base daily rate on weekly rate/7 (applies to selection months and weeks)enforces minimum charge stipulations: if "Months" then min chg of 1m else if "weeks" then min chg of 1w else if "days then min chg of 30d. Min chg is compared to standard subtotal computed with SUM({m,w,d} periods * {m,w,d} rates)
15
16OriginalConditional
17Date RangeDescriptionQtyUnit RateDaysItem SubtotalTerms: DaysNum MonthsNum WeeksNum DaysNum MonthsNum WeeksNum DaysMonthly RateWeekly RateDaily RateSubtotalRental Period
18Computer Monitor16.6749d326.83Days00490049  6.67326.8349d
19Speaker Set16.671m,2w,5d11.21Months1251256.671.670.2411.211m,2w,5d
20Start DateKeyboard & Mouse Set16.677w46.69Weeks070070 6.670.9546.697w
219/14/22Computer Tower16.677w46.69Days070070 6.670.9546.697w
22End DateRed Stapler16.677w46.69Days070070 6.670.9546.697w
2311/1/22Red Stapler16.677w46.69Days070070 6.670.9546.697w
24inclusiveRed Stapler16.677w46.69Days070070 6.670.9546.697w
25              
26              
27Subtotal571.49
2812/26/2022Discount10.0%
29200.148Net Amount514.34
30TOTAL AMOUNT514.34
Lil Stinker
Cell Formulas
RangeFormula
I17I17="Terms: "&$E17
J18:J26J18=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18K18=IF(ISBLANK(C18),"",IF(I18="Days",0,QUOTIENT(DATEDIF(EDATE($A$21,J18),$A$23+1,"D"),7)))
L18:L26L18=IF(ISBLANK(C18),"",DATEDIF(EDATE($A$21,J18),$A$23+1,"D")-7*K18)
M18:M26M18=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),J18+1,J18))
N18:N26N18=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),0,K18))
O18O18=IF(ISBLANK(C18),"",IF(I18="Days",MAX(L18,30),IF(AND(I18="Months",K18=4),0,L18)))
P18:P26P18=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
Q18Q18=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),IF(I18="Weeks",D18,"")))
R18R18=IF(ISBLANK(C18),"",IF(I18="Days",D18,(ROUND(Q18/7,2))))
S18S18=IF(ISBLANK(C18),"",C18*MAX(IF(I18="Months",P18,IF(118="Weeks",Q18,30*R18)),SUMPRODUCT(M18:O18,P18:R18)))
T18:T26T18=IF(ISBLANK(C18),"",IF(M18<>0,M18&IF(AND(N18=0,O18=0),"m","m,"),"")&IF(N18<>0,N18&IF(O18=0,"w","w,"),"")&IF(O18<>0,O18&"d",""))
K19:K26K19=IF(ISBLANK(C19),"",QUOTIENT(DATEDIF(EDATE($A$21,J19),$A$23+1,"D"),7))
O19:O26O19=IF(ISBLANK(C19),"",IF(AND(I19="Months",K19=4),0,L19))
Q19:Q26Q19=IF(ISBLANK(C19),"",IF(I19="Months",ROUND(D19/4,2),D19))
R19:R26R19=IF(ISBLANK(C19),"",(ROUND(Q19/7,2)))
S19:S26S19=IF(ISBLANK(C19),"",C19*MAX(IF(I19="Months",P19,Q19),SUMPRODUCT(M19:O19,P19:R19)))
I22:I26I22=IF(ISBLANK(C22),"",SUBSTITUTE(I$17,"Terms: ",""))
E18:E26E18=T18
F18:F26F18=S18
F27F27=SUBTOTAL(109,F18:F26)
A29A29=6.67*30
B29B29=320.16/6.67
F29F29=F27*(1-F28)
F30F30=F29
Cells with Data Validation
CellAllowCriteria
E17ListMonths,Weeks,Days
 
Upvote 0
Thanks again for this and I appreciate the explanation columns as well. The reworked formulas in Row 18 appear to do the trick. The only initial change that I made was to the Rental Period cell where I've made it refer to the columns J:L rather than M:O. Reason being, when the duration comes to any period less than a month, I don't want it to display 30d but the actual number of days.

Which leads me to my next issue. How can I have column E display the actual duration of time in M, W, D rather than the number of days when "Days" is chosen in E17 without disturbing the conditional calculations in the table? Would that require a whole separate table with the original formulas just to produce that result for the Rental Period? I may want to introduce the option to display the number of days or M, W, D based on the user's decision. Ultimately, it comes down to more discussion with my supers as to what they want it to show. I'm just curious.
 
Upvote 0
Lil Stinker said:
The only initial change that I made was to the Rental Period cell where I've made it refer to the columns J:L rather than M:O. Reason being, when the duration comes to any period less than a month, I don't want it to display 30d but the actual number of days.
Click to expand...
Check this carefully. If I recall, one of the reasons for the three "Conditional" columns was to accommodate the special rounding for 4 weeks (or 4 weeks and a day or two)...to round the months up by one when "Months" is selected. So instead of reporting the Rental Period as 4w,1d (given by the "Original" formulas in J:L), we restructure/round to 1m (given by the "Conditional" formulas in M:O). So switching to J:L might have some negative unintended consequences. I think there might be another approach, as I've included an "if "Days" then MAX(L18,30)" component in the Conditional Num Days formula. Perhaps that is not needed. Instead, we simply return L18 there. Then enforce the minimum charge of 30 days (when "Days" is selected) in the Subtotal formula (it's already in there). See if this "version 4" resolves the issue you've mentioned while keeping the Rental Period formula referenced to M:O...
Mrexcel_20220915_Lil Stinker.xlsx
ABCDEFGHIJKLMNOPQRST
14introduces four options: months, weeks, days, customcarries over user selection for months, weeks, days, customif "months" then floor of date range (start to end) in months else 0 (correct for weeks and days)if "days" then 0 else QUOTIENT(date range from start + NumMonths to end in days, 7) (correct for months and weeks)date range from start + NumMonths to end in days - 7*NumWeeks (correct for months, weeks, and days)if "months" AND NumWeeks=4 then NumMonths+1 else NumMonthsif "months" AND NumWeeks=4 then 0 else NumWeeksif "days" then MAX(30,actual days) else if "months" AND NumWeeks=4 then 0 else NumDaysif "months" then use monthly "unit rate" else "" (for selection of weeks and days)if "months" then base weekly rate on monthly rate/4 else if "weeks" then "unit rate" else "" (applies to selection "days")if "days" then use daily rate directly else base daily rate on weekly rate/7 (applies to selection months and weeks). Also introduced logic check for "custom" to return ""enforces minimum charge stipulations: if "Months" then min chg of 1m else if "weeks" then min chg of 1w else if "days then min chg of 30d. Min chg is compared to standard subtotal computed with SUM({m,w,d} periods * {m,w,d} rates). Also spliced in array-based formula to compute week by week discounts for "weeks" selection.
15
16OriginalConditional
17Date RangeDescriptionQtyUnit RateWeeksItem SubtotalTerms: WeeksNum MonthsNum WeeksNum DaysNum MonthsNum WeeksNum DaysMonthly RateWeekly RateDaily RateSubtotalRental Period
18Red Stapler16.6729d200.10Days00290029  6.67200.1029d
19Speaker Set16.671m6.67Months0411006.671.670.246.671m
20Start DateKeyboard & Mouse Set16.674w,1d27.63Weeks041041 6.670.9527.634w,1d
219/14/22Computer Tower16.674w,1d27.63Days041041 6.670.9527.634w,1d
22End DateRed Stapler16.674w,1d27.63Weeks041041 6.670.9527.634w,1d
2310/12/22Red Stapler16.674w,1d27.63Weeks041041 6.670.9527.634w,1d
24inclusiveRed Stapler16.674w,1d27.63Weeks041041 6.670.9527.634w,1d
25              
26              
27Subtotal344.92
28Discount10.0%
29Net Amount310.43
30TOTAL AMOUNT310.43
Lil Stinker_v4
Cell Formulas
RangeFormula
I17I17="Terms: "&$E17
J18:J26J18=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
K18K18=IF(ISBLANK(C18),"",IF(I18="Days",0,QUOTIENT(DATEDIF(EDATE($A$21,J18),$A$23+1,"D"),7)))
L18:L26L18=IF(ISBLANK(C18),"",DATEDIF(EDATE($A$21,J18),$A$23+1,"D")-7*K18)
M18:M26M18=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),J18+1,J18))
N18:N26N18=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),0,K18))
O18:O26O18=IF(ISBLANK(C18),"",IF(AND(I18="Months",K18=4),0,L18))
P18:P26P18=IF(ISBLANK(C18),"",IF(I18="Months",D18,""))
Q18Q18=IF(ISBLANK(C18),"",IF(I18="Months",ROUND(D18/4,2),IF(I18="Weeks",D18,"")))
R18R18=IF(ISBLANK(C18),"",IF(I18="Custom","",IF(I18="Days",D18,(ROUND(Q18/7,2)))))
S18S18=IF(ISBLANK(C18),"",IF(I18="Custom","",C18*MAX(IF(I18="Months",P18,IF(I18="Weeks",Q18,30*R18)), IF(I18="Weeks",SUM(Q18*(DATEDIF(EDATE($A$21,J18),$A$23+1,"D")>{0,7,14,21})*{1,0.5,0.25,0}), SUMPRODUCT(M18:O18,P18:R18) ))))
T18:T26T18=IF(ISBLANK(C18),"",IF(M18<>0,M18&IF(AND(N18=0,O18=0),"m","m,"),"")&IF(N18<>0,N18&IF(O18=0,"w","w,"),"")&IF(O18<>0,O18&"d",""))
K19:K26K19=IF(ISBLANK(C19),"",QUOTIENT(DATEDIF(EDATE($A$21,J19),$A$23+1,"D"),7))
Q19:Q26Q19=IF(ISBLANK(C19),"",IF(I19="Months",ROUND(D19/4,2),D19))
R19:R26R19=IF(ISBLANK(C19),"",(ROUND(Q19/7,2)))
S19:S26S19=IF(ISBLANK(C19),"",C19*MAX(IF(I19="Months",P19,Q19),SUMPRODUCT(M19:O19,P19:R19)))
I22:I26I22=IF(ISBLANK(C22),"",SUBSTITUTE(I$17,"Terms: ",""))
E18:E26E18=T18
F18:F26F18=S18
F27F27=SUBTOTAL(109,F18:F26)
F29F29=F27*(1-F28)
F30F30=F29
Cells with Data Validation
CellAllowCriteria
E17ListMonths,Weeks,Days,Custom
 
Upvote 0
Good catch! I knew there was a reason for those conditional columns but in all the testing I've been doing over the last few months, each rental period has matched in those 6 columns so I forgot why they were there in the first place. 😅

But I have come up on something interesting regarding the Custom option in this 4th version. When I plugged in an extended period ranging from 7/19/22 to 5/9/23, with Custom selected, it is not displaying the Rental Period as 9m 3w as it is does when Months is chosen. Instead it is only displaying in weeks and days, 42w 1d. Why would it be doing this when there is nothing in columns J:O calling back to the Custom alternative?
 
Upvote 0
Lil Stinker said:
Why would it be doing this when there is nothing in columns J:O calling back to the Custom alternative?
Click to expand...
There is...by negation. The "Original" [Num Months] formula is:
Excel Formula: 
=IF(ISBLANK(C18),"",IF(I18="Months",DATEDIF($A$21,$A$23+1,"M"),0))
...so it specifically looks for "Months" to report back the number of months, and anything else (including "Custom") returns a 0.
What would you like here...perhaps return the number of months if "months" or "custom"?

Edit:
If so, then replace the formula with...
Excel Formula: 
=IF(ISBLANK(C18),"",IF(OR(I18="Months",I18="Custom"),DATEDIF($A$21,$A$23+1,"M"),0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Auto Sort (Descinding) based on a column header using VBA
Replies
1
Views
248
Fluff
Fluff
L
  • Solved
Custom rate prorating / Nested IFs?
Replies
5
Views
672
KRice
K
V
  • Question Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
405
kvsrinivasamurthy
K
V
  • Solved
Progressive average formula that counts also blank cells
2
Replies
17
Views
943
kvsrinivasamurthy
K
L
  • Question Question
Prorating formula based on weeks, months
Replies
5
Views
3K
Lil Stinker
L

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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
Back
Top