Day / Week / Month Rate - Formula

[SwampthingX90]

I'm trying to get the formula below to calculate a certain rate based on a certain date range. It's supposed to never exceed the monthly rate, but since I'm using 28 as a monthly divider, 10/3 thru 10/31 would end up at 25 days and use the weekly rate mixed with the daily rate which would exceed the monthly rate. Let me know if I need to expand further.

=IF(K16<>0,((H16*MOD(K16,7)*(MOD(K16,7)<4)+I16*((MOD(K16,7)>3)+INT(MOD(K16,28)/7))+J16*INT(K16/28))*D16),0)

 

[jasonb75]

Is the idea that it should always use the cheapest rate(s) for the number of days shown? For example, using row 18, 5 days would cost more than 1 week so base the cost on 7 days at week rate instead of 5 at day rate? (With the same logic being applied to week and month rates).

What about longer periods (say 35 days, should that be 28 days at month rate and 7 days at week rate, or 35 days at month rate)?

With questions like this it is inevitable that if we go with what we think is the most logical method that it will be the wrong one.

 

[SwampthingX90]

Is the idea that it should always use the cheapest rate(s) for the number of days shown? For example, using row 18, 5 days would cost more than 1 week so base the cost on 7 days at week rate instead of 5 at day rate? (With the same logic being applied to week and month rates).

Yes that's exactly right. I need the cheapest rate to calculate.

What about longer periods (say 35 days, should that be 28 days at month rate and 7 days at week rate, or 35 days at month rate)?

It should be 28 days at month rate and 7 days at week rate. So, if it ran from 9/1 to 10/9 it would be one month and one week

 

[jasonb75]

One other thing that I forgot to ask, which version of excel are you using (if it needs to work with multiple versions then I need to know the oldest one).

I'll start looking at this now but I'm going to be away from the forum for a couple of hours later today and might not get it finished until after I come back.

 

[SwampthingX90]

Version 2022. It does not need to work with multiple versions. Thank you so much!

 

[Fluff]

Version 2022

That is the build number, rather than the version which should be something like 2016,2019 365 etc.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jasonb75]

Version 2022.

Noting Fluff's comments, could you confirm if that is Office 2022 (I've only seen 2021), or if you have picked up on the other version shown at the bottom.

If you go to File, then Account (as you did before), the bit that we need to know is on the side where it says 'Product Information', but closer to the top than where you looked before.
On mine, it says 'Microsoft 365' just below the Microsoft logo, I think that they should all be the same layout, at least for most of the fairly recent versions.

That is the build number, rather than the version

Version appears to be an ambiguous term that causes a lot of confusion, Fluff.

I'm using Office 365 (beta channel, with at least one pending update that I've just noticed).

For me, that is Version 2210, Build 15715.20014

 

[Fluff]

Version appears to be an ambiguous term that causes a lot of confusion, Fluff.

Agreed, what MS now call the "Product" used to be called the "Version" & so it does cause confusion.

 

[jasonb75]

Trying to keep this as simple as possible to start with, try copying the mini sheet below into a blank Excel sheet and test it with some different rates and durations to see if the results are as expected. (if you click the copy icon at the top left corner of the mini sheet, then paste to H5 in Excel it will enter the formulas for you in the correct places).

The table in H5:L9 is set to mimic the same section of your screen capture for testing with the final results shown in the cost column. The other 3 tables from columns N to X are temporary to break the formula down for testing, once we are sure that the results are correct and the correct version of excel has been confirmed I can piece it all together into a single formula.

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
5	Daily	Weekly	Monthly	Days	Cost		Days at day rate	Days at week rate	Days at month rate		Days	Weeks	Months		Day rate	Week rate	Month rate
6	140	700	2450	56	4900		0	0	56		0	0	2		0	0	4900
7	85	425	1360	25	1360		4	21	0		4	3	0		340	1275	0
8	85	425	1360	84	4080		0	0	84		0	0	3		0	0	4080
9	40	160	480	56	960		0	0	56		0	0	2		0	0	960
Sheet11

Cell Formulas
Range	Formula
N6:N9	N6	=K6-P6-O6
O6:O9	O6	=FLOOR(K6-P6,7)
P6:P9	P6	=FLOOR(K6,28)
R6:R9	R6	=N6
S6:S9	S6	=O6/7
T6:T9	T6	=P6/28
V6:W9	V6	=MIN(R6*H6,I6)
X6:X9	X6	=T6*J6
L6:L9	L6	=X6+MIN(V6+W6,J6)

 

[Shesil]

@SwampthingX90
Please check this:-

=IF(K6>=28,INT(K6/28)*J6+INT(MOD(K6,28)/7)*I6+MOD(K6,7)*H6,INT(MOD(K6,28)/7)*I6+MOD(K6,7)*H6)