Jump to content

bittan

Members
  • Content count

    12
  • Joined

  • Last visited

Community Reputation

0 Neutral

About bittan

  • Rank
    member
  1. Please suggest something perfect.The above is only example what i want.My only goal is to calculate the revenue for each quarter ;according to number of days falling in that quarter. Kindly suggest the best formula for this.I know very little about file maker functions and unable to solve such type of problems.Even i don,t know how to use case function properly as you suggested. Thanks.
  2. This is working perfectly.Need further help for making my quarterly revenue calculation automatic. Now i want the result of cDayinThisYr to be automatically entered in to the respective Quarter field according to the numerical value for performing automatic calculation .Following example will help make it more clear. If value is between 20days to 90days then should be automatically entered in to Dec-2012(1ST quarter) and multiplied by fix number(contract Amt/365). If value is between 91days to1180days then should be automatically entered in to March-2013(2nd quarter) and multiplied by fix number(contract Amt/365). If value is between 181 days to270 days then should be automatically entered in toJune-2013(3rd quarter)....... If value is between 271days to365 days then should be automatically entered in to Sep-2013(4th quarter)......... I don't know how to do this at all ! Please help me once again.Please ignore my typing mistakes;if present.Thanks in advance.
  3. sorry members.you are right.It must be "I simply subtracted the CS date from 01/oct/12".i could not notice this big mistake as I am too busy to solve this problem.i do hope you will suggest the best. Thanks in advance.
  4. yes,you are right.Sorry for typing mistake the Number of days in old fin year=304 days. i am interested only for CS date which comes between 01/Oct/2011 to 30/sep/13. CS start date is 01/Jan/12 and CE date is 31/dec/12. Please suggest any suitable formula as i am unable to find or make such type of formula. Please help.
  5. Dear All, How to count number of days passed in old financial year and number of days that will come in current financial year for a given one year contract.It should not count the date before old financial year. i have to made four quarterly report for the each quarter revenue at the end Dec ,March, June and Sep based on number of days falling in each quarter. Following example will make it more clear. CS Date CE Date Contract Amt. 01/Jan/2012 31/Dec/2012 730 Suppose current financial year starts from 01/Oct/2012 Number of days in old fin year=608 days Number of days in New fin year=61 days Dec-2012(1ST quarter)Rev=61 days*1 day contract Amt=122 March-13(2ND quarter)Rev=0......... I simply subtracted the CS date from 31/oct/12.it gives correct result only if CS date is after 30/sep/2011 and CS date field is not empty.it gives 734807 if CS is empty and gives more than 365 days if there any date before of 01/oct/2011.I can't delete or change the dates which is before of 01/sep/2011 due to some reason. Please help.
  6. bittan

    Due date formula

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

    Due date formula

    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.
  8. bittan

    Due date formula

    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.
  9. bittan

    Due date formula

    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
  10. 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.
×

Important Information

By using this site, you agree to our Terms of Use.