8 Replies Latest reply on Dec 7, 2016 12:12 PM by alquimby

# Calculating Elapsed Time Between two dates

Hello All,

So I don't know if anyone else has had this issue before. But I like how the FM KB had this calculation as an example. But here is what I have:

I have it set to use CurrentDate IF DecomissionDate is empty.

Number_Of_Days Script:

If (

IsEmpty ( DecomissionDate ) ;

Get(CurrentDate)  ; DecomissionDate

)

- CommissionedDate

Month_Length:

Case(

Month(CommissionedDate) = "1"; "31" ;

Month(CommissionedDate) = "2" ; "28" ;

Month(CommissionedDate) = "3" ; "31" ;

Month(CommissionedDate) = "4" ; "30" ;

Month(CommissionedDate) = "5" ; "31" ;

Month(CommissionedDate) = "6" ; "30" ;

Month(CommissionedDate) = "7" ; "31" ;

Month(CommissionedDate) = "8" ; "31" ;

Month(CommissionedDate) = "9" ; "30" ;

Month(CommissionedDate) = "10" ; "31" ;

Month(CommissionedDate) = "11" ; "30" ;

Month(CommissionedDate) = "12" ; "31"

)

Final Script:

Time_Lapsed:

(Int(

Number_of_Days / 365.25 )) &

" Years " & If(

(Round( Mod( Number_of_Days ; 30.4375 ) ; 0 )) <=

Month_Length and ((Int(Number_of_Days / 30.4375) ) ) < "1" ; "0" ;

((Int(Number_of_Days / 30.4375) - (12 * (Int( Number_of_Days / 365.25 )))))

)

& " Months " & (Round( Mod( Number_of_Days ; 30.4375 ) ; 0 )) & " Days"

Script Reference:

Issue:

If I have a date of 12/06/2015 , the Lapsed time shows 1 years, 0 Months, 2 Days.

It is currently 12/07/2016 which should make it 1 Day not 2...

Any thoughts would help greatly.

Thank you all.

• ###### 1. Re: Calculating Elapsed Time Between two dates

When comparing numeric values such as:

Month_Length and ((Int(Number_of_Days / 30.4375) ) ) < "1"

Don't enclose the numbers in quotes.

A simpler way to get the number of days in a month:

Day ( Date ( Month ( YourDateFieldHere ) + 1 ; 0 ; Year ( YourDateFieldHere ) ) )

You can use any date that falls in the month for which you need the number of days.

1 of 1 people found this helpful
• ###### 2. Re: Calculating Elapsed Time Between two dates

Don't forget there was a 2-29-2016 this year. Your result is correct. If you change your Decommission Date to 12-6-2016 your time elapsed will be 1 day. FileMaker's Date and Timestamp fields account Leap Years.

• ###### 3. Re: Calculating Elapsed Time Between two dates

Allen you are totally correct.

What a great brain fart on my end.

Thank you Phil for your suggestion. I have removed the quotes on the numbers.

Thank you all.

• ###### 4. Re: Calculating Elapsed Time Between two dates

Actually, I think your calc is not correct after all. Try this one (unstored):

Let ( [

C = Get ( CurrentDate ) ;

yC = Year ( C ) ;

mC = Month ( C ) ;

dC = Day ( C ) ;

doyC = DayOfYear ( C ) ;

B = Commission ;

yB = Year ( B ) ;

mB = Month ( B ) ;

dB = Day ( B ) ;

doyB = DayOfYear ( Date( mB ; dB ; yC ) ) ;

num_years = ( yC - yB - ( doyC < doyB ) ) ;

num_months = Mod ( mC - mB - (dC < dB ) ; 12 ) ;

num_days = C - Date ( mC - ( dC < dB ) ; dB ; yC ) ] ;

num_years & " Years, "  &  num_months  & " Months, "  & num_days & " Days")

If Commission Date is 12-6-2015, using the above, you should get 1 year, 0 months, 1 day.

• ###### 5. Re: Calculating Elapsed Time Between two dates

Right. But 2016 is a leap year, adding an extra day.

Therefore, having that calculation equal 1 year, 0 months, 2 days will be correct.

• ###### 6. Re: Calculating Elapsed Time Between two dates

Hi,

one of many ways of finding the last day of the month is this calculation:

Date(\$month + 1; 0; \$year)

• ###### 7. Re: Calculating Elapsed Time Between two dates

a bit more ...

the zero in the date simply represents the first day of the month, minus one, ergo, the last day of the previous month.  Similarly, the month 13 is the first month of the following year ...

• ###### 8. Re: Calculating Elapsed Time Between two dates

The time elapsed in days between 12-6-2015 and 12-7-2016 is 367 (12-6-2014 to 12-7-2015 is 366). But 12-7-2016 is 1 year, 0 months, and 1 day removed from 12-6-2015.