This one stumped me for a while today. I was testing a new formula and needed to group a series of dates into 2-week periods ending on a user selected Date. If the user selects Wednesday 8/16/06 there would be periods ending on 8/2, 8/16, 8/30 and so on. All transactions before and after the selected date would be grouped into 2-week periods ending on the appropriate Wednesday.

The method sounds simple.
1) Count the number of days between the transaction and the target date
2) Divide that number by 14 (days)
3) Round down to the next integer.

The result would be periods numbered -3, -2, -1, 0, 1, 2, 3, etc. Each of these periods being 14 days long with the negative periods being in the past and the positive periods in the future. Period zero should end on the user’s target date.

But, I ran into a problem. I assumed that I could use the Truncate() function to round the result of my division down to the next integer. However I found that if you have negative numbers truncate rounds UP. In other words truncate converts both 0.9 and -0.9 into zero. Truncate always goes toward zero which would give me one period of 28 days.

This gave me an idea. There is a new function in CRvXI called RoundUp that I have never used. I could round up and subtract 1. When I read the help I learned that “UP” is defined as AWAY from zero. So -0.9 would round “UP” to -1.0. So neither of these functions could be used to solve my problem.

Fortunately I knew another method for rounding up that always goes in the same direction. If you ever need to round to the next higher number, and both positive and negative numbers need to go in the same direction, use the following:

Round ( {YourValue} + .499999 )

This will always round up to the next truly higher integer, regardless of the sign of the starting value. Oh, and if you ever need to group in custom 14 day periods, let me know. I have the answer for that one, too.