Date Calc

[stefanshotton]

I have a field in date format.

Date moved into current address.

I need to calculate from that date how many years and months they have been there compared with another/current date.

ie 3years 4 months

[RalphL]

Check this:

http://www.fmforums.com/threads/showflat...true#Post142645

[CobaltSky]

Hello stefanshotton,

There are several ways to approach the calculation of elapsed time and which is best depends on how you define the problem and also on how much finesse you require.

As part of the definitional problem is the question of whether you wish to count whole months, whole or part months, months elapsed from day to day (so 13 Jun to 13 Jul is 1 month but 13 Jun to 12 Jul is not), whole calendar months only, etc etc.

As regards 'finesse', the question is largely one of presentation. Do you want to trap for empty values or for an end date that precedes the start date (either of which might otherwise produce erroneous output), do you want the zero values stated (eg 0 years, 0 months) or only in certain circumstances, do you want the labels to correctly reflect the singular and plural form (1 month, 2 months) and so on.

As you can see, there are a number of options which must be taken into account in the design of the code if it is to give you the output you require. Notwithstanding that, here is a formula which may (or may not) be suitable:

Let([

A = GetAsNumber(Year(Date2) - Year(Date1));

B = GetAsNumber(Date(Month(Date2); Day(Date2); Year(Date1)) < Date1);

C = A - B; //-------whole years elapsed

D = GetAsNumber(Month(Date2) - Month(Date1));

E = GetAsNumber(Day(Date2) < Day(Date1));

F = GetAsNumber(Month(Date2) < Month(Date1) or 

(Month(Date2) = Month(Date1) and E));

G = D - E + 12 * F]; //-------whole months elapsed

Case(Date1 and Date2 and Date2 >= Date1;

If(C; C & " Year") & If(C > 1; "s") & 

If(C and G; ", ") &

If(G or not C; G & " Month") & If(not (C + G) or G > 1; "s")

)// End Case

)// End Let

- where the starting date is a date field named 'Date1' and the ending date is a date field named 'Date2'.

The above code will produce a null result unless dates are entered into both input fields and the end date does not precede the start date. It deals with whole actual months rather than calendar months. Part months are ignored. Zero values will be suppressed except during the first month (when '0 months' will be returned), so before twelve months have elapsed the duration will be returned in months only, and when whole years (and no complete additional months) have passed the duration will be returned in years only. When the duration is returned in both months and years, a comma and space are included between them.

The above may not exactly match your requirements, but I'm afraid you didn't provide a lot of information to go on. If it does not, then perhaps it will give you a starting point from which to build a variation that will satisfy you.