Calculate total time

[Mr_Dog]

I have a business that runs on memberships. In my FM DB, I have two fields; one says "Date of Enrollment", and the other says "Length of enrollment". How can I calculate the length of enrollment from the date of enrollment? The result should be a numeric value, (i.e. 6, as opposed to "six") Please type slowly, I'm not very adept at this.

Thanks,

Mr_Dog [email protected]

[EddyB]

Hi,

Make sure your date of enrollment is actually formatted as a date field. Then create a calculation field - Length of enrollment

Length of enrollment=

Status(CurrentDate) - DateOfEnrollment

Make sure you set the calc to not store the result.

Ed

[LaRetta]

Hi Mr_Dog,

You didn't indicate if '6' is for days, months, or years.

Ed

[Mr_Dog]

If I needed to show number of months for Lenght of Enrollment, would I use:

(Status(CurrentDate) - DateOfEnrollment) / 12 (or 365/12)?

Mr_Dog

[Ugo DI LUCA]

May be

(Year(Status(CurrentDate))-Year(enrolment))*12+Month(Status(CurrentDate))-Month(enrolment)

[Lee Smith]

(Year(Status(CurrentDate) + 1) - Year(enrollment + 1)) * 12 + (Month(Status(CurrentDate) + 1) - Month(enrollment + 1)) - Case(Day(Status(CurrentDate) + 1) = Day(enrollment), 0, Day(Status(CurrentDate) + 1) < Day(enrollment + 1), 1, 0)

[Ugo DI LUCA]

Huh ?!

I wish I was Month(Status (CurrentDate)) +1, cause I'd be in Holidays....

Definitely need some rest.

May you extend on your logic Lee. I'm sure you did a test with it. Why all these +1 ?

[LaRetta]

Hi guys,

Okay, mathematics is not one of my strongest skills, but wouldn't this be easier?

Round((Status(CurrentDate) - DateOfEnrollment) / 365 * 12, 2)

LaRetta

[Ugo DI LUCA]

Hmmm...

Some comparisons :

Date of enrolment : Yesterday

Lee---> 0

LaRetta ---->.03

Ugo--->0

Date of enrolment : Today

Lee--->0

LaRetta--->0

Ugo--->0

Date of enrolment : 04/10/02

Lee--->8

LaRetta--->9,01

Ugo--->8

and strange enough

Date of enrolment : 7/10/02

Lee--->8

LaRetta--->8,91

Ugo--->9

I therefore assume Lee's calc is correct as it take into account that this employee is still in his 8th month.

Given date of enrolment

[LaRetta]

Hi everyone!

Well, mine includes round which rounds the result up or down. Extend your decimals out and take a look. As I indicated, it depends upon what is needed. If someone enrolled on 7/3/2003, if you don't extend at two decimals, it'll display '0.'

Using Round (2-decimals) when I test it ... 7/10/2002 produces 11.8. Uhhh, isn't that correct? And 4/10/2002 produces 14.79. Maybe you're not using my formula. Ugo, it sounds like you've got my formula wrong! It's:

Round((Status(CurrentDate) - DateOfEmployment) / 365 * 12, 2)

You can also use Truncate or Int to fine-tune the exact way to handle it. Again, it depends upon what is needed.

LaRetta

[Ugo DI LUCA]

Hihi...

You know of my terrible nightmare for feb 29th....

Date of enrolment : 29/02/2000

Status(CurrentDate) = 30/03/2000

Results for both Lee and LaRetta ---->0

Forgot about mine, it's not better, and even worse

[LaRetta]

When using full, complete dates, FM tracks leap year just fine. When breaking the dates up, leap year can cause major headaches for sure!!

LaRetta

[Ugo DI LUCA]

LaRetta,

Sorry about my dates. European format. I meant october 7th...

I'm still screwed with this.

[Ugo DI LUCA]

My last try...

(Year(Status(CurrentDate) - Year(enrolment) - (DayofYear(Status(CurrentDate)) < DayofYear(enrolment))) *12

+

MOD(Month(Status(CurrentDate))-Month(enrolment)+12;12)

[Lee Smith]

Ugo, what is JOURANNEE

mean

Lee

[Ugo DI LUCA]

JOURANNEE ? Where ?

It's "DayofYear"...

But here's a correction...

((Year(Status(CurrentDate))-Year(enrollmentdate))-(Status(CurrentDate)<enrollmentdate))*12+MOD(Month(Status(CurrentDate))-Month(enrollmentdate)+12;12)-(Day(Status(CurrentDate))<Day(enrolment))

I had never done this "omission" of Case. That's fun though.

[LaRetta]

Hi Mr_Dog,

Please don't be confused with all the banter! If you're unsure, as you probably are by now, create calcs (number) of each of our formulas and place them in a table, type in some enrollment dates and take a look for yourself.

If they all provide you with the results you want, keep in mind that simpler formulas are more efficient (less processor cycles).

Oh, on Ugo's last calculation, he's missing a few parenthesis on the end so you'll need to add those (probably just didn't grab it all when copying it).

