Greetings:

Here's a problem that I've yet to figure out.

I'm trying to determine the number of months, weeks and days between
two dates to determine what the cost of a rented item will be, when the
vendor offers a discount for monthly rates over weekly rates over daily
rates. The standard pricing is always with the assumption that a month
is thirty (30) days, a week is seven (7) days, and a day is, well a
day...regardless of the actual calendar months they cover.

The desire is to have the answer divided in to three fields. For
instance, a rental for 39 days would ideally return the answer "1
month, 1 week, two days", in unique cells specific to each piece of
data.

Can anyone help with this? Thanks!

Rip

Check out this link...

http://www.cpearson.com/excel/datedif.htm
--
HTH...

Jim Thomlinson


"(E-Mail Removed)" wrote:

> Greetings:
>
> Here's a problem that I've yet to figure out.
>
> I'm trying to determine the number of months, weeks and days between
> two dates to determine what the cost of a rented item will be, when the
> vendor offers a discount for monthly rates over weekly rates over daily
> rates. The standard pricing is always with the assumption that a month
> is thirty (30) days, a week is seven (7) days, and a day is, well a
> day...regardless of the actual calendar months they cover.
>
> The desire is to have the answer divided in to three fields. For
> instance, a rental for 39 days would ideally return the answer "1
> month, 1 week, two days", in unique cells specific to each piece of
> data.
>
> Can anyone help with this? Thanks!
>
> Rip
>
>

On 21 Sep 2006 13:04:15 -0700, (E-Mail Removed) wrote:

>Greetings:
>
>Here's a problem that I've yet to figure out.
>
>I'm trying to determine the number of months, weeks and days between
>two dates to determine what the cost of a rented item will be, when the
>vendor offers a discount for monthly rates over weekly rates over daily
>rates. The standard pricing is always with the assumption that a month
>is thirty (30) days, a week is seven (7) days, and a day is, well a
>day...regardless of the actual calendar months they cover.
>
>The desire is to have the answer divided in to three fields. For
>instance, a rental for 39 days would ideally return the answer "1
>month, 1 week, two days", in unique cells specific to each piece of
>data.
>
>Can anyone help with this? Thanks!
>
>Rip

Method 1 (returns the values in separate columns):

A1: Rental Term
B1: Months
C1: Weeks
D1: Days

A2: <enter the number of days of the rental term>
B2: =INT(Rental_Term/30)
C2: =INT(MOD(Rental_Term,30)/7)
D2: =MOD(MOD(Rental_Term,30),7)

You can custom format the cells to have the unit of time displayed as you
describe above, if you want.
--ron

Jim, Ron. Thanks for your thoughtful and quick responses. Ron, your
solution works beautifully. I've been trying to get this right
forever!

Thanks again. Good guys, both of you.

~Rip

On 21 Sep 2006 14:39:31 -0700, (E-Mail Removed) wrote:

>Jim, Ron. Thanks for your thoughtful and quick responses. Ron, your
>solution works beautifully. I've been trying to get this right
>forever!
>
>Thanks again. Good guys, both of you.
>
>~Rip

Glad to help. Thanks for the feedback.


--ron