AnsweredAssumed Answered

updating calculations

Question asked by kaz_1 on Jul 10, 2009
Latest reply on Jul 10, 2009 by TSGal

Title

updating calculations

Post

Hey,

 

I wanted a way to calculate the amount of time passed between two entered dates, one of which is "current date". I used the following instructions and they worked very well. However, they only calculate the difference in date when you enter the second date. Is there any way that I can set this calculation to run every day so the difference remains accurate as time progresses.

 

Thanks

 

FileMaker Pro has several powerful date functions, which make it possible to determine the number of years, months and days that have elapsed between two dates. This is useful in many ways. For instance, if you had a database that you kept track of your customers. Given a date that a person became a Customer and today's date, we can determine the length of time that they have been a Customer.

The following calculation fields are required for the final Calculation. These calculations compare the value in an existing date field called "Customer Since" with the value obtained using the Status(CurrentDate) function.

Create the following fields:

Customer_Since (Date Field. Auto-enter creation date)

Number_of_Days (calculation, number result):
Get(CurrentDate) - Customer_Since

Month_Length (calculation, text result):
Case(Month(Customer_Since) = "1", "31", Month(Customer_Since) = "2" , "28" , Month(Customer_Since) = "3" , "31" , Month(Customer_Since) = "4" , "30" , Month(Customer_Since) = "5" , "31" , Month(Customer_Since) = "6" , "30" , Month(Customer_Since) = "7" , "31" , Month(Customer_Since) = "8" , "31" , Month(Customer_Since) = "9" , "30" , Month(Customer_Since) = "10" , "31" , Month(Customer_Since) = "11" , "30" , Month(Customer_Since) = "12" , "31" )

Using the calculations above we can know calculate the length of time someone has been a customer by Years, Months, and Days. Please note that the calculations listed above are not meant to be used on the layout. It is only necessary for these fields to exist in the Define Fields. The following uses the calculated values above to display the length of time the person has been a customer in the text format of "yy Years, mm Months, dd Days":

Length_as_a_Customer (calculation, number result):
(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"

Dividing by 365.25 in the year calculation takes into account leap year. When calculating the months we are first checking to see if the number of days is greater than the number of days in the current month. If the number of days is less than the number of days in the current month and If the number of months is also less than 1, then months is equal 0. The last section of the month's calculation calculates the total number of months and subtracts 12 months for every year. We divide number of days by 30.4375 as an average number of days in a month (365.25/12 = 30.4375).










Outcomes