Have fun with it, okay?

LaRetta

[Ugo DI LUCA]

Thanks LaRetta.

Yes don't get confused.

Now, when we modify a post, the threads are "threading" at right side instead of the extra carriage

[Lee Smith]

Ugo's last calculation worked fine except for the European ";" instead of ","

here - -Month(enrollmentdate)+12;12)-(Day(.....

Lee

[LaRetta]

Hi Lee,

Yes I caught the

[Mr_Dog]

Hello all,

I can't say that I actually <I>understand</I> it all, but it does work! Thanks so much for the time you put into my little problem. I truly appreciate it!

Mr_Dog

************

A Dog is just a dog,

Until he turns to face you.

Then he is Mr. Dog.

- Chinese Proverb

[Ugo DI LUCA]

Hi,

At least, this demonstrate again that the number of posts has no influence over the quality of the post.

It also shows that "When it's time for bed, there's no need to wait"

To explain the logic, split it in several parts .

1.Number of calendar years from date of enrollment.:

(Year(Status(CurrentDate))-Year(enrollment))

--->2003-2002 = 1

2. Check that one year has been completed :

DayOfYear(Status(CurrentDate))<DayOfYear(enrollment)

Return 1 if not. This could have been used with a Case Statement, but this boolean calc is sufficient here.

----> (07/05--186) < (10/07--280) ----> 1 year to be substracted to the previous calc.

3. Change Years in Months.

((Year(Status(CurrentDate))-Year(enrollment))-(DayOfYear(Status(CurrentDate))<DayOfYear(enrollment)))*12

4. How many Month since last "Anniversary" Date (including Leap Years - As LaRetta interrestingly pointed it, it seems it has no influence on Months, while it has for weeks)

Mod(Month(Status(CurrentDate)) - Month(enrollment) +

Month(Date(12,31,Year(Status(CurrentDate)))), Month(Date(12,31,Year(Status(CurrentDate))))

This latest calc, originally designed for a Week count (Substituting 'Month' by 'WeekOfYear' was given by Lee in a post called "Use CASE or IF statement".

5. Check that one month is completed :

Day(Status(CurrentDate)<Day(enrollment)

Return 1 if not. This could have been used with a Case Statement, but this boolean calc is sufficient here.

----> (05) < (07) ----> 1 month to be substracted to the previous calc.

So... :

((Year(Status(CurrentDate))-Year(enrollment))

-(Month(Status(CurrentDate))<Month(enrollment)))*12

+Mod(Month(Status(CurrentDate)) - Month(enrollment) +

Month(Date(12,31,Year(Status(CurrentDate)))), Month(Date(12,31,Year(Status(CurrentDate))))

-(Day(Status(CurrentDate))<Day(enrollment)))

should be a correct logic (even if complex) calculation.

HTH.

[EddyB]

Hi Ugo,

I think you've made a small error on the last calc.

If the day of enrollment is greater than the day of today than an extra month is added on.

For example,

If you enter the date 4th June 2003

Then the calc correctly works out 1 month

If you enter the date 7th June 2003

Then the calc works out 2 months

I'm having a look at your calc to see if I can see why this is happening.

Ed.

[Ugo DI LUCA]

God Damned....

I wish I had same systems as yours so I would simply cut and paste the calcs to this board. Thanks Ed.

Christian, didn't you say you were working on this kind of tool?

Missing parenthesis again.

A la Excel

A = Year(Status(CurrentDate))

B= Year(enrollment)

C= Month(Status(CurrentDate))

D= Month(enrollment)

E= Day(Status(CurrentDate))

F= Day(enrollment)

G= Month(Date(12,31,A))

((A-:-(C<D))*12 +(Mod(C-D+G,G))-(E<F)

((Year(Status(CurrentDate))-Year(enrollment))-(Month(Status(CurrentDate))

<Month(enrollment)))*12 +(Mod(Month(Status(CurrentDate))-Month(enrollment)+

Month(Date(12,31,Year(Status(CurrentDate)))),

Month(Date(12,31,Year(Status(CurrentDate))))))-(Day(Status(CurrentDate))<Day(enrollment))