Calculate number of years and months between two dates?

[filemakerlearner]

Hello,

I'm trying to calculate the number of years and months between today's date, and a date provided ("MyDate").

Here's what I've got so far:

Get(CurrentDate) - MyDate

This returns me the number of days between the two dates. But when I try to do Year() of this statement, it doesn't get me the correct number of years (it's one too many). Hmm.

What am I missing?

[LaRetta]

You need to describe what you want as a result. Do you want years and months? If it is two days short of a year, what should be result?

Without clear business rules, it will be impossible to resolve. What is a month? Is it 30 days? Is Jan 31, 2004 to Feb 28, 2004 one month? Or one month and one day (because of leap year)? So do you round up to a full year if short or round down? As you've seen, you can end up with an extra year in your result without a boolean test as to whether the month/day has passed or not.

So we need further definition.

[LaRetta]

Try this . I would suggest reading the entire thread to help pinpoint your business rules as well.

Edited April 11, 2009 by Guest
Added sentence

[comment]

See if this helps:

http://fmforums.com/forum/showpost.php?post/322417/

Hint: once you know the number of elapsed months, you can divide them by 12 to get the elapsed years. The remainder of the division will be the "and x months" part.

[comment]

LOL - again. But at least the gap is getting shorter.

[LaRetta]

It'll get shorter still - I'm heading for bed!

BTW, I started to get tangled in the 'what is a month' issue and what about remaining days? Round up or down or ?

[filemakerlearner]

Thanks, this is really helpful! I'm working on it now.

This is raising another question I have. I'm new to filemaker, but I'm a C programmer, so I guess I come at it with that mentality.

I'm wondering, since I'm trying to define a field as a calculation doing this, to what extent can I put a series of statements in one of those field calculation things? Like, I'd really like to say something like:

Set YearsWorked = xyz

if (x) then {

YearsWorked = YearsWorked - 1

}

It seems like somehow I have to put the whole series of logic into one line in filemaker!

Similarly, in the code that you pointed me to, they have this line:

years = Year ( today ) - Year ( Start_Date ) - ( today < Date ( Month ( Start_Date ) ; Day ( Start_Date ) ; Year ( today ) ) )

I'm a little confused what that logic structure is with the "<" -- it sort of looks like an if statement, but there's no "if". Does Filemaker just default to "if"?

Thanks so much for the tips.

[comment]

It seems like somehow I have to put the whole series of logic into one line

Not at all: First, any white space in a formula is ignored, so you can break up your formula any way you like. Second, you can use the Let() function, in the form of:

Let ( [

a = 2 ;

b = 3

] ;

a + b

)

or:

Let (

YearsWorked =

;

If ( ; YearsWorked ; YearsWorked - 1 )

)

or even:

Let ( [

YearsWorked = ;

...

YearsWorked = If ( ; YearsWorked ; YearsWorked - 1 )

...

and so on.

what that logic structure is with the "<"

A comparison evaluates to a Boolean (0 or 1). "a = b" is a concise way to say "If ( a = b ; 1 ; 0)". Actually, the latter is a redundancy, because the expression "a = b" evaluates first.

[sparker]

Hello, can someone please help me! I am stuck trying to figure out how to calculate the length of time my employees have been with our company. The problem I am having with the calculation I am using now is that it does not look at the termination date for employees that have been terminated. This calculation below only works for employees that are still with the company and have never left. Now what happens if an employee leaves and then comes back? How do I track employment length for employee’s record? Should the length of employment change using the new rehire date? Or should I have two fields, one being the original hire date and the other being the new rehire date? Some of them have left and came back so the hire date and termination date has changed a few times. I want to calculate length from the new hire date to the termination date if one exists. The code that I am using now is below. Need to add termination date in there somewhere. Please help me if you can. TIA

Shareen

[email protected]

Code:

GetAsText(Year(Get ( CurrentDate )) - Year(Original Hire Date) - If(GetAsNumber(Get ( CurrentDate ))< Date(Month(Original Hire Date); Day(Original Hire Date); Year(Get ( CurrentDate ))); 1; 0)) & " Y, " & GetAsText(Mod(Month(Get ( CurrentDate )) - Month(Original Hire Date) + 12 - If(Day(Get ( CurrentDate )) < Day(Original Hire Date); 1; 0); 12)) &" "&"M"

[comment]

Strictly speaking, if an employee is re-hired, they should get a new employee record. Unless the fact that they have been employed in the past is significant in some way - then you would need a related table of EmploymentPeriods.

[sparker]

Thank you for the reply but how does this help me calculate the length an employee has been with the company based on the date of hire and the termination date and not the current date. How do I change this code to add the termination date into the calculation? I only want to know the length based on the hire date and the termination date. So that would be from the Hire Date to the Termination Date. Seems like this little bit of code needs to be changed throughout the code. (Get ( CurrentDate ) and somehow changed to the Termination Date.

Current Code Used:

GetAsText(Year(Get ( CurrentDate )) - Year(Orginal Hire Date) - If(GetAsNumber(Get ( CurrentDate ))< Date(Month(Orginal Hire Date); Day(Orginal Hire Date); Year(Get ( CurrentDate ))); 1; 0)) & " Y, " & GetAsText(Mod(Month(Get ( CurrentDate )) - Month(Orginal Hire Date) + 12 - If(Day(Get ( CurrentDate )) < Day(Orginal Hire Date); 1; 0); 12)) &" "&"M"

Thanks in advance for any help provided.

Shareen

[email protected]

[comment]

I am a bit confused. If you have ONE HireDate and ONE TerminationDate, and you only want the know the length of the period between these two, then of course you don't want to use Get ( CurrentDate ) in the calculation, if the person is no longer employed.

I don't like your formula too much, so try something like:

Let ( [

end = Case ( TerminationDate ; TerminationDate ; Get ( CurrentDate ) ) ;

m = 12 * ( Year ( end ) - Year ( HireDate ) ) + Month ( end ) - Month ( HireDate )

- ( Day ( end ) < Day ( HireDate ) ) 

] ;

Div ( m ; 12 ) & " Y, " & Mod ( m ; 12 ) & " M" 

)

This calculates the period between HireDate and EITHER today (if there is no TerminationDate) OR TerminationDate (if TerminationDate has a value).

I am still not sure what you want to do when a person is re-hired.

[sparker]

Hi there, can someone please help me with this calculation? It is working just fine but I would like it to show the number of years, months, and days not just the year and month. Need to add something to make it show the days too. TIA

Let ( [

end = Case ( Termination Date Mask ; Termination Date Mask ; Get ( CurrentDate ) ) ;

m = 12 * ( Year ( end ) - Year ( Rehire Date Mask ) ) + Month ( end ) - Month ( Rehire Date Mask )

- ( Day ( end ) < Day ( Rehire Date Mask ) )

] ;

Div ( m ; 12 ) & " Y, " & Mod ( m ; 12 ) & " M"

)

[comment]

I suggest you read these:

http://fmforums.com/forum/showtopic.php?tid/166684/post/166888/#166888

http://fmforums.com/forum/showtopic.php?tid/171817/post/184618/#184618

http://fmforums.com/forum/showpost.php?post/191303/

http://fmforums.com/forum/showtopic.php?tid/175883/post/201792/#201792

http://www.fmforums.com/forum/showpost.php?post/213906/

http://fmforums.com/forum/showtopic.php?tid/183910/post/238121/#238121

http://fmforums.com/forum/showpost.php?post/244877/

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