Due date formula

[bittan]

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.

[comment]

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?

[bittan]

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

[comment]

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.

[Lee Smith]

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

[bittan]

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.

[comment]

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.

[bittan]

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.

[comment]

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

[bittan]

Thanks a lot.you are great and genius.i followed your suggestion to use with n* and everything is working fine. :jester: