I need to calculate the number of days/weeks/months/years a unit is rented. I am using the calculation attached.

Here's the issue. We have a 2 week minimum rental, so, anything less than 2 weeks should show as 2 weeks. No problem. But, from a statistical point of view we'd like to track what units are renting and for what period of time. If I have a unit rented from Oct. 1 - Nov 2 the calc shows 2 months. How can I show it as 1 Month/1 Day or 1 year 3 months, etc. without having to have a calculation the length of my arm?

Edited November 12, 200817 yr by Guest

I'd look first at some existing custom functions, e.g.

http://www.briandunning.com/cf/536

http://www.briandunning.com/cf/518

http://www.briandunning.com/cf/57

Think about what "1 month" really means. It could be anything from 28 days to 31 days.

For comparison purposes I'd suggest displaying the rental period in days or weeks, or maybe weeks and days.

Days and weeks are constant, whereas months and years change actual duration.

See also:

http://fmforums.com/forum/showtopic.php?tid/189394/

Fitch:

I tried the first CF. The problem is twofold. It doesn't take in leap years and if the rental period is from Nov.1 - Nov. 30 it displays 4 weeks 2 days. You must have: Start= Nov 1 & end = Dec.1 to show a month, but in rental terms that would be 1 Month 1 day.

Vaughn:

I agree Weeks and Days would be easier, but, the client, a condo on the beach, currently has a 2 week rental minimum and there are those who are pushing for either a 1 month or 3 month minimum. So statistically they want to see what percentage of units are renting for 2 weeks, 3 weeks, 1 month, 3 months, and greater than 3 months. I felt having the calculation give us those types of returns would be easier to develop a statistical report for them.

I wonder, can we round a date? Would that help?

TIA

Al

There's no problem with doing the calculation in weeks or in months. It's mixing the two that creates absurd results.

Michael:

I'm sorry, how does mixing the 2 become absurd? (Ah, you mean based on the results I'm getting.) From a statistical point of view?

The reason I set my calc to go from 3 weeks directly to 1 month is so we just get past the whole 4 weeks 2 days scenario. But, if we go from Sept. 12 - Oct. 13 we now have 1 month and 1 day and my calc shows 2 months. That's statistically incorrect for the clients purposes. Is it possible to round a date?

Say:

32 - 39 days = 1 month

>= 40 days = 2 months.

And what we be a reasonable place to round it?

Edited November 12, 200817 yr by Guest

Rent period starts on 31 March, ends in one month. What day should that be?

Well, in landlord/tenant leases they would end the lease April 30 and charge for 1 month and 1 day. But, for the clients needs it would be April 29. However, from a statistical view I could just as easily round the former to 1 month and it would not corrupt the data for the client.

I am not sure what is "statistically correct". You can set any rules you like for this, as long as they serve your purpose. For example, you could say that 4 weeks should count as a month. But then don't bother "checking" the result against actual dates, because it won't make any sense.

I guess for the client's purposes:

2 weeks

3 weeks

1 month

>1 month and < 3months

3 Months

> 3 months and < 6 Months

Anything more is just nice to know, but, will not affect their decision process.

That's quite a jump from the thread title (and your original request) of days! :smile2:

Yes, you're right. I use a CF to calculate the days in another field. What I meant was correcting the problem I was having with it. Which, I believe, we can all agree I am no closer to doing.

So, I thank you all for your comments and suggestions, but, I guess, for my purposes, I will need a calculation as long, or perhaps longer, than my arm. B)

Well, that should be simple enough: just set an arbitrary number of days for any category, and do a Case() like you started to do.

BTW, you only need the lower limit of any category, for example:

Case (

Period ≥ 90 ; "3 months or more" ;

Period ≥ 30 ; "1 - 3 months" ;

...

)

The calculation (in this case) needs to be as long as the list of your categories.

Thanks, I'll work on that in the AM. At my age it is now time for beddy-by with my friend Jack Daniels. He'll help me see things with greater clarity. B)