bittan Posted August 25, 2012 Share Posted August 25, 2012 Hi everyone, I am newbie and expecting help for how to use few of my excel due date formulas in FM.The formulas are...... DATE(YEAR(H2),MONTH(H2)+(12/K2),DAY(H2)-1) H2 is start date K2 is Number between 2 to 4 IF(1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(1)),DAY(F2)))) F2 is date K2 is Number between 2 to 4 Thanks in advance. Link to comment Share on other sites More sharing options...
comment Posted August 25, 2012 Share Posted August 25, 2012 The Date() function in Filemaker is very similar to Excel's, except for the order of arguments: http://www.filemaker.com/11help/html/func_ref1.31.13.html#1028390 I didn't understand the IF(1,...) construct in the second formula. Why not explain what do you expect to calculate? Link to comment Share on other sites More sharing options...
bittan Posted August 25, 2012 Author Share Posted August 25, 2012 I think following example will help. client has purchased a new machine on 16/jan/2012 and the warranty of the machine is one year(that is 15/jan/2013).Under warranty period(16/jan/12--15/jan/13) i have to provide 3(it may be between 2 to 4 ;depends upon purchase agreement) periodic servicing of the machine within the warranty period equally equally divided by number of servicing. Warr. start Date Warr.End Date No. of ser ser 1 Period ser. 2 Period ser3Period 16/jan/12 ---------15/Jan/13 3 16/jan/12--15/may/12 16/may/12-15/sep/12 16/sep/12-15/Jan/13 Link to comment Share on other sites More sharing options...
comment Posted August 25, 2012 Share Posted August 25, 2012 i have to provide 3(it may be between 2 to 4 ;depends upon purchase agreement) periodic servicing Well, then you need either [a] 4 calculation fields or a repeating calculation field with 4 repetitions or [c] up to 4 records in a related table. Which is best depends on how you intend to use the results later on; for example, if you want to add more attributes to each service (such as the actual date, etc.), then there can be no doubt that [c] is the correct route to take. In any case, to calculate the last date for the n-th service, you can use = Date ( Month ( StartDate ) + n * ( 12 / K ) ; Day ( StartDate ) - 1 ; Year ( StartDate ) ) where K is the number of services in a year. Link to comment Share on other sites More sharing options...
Lee Smith Posted August 25, 2012 Share Posted August 25, 2012 Hi bittan, and welcome to the forum, Automatic message This topic has been moved from "Database Schema & Business Logic → Calculation Engine (Define Fields) → Function of the Day" to "Database Schema & Business Logic → Calculation Engine (Define Fields)". The Function of the Day Topic Area was created to discussed the different functions available (i.e. purpose and use) back in May 2008. Yeah, it's a little old, and needs to be updated to include the newer functions. Lee Link to comment Share on other sites More sharing options...
bittan Posted August 25, 2012 Author Share Posted August 25, 2012 Thanks a lot!! working perfectly. Can you remove n* from formula? This is very difficult to specify different n* and different K for each records.hoping you will do solve my problem.Thanks in advance. Link to comment Share on other sites More sharing options...
comment Posted August 25, 2012 Share Posted August 25, 2012 This is very difficult to specify different n* and different K for each records. I don't see why: you have a field for K, where each record can have a different value. As for n, it depends on which of the three options above you have chosen. Link to comment Share on other sites More sharing options...
bittan Posted August 25, 2012 Author Share Posted August 25, 2012 i have already imported 3000 records from excel with Different K. My layout has 4 K fields;the maximum number for Ks, as the K can be changed anytime!!!!!The K can't be more than 4 and less than 2(that is it may be 2 or 3 or 4) in EXCEL the following formulas is working perfectly. formula for K1 start date:IF(1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(2-1)),DAY(1)))) where F2 is warranty start date and K is total number of service F2 refer Warranty start date cell. formula for K1 start date:IF(1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(1)),DAY(F2)))) formula for K1 End date:DATE(YEAR(H2),MONTH(H2)+(12/K2),DAY(H2)-1) where H2 reffer F2 cell. K2 start date :IF(2=1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(2-1)),DAY(1)))) formula for K2 End date:DATE(YEAR(L2),MONTH(L2)+(12/K2),DAY(L2)-1) where L2 reffer H2 cell IF(3=1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(3-1)),DAY(1)))) formula for K3 start date:IF(3=1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(3-1)),DAY(1)))) formula for K3 End date:DATE(YEAR(O2),MONTH(O2)+(12/K2),DAY(O2)-1) Where O2 refer to L2 cell formula for K4 start: IF(4=1,F2,(DATE(YEAR(F2),MONTH(F2)+((12/K2)*(4-1)),DAY(1)))) K4 end date:DATE(YEAR(R2),MONTH(R2)+(12/K2),DAY(R2)-1) In the above mentioned formula if i put K=2 in total number service it shows two sets of periods as follows: start date 1--------end date 1 start date 2------- end date2 if i put K=3, it will show; start date 1---end date 1 start date 2---end date2 start date3--end date3 And so on. requesting you to make something like this. Link to comment Share on other sites More sharing options...
comment Posted August 25, 2012 Share Posted August 25, 2012 I am afraid you have lost me somewhere along the way. Perhaps the attached can make things clearer. Mind you, I don't think that's the best approach: for example, it would be difficult to perform a find by date - since the calculated dates are split among 4 separate fields. ServicePeriods.fp7.zip Link to comment Share on other sites More sharing options...
bittan Posted August 26, 2012 Author Share Posted August 26, 2012 Thanks a lot.you are great and genius.i followed your suggestion to use with n* and everything is working fine. :jester: Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4233 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now