Number of days in old Financial year and in new financial year

[bittan]

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.

[comment]

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.

Not sure what your complaint is.

Obviously, if you subtract CS date from Oct 31, 2012 and CS date is empty, then you haven't subtracted anything - and the result will be the number of days from Jan 1, 0001 until Oct 31, 2012. After all, GetAsDate ( 734807 ) = Oct 31, 2012.

Also, any CS date before Nov 1, 2011 when subtracted from Oct 31, 2012 will produce a result larger than 365 - because more than a year has elapsed.

---

And why subtract from Oct 31, 2012 when the FY starts on October 1?!

Edited October 2, 2012 by comment

[Lee Smith]

Hi Bittan, and Welcome to the Forum,

I'm having a problem following your math.

When did the contract start? It seems by your description that there are three Financial Years (FY) involved, and you are trying to portion the $730 by the FY?

You have CS (contract Start Date) as Jan 1, 2012 and CE (contract End Date ad Dec 31, 2012)?

Why not attach a file?

Lee

[bittan]

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.

[bittan]

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.

[comment]

It's hard to figure out what you're after - perhaps something like this?

BreakDays.fp7.zip

[bittan]

Thanks a lot.

[bittan]

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.

[Lee Smith]

Hi battering,

I should have moved this topic yesterday because, the Function of the Day topic areas is reserved for the discussion of the different functions, plus these topics go back a couple years and we should update them at some point to include the newer functions that have come out since these discussions were created.

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)".

If you should have any questions about this action, please send me a private message.

Lee

[bittan]

Hi Smith,

Can you solve my problem?

[comment]

If value is between 20days to 90days then should be automatically entered in to Dec-2012(1ST quarter)

I am afraid that makes no sense. If the overlap value is 20 days, those 20 days could easily be in the last quarter of the selected year.

I don't know how to do this at all !

Well, you could simply use the Case() function for this - if that's really what you want to do.

[bittan]

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.

[comment]

I am afraid you may have the wrong idea about how this works. The help you get here is supposed to teach you how to solve a particular problem by yourself.

After being shown how to calculate the overlap between a contract's period and a given year, you should be able to adapt the method you have learned to calculating the overlap between a contract's period and a given quarter.

If you cannot or will not learn how to do it yourself, consider hiring someone to do it for you.