Career database: years, months and days between promotions

[central11]

Dear colleagues, I tried to do this by myself and I am kind of ashamed that I have failed so far. Well, I am building a database for my research. It is a group of workers inside an organization. The organization has several levels in terms of jobs and usually you enter in the bottom and (if you are lucky) you reach the top of the pyramid.

Each worker has a record and in it I created the fields for the date he reached a new level in the organization. My purpose is to enter the dates in the fields and have a field showing how many years, months and date it took from going from one to another.

I am using here two fields Level1 and Level2 (both dates in format day/month/year).

GetAsText ( Year ( Get ( Level2 ) ) - Year ( Level1 ) - If ( Get ( Level2 ) < Date ( Month ( Level1 ) ; Day ( Level1 ) ; Year ( Get ( Level2 ) ) ) ; 1 ; 0 ) ) & " Years, " & GetAsText ( Mod ( Month ( Get ( Level2 ) ) - Month ( Level1 ) + 12 - If ( Day ( Get ( Level2 ) ) < Day ( Level1 ) ; 1 ; 0 ) ; 12 ) ) & " Months, " & GetAsText ( Day ( Get ( Level2 ) ) - Day ( Level1 ) + If ( Day ( Get ( Level2 ) ) ≥ Day ( Level1 ); 0 ; If ( Day ( Get ( Level2 ) - Day ( Get ( Level2 ) ) ) < Day ( Level1 ) ; Day ( Level1 ) ; Day ( Get ( Level2 ) - Day ( Get ( Level2 ) ) ) ) ) ) & " Days "

Everytime I enter the information FM show the message: "This parameter is an invalid Get function parameter. What I am doing wrong?

[Raybaudi]

Get ( Level2 ) is the invalid Get( ) function.

Try to use only Level2, but I think that you'll have to re-visit that formula.

[Fitch]

This might be what you want:

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

[Vaughan]

My purpose is to enter the dates in the fields and have a field showing how many years, months and date it took from going from one to another.

The other problem you'll encounter is that the concept of "years, months and days" is flawed because a month is not a fixed period of time. Just don't even go there.

Instead, to make things MUCH simpler, just display the number of years as a decimal (e.g., 2.12) or the whole number of days (e.g., 774)

[doughemi]

I have used basically the OP's formula successfully for quite some time. It will work if (s)he changes all the Get(Level2) to just Level2. I agree with Vaughan up to a point; it shouldn't be used for calculations or other further operations, but it will do nicely for display purposes only.