×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Counting months

Counting months

Counting months

(OP)
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

RE: Counting months

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


RE: Counting months

(OP)
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?

RE: Counting months

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

RE: Counting months

(OP)
Thank you very much......  The first formula worked.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close