Help with formula to calculate rentals.

[XL Pro]

I have a worksheet that reports the total number of days a piece of equipment has been on a site for a month.

The breakdown works like this:
4 rental days = 1 week
12 rental days = 1 month

So, if a piece of equipment is rented for 6 days, we want to calculate it as 1 week (5 days) and 1 day, not at 1 week (4 days, per our criteria) and 2 days.

Same thing would apply for 11 days: 2 weeks + 1 day vs 2 weeks (@ 4 day-wk + 3 days).

When the days are 12 or more, the equipment is automatically charged for 1 month.

I can't quite seem to get the formula calculated in my head to figure out what to do.

Thanks for any help or ideas on how to tackle this.

 

[sulakvea]

smth like this?

=int(A1/5) & "weeks" & mod(A1,5) & "days"

 

[XL Pro]

You know, I was trying to work with the MOD() function, but wasn't quite getting it right. I think I was making it harder than it was. Your solution is simple enough. Thanks!

 

[XL Pro]

Oops.. spoke too soon, 8 & 9 day rentals should be calculated as 2 Weeks. So I came up with this:
=IF(J44>11,"1 MO",IF(AND(J44>=8,J44<=10),"2 WK",IF(J44<5,J44 & " day(s)",INT(J44/5)&" WK "&IF(MOD(J44,5)=0,"",MOD(J44,5)&" DY"))))

10 days is correctly calculated in your formula, but I used it as the criteria for 2 wks anyway.

The main thing was getting me to a good starting point.

 

[XL Pro]

Geez, I made it too difficult again. If anyone is following along here, or if anyone cares, the original formula that sulakvea provided works as I needed.

It also made it easy to actually calculate the rental rate.