Need help crerating Formula with IF function -

See attached update file In cell F3 I added an if function. I need multiple IF functions to create a series of true. For instance, in this example, if I change the date and the days on rent increase to 6, that would result in a false, so I need to add another "IF". 6 daily rates would result in $3000. the weekly rate is $1820 so if a customer had the equipment for more than 4 days but less than 8, we would just bill $1820 which is less than $3000 (6 $500 daily rates).

Try this formula copied down

=IF(INT(B3/30)>=1,E3*DATEDIF(A3,TODAY(),"m"), IF(INT(B3/7)>=1, MIN(INT(B3/7)*D3,E3), MIN(B3*C3,D3)))

Try for 9 days rental .....

=IF(INT(B3/30)>=1,E3*DATEDIF(A3,TODAY(),"m"), IF(INT(B3/7)>=1, MIN(INT(B3/7)*D3+MOD(B3,7)*C3,E3), MIN(B3*C3,D3)))

You will need similar logic for other calculations to that highlighted: examples below.

=IF(INT($B3/30)>=1,$E3*INT($B3/30)+INT(MOD($B3,30)/7)*$D3+MOD(MOD($B3,30),7)*$C3,IF(INT($B3/7)>=1,(INT($B3/7)*$D3+MOD($B3,7)*$C$3),MIN(B3*C3,D3)))

Given month=30 days (?) and using formula above

Put start date as 9 October giving 41 days rental: 1 month @ 5150 + 1 week @ 1820 + 4 days @ 500 = $8970

Put start date as 08 October giving 42 days rental: 1 month @ 5150 + 1 week @ 1820 + 5 days @ 500 (30+7 +5 ) = $9470

or

is it 1 month (30 days) + 2 weeks ( as 1 week hire < 5 days hire) = $8790

Should month be defined as 28 days??

Not simple !!!

yes, NOT simple. LOL I was along the same track you are going but kept missing something. Your formulas have given me some new ideas though - maybe going month 28 days would be easier, I may need another column to round the end result to the nearest increment. i.e. if the actual rental is greater than the month rate, then assume the lower month rate. the the base formula still doesn't work with every date scenario however. I need to tweak that still.. I added updated sheet. I added the back in date and that cleaned up the formula variable some. see attached

this one is working for my original scenario
=IF(INT(C3/30)>=1,F3*DATEDIF(A3,TODAY(),"m"), IF(INT(C3/7)>=1, MIN(INT(C3/7)*E3+MOD(C3,7)*D3,F3), MIN(C3*D3,E3)))

I also want to look at another option
in this option, assuming the same rental business
calculate rate as day rate is $500, week rate is $1820, month rate is $5150 but prorate the days based on week and month milestones. i.e. a week would be 5 days and a month would be 22 days. so $1820/5 and $5150/22. one week and 2 days would be: $1820+($364*2)=$2548. This would also assume that every month we would bill $5150. If the unit went out on 11-01-05, we would bill $5150 on 12-01-15, and again on 01-01-16, and so on. I don't necessarily have to get all of the info from one cell formula but even across multiple cell formulas if need be.