Date calculation

[Toni]

Hi everyone

I want to find a solution about the calculation of dates. Where I work is applied a "special price" based on the years workers have been working for this company. Let's say, if they have 1 year they get 5% plus, two years 10%, three years 15% after which it stops. That's the most they can get (15%).

So I Have setup a field like this:

Get ( current date ) - datestarted

Where the datestarted is a field which contains the date they have start working. If I want the result to be a number, it gives the right difference of days that have been passed, but if I want the result to be a date, it gives it to me the wrong difference. Let's take this example (the format of the dates are in European dd/mm/yyyy):

01/06/2004 - 28/02/2000 = 1555 (if the result is a number)

01/06/2004 - 28/02/2000 = 04/04/0005 (if the result is a date)

Now I want to the result to be a date because than I can get the year from this and calculate the "special price" mentioned above, but I can't. The difference got to be 04/03/004, it's obvious that have passed 4 days, three months and four years since the started day.

Any idea?

[-Queue-]

This is just an age calculation. So why not use something like

Min(Year(Get(CurrentDate)) - Year(Date) - (Get(CurrentDate) < Date( Month(Date); Day(Date); Year(Get(CurrentDate)) )), 3 ) * .05

This determines the 'age', then takes the smaller of either age or 3, and multiplies by .05 to result in .05, .1, or .15.

Are you really concerned how many days and months are involved? It sounds like all you really need is years.

[Toni]

Thanks Queue, it worked, but I don't get the formula, this part:

Year(Get(CurrentDate)) - Year(Date) - (Get(CurrentDate)

The first and second function will return a number, the third a date. I just don't see how to subtrac a date from number.

And yes, i want only the year difference. I thought subtracting two date fields resulting in date would give me the difference in years, month and days, after which I could get the year number.

[-Queue-]

No, subtracting the dates will only give you the number of days difference. Converting that result to a date would give you X number of days after year zero, which you may be able to extract some interesting data from, but wouldn't be the best way to go about it.

The third expression in the calculation is actually

(Get(CurrentDate) < Date( Month(Date); Day(Date); Year(Get(CurrentDate)) ))

which is a test to determine if the current date would be less than the original date, if it were in the current year. The test will return 1 if it is true and zero if it is not. So the calculation will subtract 1 if the test is true. For example, if the dates are 14/07/2004 and 13/07/2002, then the years differ by 2, but the current date is less than the original month and day within the current year (13/07/2004), so it is really only one year. Tomorrow the same record would return 2 years.

This calculation should take leap years into account also.

I should note that it must be set to not store calculation results, so that it will update automatically each day.

[Toni]

I see, great calculation.

Thanks again