Calculating whole months elapsed

[Drewru]

I'm trying to write a script (using FMP 11) that will compute the number of whole months (and days) elapsed between 2 dates, where a whole month is defined as the period from the first day of that month through the last day of that month. So:

Jan 1 to Jan 31 = 1 whole month

Jan 1 to Feb 1 = 1 whole month + 1 day

Jan 15 to Feb 15 = 0 whole months + 30 days

Jan 15 to Feb 25 = 0 whole months + 40 days.

[mr_vodka]

Feb 3 to March 3. Is that a whole month? Is Feb 3 to Feb 28th a whole month?

 

The old question, what constitutes a month?

[Drewru]

No, Feb 3 to Mar 3 is not a whole month, nor is Feb 3 to Feb 28. As I stated in my original post, a whole month would be defined as the first day of a month through the last day of that month. Please look at the examples I provided in my original post.

[Lee Smith]

Hi Drew, and welcome to the Forum.

 

First of all, please don't be terse in your responses to those who are trying to help. We often ask questions that will sound like we didn't read the question, most of the time we are digging deeper in order to point you in a direction that might help.

 

With that said. Have you looked at the different Custom Functions available at  Brian Dunning's site. HERE ?  Maybe you can modify one of them and get what you are after.

 

Keep in mind, as John stated above, the date calculations in FileMaker can be inaccurate, and I believe that is why he question what you are trying accomplish. As I stated, giving us more information about the use of this calculation can be helpful.

 

Lee

[Drewru]

Sorry, I didn't realize that I was being terse; I apologize if I was coming across as condescending or insulting.

Yes, I have looked at the custom functions at Brian Dunning's site, and a couple of others but did not find what I wanted.

To clarify, if I have 2 dates defining some period, I want the number of whole months plus the number of non-whole month days. A whole month being defined as day one of a month through the last day of that month. So, the 15th of one month to the 15th of the next month would not be any whole months.

If the first day of the first month in the given range is not the first of that month, then that first month is not a whole month. If the last day of the last monthe in the given range is not the last of that month, then it is not a whole month.

For example, If date1 is Jan 5, 2013 and date2 is Apr 2, 2013, then that would be 2 whole months plus 28 days (Jan 31 - Jan 5 + Apr 2 - Apr 0)

Trying to clarify this and be explicit has led me to coming up with a possible solution. I think the following will get me what I need, did I miss something?

Let (
    [
        // if Invoice Start is the first day of the month, use that; else use first day of the next month
        ~start = Contacts::Invoice Start ;
        ~start =
        If (
            Day ( ~start ) = 1 ;
            ~start ;
            Date ( Month ( ~start ) + 1 ; 1 ; Year ( ~start ) )
        ) ;
        
        // if Invoice End is the last day of the month, use that; else use last day of previous month
        ~end = Contacts::Invoice End ;
        ~end =
        If (
            Day ( Date ( Month ( ~end ) + 1 ; 0 ; Year ( ~end ) ) ) = Day ( ~end ) ;
            ~end ;
            Date ( Month ( ~end ) ; 0 ; Year ( ~end ) )
        ) ;
        
        // number of whole months
        ~months = Month ( ~end ) - Month ( ~start ) ;
        
        // number of non-whole month days
        ~days = ( ~start - Contacts::Invoice Start ) + ( Contacts::Invoice End - ~end )
    ] ;

    ( Summary Total Weight * ~months * Invoice Rate ) +
    ( Summary Total Weight * ~days * Invoice Daily Rate ) +
    Summary Total Shipping - Summary Total Discount
)

[Raybaudi]

did I miss something?

 

 // number of whole months
        ~months = Month ( ~end ) - Month ( ~start ) + 1 ;

... and you have to consider dates in the same month and/or dates in different years.

[Raybaudi]

You could try this one:
 

Let([
start = dateFrom ;
end = dateTo ;
~start = If ( Day ( start ) = 1 ; start ; Date ( Month ( start ) + 1 ; 1 ; Year ( start ) ) ) ;
~end = If ( Day ( Date ( Month ( end ) + 1 ; 0 ; Year ( end ) ) ) = Day ( end ) ; end ; Date ( Month ( end ) ; 0 ; Year ( end ) ) ) ;
~months = Month ( ~end ) - Month ( ~start ) + 1 + ( Year ( ~end ) - Year ( ~start ) ) * 12 ;
~days =  If ( start - Day ( start ) = end - Day ( end ) and ~months  ≤ 0 ; end - start  + 1 ;  ~start - start  + end - ~end ) 
];
Case(
start > end or ValueCount ( List ( dateFrom ; dateTo ) ) < 2 ; "" ;
Substitute ( List ( If ( ~months > 0 ; If ( ~months > 1 ; ~months & " months" ; ~months & " month" ) ) ; If ( ~days ; If ( ~days > 1 ; ~days & " days" ; ~days & " day" ) ) ) ; ¶ ; " and " )
)
)

[Drewru]

Raybaudi, I get everything up to the case statement, it is essentially what I already had, but with correction for different years. I'm not sure I get what you're doing in the case statement.

[Raybaudi]

After doing a check on the two date fields ( if empty or with incorrect values ), substantially I build a string with the singular or plural words.

[Drewru]

Ah. So, if I'm already pre-verifying that start and end are populated with valid dates, and that start precedes end, then I don't need the case statement?

[Raybaudi]

ofcourse

[Drewru]

Ok, one last question, I think (I hope). If I have a date range from June 15 to August 5, that should work out to 1 whole month plus 20 days. Doesn't the calculation I wrote get me that? I was adding up the days from start to the start of the first whole month and the days from the end of the last whole month to end. I'm not sure what your clause is doing for me: ~days = If ( start - Day ( start ) = end - Day ( end ) and ~months ≤ 0 ; end - start + 1 ; ~start - start + end - ~end )

[Raybaudi]

A date range from June 15 to August 5 returns 1 month and 21 days ( 16 days of june + 5 days of august ).

 

If ( start - Day ( start ) = end - Day ( end ) and ~months ≤ 0 // if the two dates are in the same month and same year

[Drewru]

Looks like I was on the right track, generally, but missing all of the edge cases. Thank you very much for straightening me out Raybaudi, you've been a big help.