Counting months 1

[otherr]

I need to count how many months from "Event" to "Today"
I can count the month, calendar month, but I want to count the month from the date of the event to the same date the next month and then that makes 1 month.
Can you help me?
Thank you

Odette

 

[JeanW]

Several ways to go.

dateStart and dateEnd are the date fields. They need to be validated to ensure that dateEnd >= dateStart.

1. ( Year(dateEnd) - Year(dateStart) ) +
( Month(dateEnd) - Month(dateStart) )

-----

2. 12 * ( Year ( dateEnd ) - Year ( dateStart ) )
+ Month ( dateEnd ) - Month ( dateStart )
- ( Day ( dateEnd ) < Day ( dateStart ) )

Note that months come in several lengths, so 'time in months' is a rather vague term. This calc considers a month as elapsed when the day of the current month is equal to or greater than the day of the original month.

-----

3. The average Gregorian year is 365.2425 days. The average month is 30.436875 days.

Since Filemaker stores dates as days elapsed since 1/1/0, if all you want is the number of average months elapsed between two dates, it's simply
( dateEnd - dateStart ) / 30.436875

HTH

 

[otherr]

It didn't work.
I would like to know:

If the (dateStart) is September 4th and the (dateEnd) is December 2nd I want the result to be 2 months. After December 4th it should say 3 months.

I think that I should put a If... Else... somewhere. What do you think?

 

[JeanW]

Depends on what you see as 'month'.
Is it 30 days ? Is it 31 days ? or is it sometimes 28 days ?
Or is it always at least 28 days ?
Before you adventure into nested If and Else (how manage a leap year)for all the possible in between months (how are you goingto find 'februari' in the range.... try this:

(Year(endDate + 1) -
Year(startDate + 1)) * 12 +
(Month(endDate + 1) -
Month(startDate + 1)) -
Case(Day(endDate + 1) = Day(startDate); 0; Day(endDate + 1) < Day(startDate + 1); 1; 0)

Or, if you want or can use an extention on the time, you could go for something along these lines:

Let (

[theDate = Get(CurrentDate);
leapFactor = If ( Mod ( Year ( theDate ) ; 4 ) = 0 ; 1 ; 0 )
];

Case (
IsEmpty(datefield); "" ;
// Time in years
format = 1 ; Year ( theDate ) - Year ( datefield ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( datefield ) );

// Time in years and days
format = 2 ; Year ( theDate ) - Year ( datefield ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( datefield ) ) & " years, " & Case (
( DayOfYear ( theDate ) - leapFactor ) ? DayOfYear ( datefield ) ; DayOfYear ( theDate ) - leapFactor - DayOfYear ( datefield ) ;
DayOfYear ( theDate ) + ( DayOfYear ( Date ( 12 ; 31 ; Year ( theDate ) ) - DayOfYear ( datefield ) - leapFactor ) ) ) & " days" ;

// Time in years, months and days
format = 3 ; Year ( theDate ) - Year ( datefield ) - ( ( DayOfYear ( theDate ) - leapFactor ) < DayOfYear ( datefield ) ) & " years, " & Mod ( Month ( theDate ) - Month ( datefield ) + 12 - (Day ( theDate ) < Day ( datefield ) ) ; 12 ) & " months, and " & (theDate - Date ( Month ( theDate ) - (Day ( theDate ) < Day ( datefield ) ) ; Day ( datefield ) ; Year ( theDate ) ) ) & " days"

)
)

This calc needs a additional field (format) for 1,2 or 3 as factor to make sure the result is as mentioned in the calc.
Or you just pars a piece of the calc.
If you have FM Advance you use it as a Custom Function.

HTH

 

[otherr]

Thank you very much...... The first formula worked.