Month Calculation

[Tom England]

This is probably very easy calculation (I'm a relative newcomer!)but getting quite annoying. I want to know how to add a certain number of calender months onto a date. -->

We run company pension schemes, whereby employees are eligible to join the schemes after say 6 months at the company.

ie. Someone may join a company (DateJoinCo)on 22/6/2001 (UK Date Formatting) and I want the EligibilityDate field in this example to show 22/12/2001. The EligibilityDate must be formatted as a date field not text.

CAN ANYONE HELP PLEASE!!

[proton]

quote:

---

Originally posted by Tom England:

This is probably very easy calculation (I'm a relative newcomer!)but getting quite annoying. I want to know how to add a certain number of calender months onto a date. -->

We run company pension schemes, whereby employees are eligible to join the schemes after say 6 months at the company.

ie. Someone may join a company (DateJoinCo)on 22/6/2001 (UK Date Formatting) and I want the EligibilityDate field in this example to show 22/12/2001. The EligibilityDate must be formatted as a date field not text.

CAN ANYONE HELP PLEASE!!

---

Tom,

I'm sure others may post great solutions for this, but till then I'll just give my two cents. Now you can add values to a date field, but if you add a number to a date field, the number is typically added to the day section and not the month section. Therefore, my simple solution would be to create the calculated field (EligibilityDate) and specify the calculation as:

DateJoinCo + 180

NB: The 180 represents the number of days in 6 months (averaging 30 days per month).

Make sure and specify the calculation result as Date and you should be okay. Hope this helps.

[JamData]

hi, i had same kind of trouble.

here is what I did:

there is a function called month(date) it will return the month number of one date, (let

[Tom England]

Thanks,

I had already got round the problem of the changing years once 6 months had been added on, it was just putting all the date elements back together.

Everything seems easy again!!!

[Vaughan]

EligibilityDate =

Date(Month(DateJoinCo) + 6, Day(DateJoinCo), Year(DateJoinCo))

Note the syntax for Date(month, day, year).

This calc will give exactly what you asked for in the original question. As a company policy, check what the actual eligibility criteria is: 180 days service or six months. If six months, what happens if I join on 31 March? Will I be eligible on 30 September or 1 October? Check what result the calculation will return. This sort of nit-picking is how lawyers make their millions. It might be necessary to re-word the policy to make it clearer and easier to implement.

BTW, date+180 days is probably easier to implement in FMP (and easiest to state in a policy) but the least intuitive and hardest to work out mentally.

[ November 06, 2001: Message edited by: Vaughan ]

[proton]

quote:

---

Originally posted by Vaughan:

EligibilityDate =

Date(Month(DateJoinCo) + 6, Day(DateJoinCo), Year(DateJoinCo))

Note the syntax for Date(month, day, year).

This calc will give exactly what you asked for in the original question. As a company policy, check what the actual eligibility criteria is: 180 days service or six months. If six months, what happens if I join on 31 March? Will I be eligible on 30 September or 1 October? Check what result the calculation will return. This sort of nit-picking is how lawyers make their millions. It might be necessary to re-word the policy to make it clearer and easier to implement.

BTW, date+180 days is probably easier to implement in FMP (and easiest to state in a policy) but the least intuitive and hardest to work out mentally.

[ November 06, 2001: Message edited by: Vaughan ]

---

Tom England,

Extracting the month, adding six months, and reassembling the date is too complicated, especially since you have to make allocations for if it goes into another year. Just add the 180 days to the date. If it goes into another year, Filemaker will automatically adjust the year as well. It's simple and effective.

On the point that Vaughan made with regards to the 180 days. Legally it is better as well to state days instead of months - if 180 isn't the right amount, then just average 31 days a month. That takes you to 186 days, and use that as the value to add. Hope this helps.

P.S. Good call on the legal angle Vaughan.

[ November 07, 2001: Message edited by: proton ]

[Tom England]

As company policies state calender months I have gone for the adding full months approach rather than 180 days. Thanks very much for the advice. I am a newcomer to this site, it is infinately more useful than File Maker support!!!!