Am in a bit of a strait. Looking for a non-VBA excel formula to work with rental rates in our workbook. I am hoping there is a simpler Excel method to use other than nested IF statements. The rental rates for equipment is billed by the day, week and month and breaks out as follows: 1 - 2 days is by the day. 3 - 10 consecutive days is by the week and >= 11 consecutive days is by the month. The complication comes to play when either a weekly rate or monthly rate is in effect and there are either partial days or partial weeks which goes similar to this:
6 - 7 consecutive days rental will evaluate as: 1 week plus 1 or 2 days.
11 - 12 consecutive days rental will evaluate as: 2 weeks plus 1 or 2 days.
>= 11 and <= 20 consecutive days will evaluate as: 1 month
After the monthly charge is in effect, there are typically charges amounting to 1 - 2 months plus 1 or 2 additional days. To complicate it further, there are also monthly charges with an extra 1 or 2 weeks coupled with 1 - 2 days.
I experimented with methods using the INT(), MOD() & ROUND() functions to arrive at a basis to determine when weekly or monthly charges would begin but unfortunately, it had to include a myriad of if/else if statements.
Is there a better more concise method?
6 - 7 consecutive days rental will evaluate as: 1 week plus 1 or 2 days.
11 - 12 consecutive days rental will evaluate as: 2 weeks plus 1 or 2 days.
>= 11 and <= 20 consecutive days will evaluate as: 1 month
After the monthly charge is in effect, there are typically charges amounting to 1 - 2 months plus 1 or 2 additional days. To complicate it further, there are also monthly charges with an extra 1 or 2 weeks coupled with 1 - 2 days.
I experimented with methods using the INT(), MOD() & ROUND() functions to arrive at a basis to determine when weekly or monthly charges would begin but unfortunately, it had to include a myriad of if/else if statements.
Is there a better more concise method?