I'm seeking help with a calculation that would round the duration between 2 dates as follows:

 

Fields:

Rental_Start_Date

Rental_End_Date 

Rental_Duration

 

Rules:

Rental_Duration will calculate how many weeks between Rental_Start and Rental_End date

Rental_Duration can show partial week (e.g. 1.25 weeks, 1.50 weeks)

Rental Duration will be rounded to 2 decimal places

Rental_Duration fractional value must be either .00, .25, .50, or .75

 

Examples:

2 weeks, 1 day (i.e. 15 days) would be calculated as 2.00 weeks

2 weeks, 2 days would be calculated as 2.25 weeks

2 weeks, 3 days would be calculated as 2.50 weeks

2 weeks, 4 days would be calculated as 2.50 weeks

2 weeks, 5 days would be calculated as 2.75 weeks 

2 weeks, 6 days would be calculated as 3.00 weeks

3 weeks, 1 day would be calculated as 3.00 weeks

3 weeks, 2 days would be calculated as 3.25 weeks

so on and so forth...

 

 

1. Are both start date and end date included in the duration?

 

2. Why would 15 days be calculated as 2.00 weeks? 15 days are 2.1428571428571429 weeks. This is closer to 2.25 than to 2 (the mid-point being 2.125). Similarly, 2 weeks, 6 days (i.e. 20 days) are 2.8571428571428571 weeks - this would be properly rounded to 2.75 rather than 3.

 

Assuming that the answer to #1 is yes and that the two examples are mistaken, try =

Let  ( [
d = EndDate - StartDate  + 1 ;
w = d / 7
] ;
Round ( 4 * w ; 0 ) / 4
)

The result type is Number. If you want it to display trailing zeros, format the field as Decimal.

Thanks for the quick answer, Comment.

 

 

1. Are both start date and end date included in the duration?

 

Yes

 

 

 

 

2. Why would 15 days be calculated as 2.00 weeks?

 

Because it's an industry norm to round it this way. Is there a calc that can accomodate this?

Well, since this norm doesn't seem to follow any logical pattern (that I can see), you'll just need to be explicit:

Let  ( [
d = EndDate - StartDate  + 1 ;
w = Div ( d ; 7 ) ;
r = Mod ( d ; 7 ) ;
fr = Choose ( r ; 0 ; 0 ; .25 ; .5 ; .5 ; .75 ; 1 )
] ;
w + fr
)

Thanks again, Comment. I'll give that a try.