FM 6 Windows

Hello All,

Would someone be good enough to help me with a date calc? I need to
determine in months the length of employment.
Date Fields: Start Date, Terminate Date.

If still employed the Terminate Date field will be empty so the Length of
Service field would change each month. The Length of Service field (type -
number?) should display the number of months.

Thanks very much,
Greg Shifrin

In article <466861a0$0$4720$4c368faf (AT) roadrunner (DOT) com>, "GregS"
<news (AT) notpeaktradingcom (DOT) invalid> wrote:

First you need to know how many years are between the years of the two
dates, which you can then multiply by 12 to get the number of full year
months.
ie.
12 * (Year(EndDate) - Year(StartDate))

From that you can add the number of extra months past the last full
year or subtract the number of months yet to make the last full year.
ie.
+ Month(EndDate) - Month(StartDate)

Similarly, you also then have to subtract 1 if end day is before the
start day (ie. the final month isn't a full month).
ie.
- If (Day(Endate) < Day(StartDate), 1, 0)

Putting this altogether gives a Calculation of:

MonthsBetween Calculation, Number Result
= 12 * (Year(EndDate) - Year(StartDate))
+ (Month(EndDate) - Month(StartDate))
- If (Day(Endate) < Day(StartDate), 1, 0)


The If statement at the end can be replaced by just the Boolean logic
test since such tests result in either 1 for True or 0 for False.
ie.
MonthsBetween Calculation, Number Result
= 12 * (Year(EndDate) - Year(StartDate))
+ (Month(EndDate) - Month(StartDate))
- (Day(Endate) < Day(StartDate))


This Calculation appears to work for all the test dates I have tried,
but there may be a problem somewhere in it so you should make sure it's
giving the correct results with your real data.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Thanks Harry for your help.

One glitch.

As I said, if the person is still employed the End Date (Terminate Date)
will be empty and must be accommodated. Your calc works perfectly if End
Date has an entry but if empty it returns a negative number which seems to
relate to total months but I can't see quite how.

Thanks,
Greg Shifrin

"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote

In article <46697342$0$9957$4c368faf (AT) roadrunner (DOT) com>, "GregS"
<news (AT) notpeaktradingcom (DOT) invalid> wrote:

Sorry, I got interrupted yesterday. I meant to add that the calculation
only give the whole number of months, not things like "1.25 months".


If the EndDate is empty, then the Calculation becomes 0 - StartDate,
which is really just nonsense. There's two ways around this.


If you want the Calculation to stay empty if EndDate is empty, then you
can put the original Calculation inside an If statement (or Case if you
prefer).
eg.
MonthsBetween Calculation, Number Result
= If (IsEmpty(EndDate),
"",
12 * (Year(EndDate) - Year(StartDate))
+ (Month(EndDate) - Month(StartDate))
- If (Day(Endate) < Day(StartDate), 1, 0)
)



If on the other hand you want to have the Calculation return the number
of months up to "today" when EndDate is empty (and keep updating itself
as dates continue), then there's a simple solution.

First create another Calculation field with a Date result that stores
either a copy of the EndDate, or if EndDate is empty uses today's date
instead.
eg.
EndDate_Temp Calculation, Date Result, Unstored
= If (IsEmpty(EndDate),
Status(CurrentDate),
EndDate
)


Then use this field in place of "EndDate" in the original Calculation.
ie.
MonthsBetween Calculation, Number Result
= 12 * (Year(EndDate_Temp) - Year(StartDate))
+ (Month(EndDate_Temp) - Month(StartDate))
- If (Day(Endate_Temp) < Day(StartDate), 1, 0)

The problem here is that using Status(CurrentDate) can cause the
database to slow down if there are LOTS of records or you're using a
slow computer / network.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

This works perfectly Harry, thanks so much.

"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote

Works perfectly Harry, thanks so much.


"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote

Works perfectly Harry, thanks again.


"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote