Date Calculation - Days of Month

[LinaML]

Hello,

this is my first post, so be gentle :

I have a problem with date calculation. I have a time period. I need to "group" these days by months and then multiple each group of days with different number. How can I do that?

Days in each "group" (1,2...12) can belong to different years. Am I clear?

Thanks for help!

LinaML

[Søren Dyhr]

It's not quite clear what you want to achieve, but if it's a summary, where you wish to make the breaker the month - is it just Month("theDate")

Has it something to do with discounts given when sales reaches a certain level inside the same month??

--sd

[LinaML]

Hello,

thank you for response.

I need a formula which can calculate taxes for period. Tax per day is different for each month. Is this possible?

LinaML

[mz123]

I'm attaching a sample file.

If the tax rates won't change for each month, then you can create a case statement like I did for the field "Tax Calc"

otherwise you may want to create a table that has a field for the month, the year, and the tax amount. If you would like me to mock this up as well, let me know.

Does that make sense?

test.fp7.zip

[LinaML]

Thank you Martha,

but what I need is calculation which will give me the number of days for specific period and then multiple these number with different amount.

Example

Start day: 01/20/2006

End day: 02/17/2006

Number of days: 28

Days - January: 12

Days - February: 16

Result=12*x+16*y etc

Period also can be through 2 years (12/26/2005 - 03/18/2006) or 3 years (12/26/2005 - 06/01/2007)

Am I clear now?

Thanks,

LinaML

[Søren Dyhr]

MMS6F there's no need to make a long Case( statement, when dealing with integers, Choose( does it faster:

http://www.filemaker.com/help/FunctionsRef-33.html

When I saw the word "group" did I think of Summaries, so if there are SEVERAL earnings (more records) within the same month is this function an option:

http://www.filemaker.com/help/FunctionsRef-341.html

Say you wish to deal with sales discounts, dependant on the month could it be done this way:

Let(theGrouping = GetSummary ( SaleSumming ; makeBreaker );





Case(

theGrouping < 1000; Sales ;

theGrouping < 2000; Sales * ,8;

theGrouping < 3000; Sales * ,7;

theGrouping < 15000; Sales * ,6;

))

What it does is that it works through the lines, first time a "Yes" occures in the Case( statement wil the belonging calc work, since filemaker not as c++ deals with "switch".

--sd

[Søren Dyhr]

Update! I do now see clearer what you're after. This is definately Summaries territory, make a found set by searching on the start date and the end date.

Choose ( makeBreaker-1; ,9; ,8 ; ,7; ,6; ,6; ,7; ,9; ,8 ; ,7; ,6; ,6; ,7 ) * GetSummary ( SaleSumming ; makeBreaker )

...is then the field that's summarised (yet another summary) on to get the payout for the entire span! makeBreaker is a calc' = Month(theDate)

--sd

[Søren Dyhr]

'''

Edited May 24, 2006 by Guest

[comment]

Seems like a tough problem. It wouldn't be too hard to calculate:

- the number of days falling in the particular month in the starting year;

- the number of days falling in the particular month in the ending year;

- the number of whole years between starting year and ending year.

But if the particular month is February, we'd need to check each year for leap/regular.

I would rather use a 'brute force' calculation: a custom function that loops thru the days one by one. I haven't checked this very thoroughly, but it seems to work (slowly, though):

CountMonthDays ( startDate; endDate; monthNumber )

Let (

d = Month ( startDate ) = monthNumber

;

d + Case ( startDate < endDate ; CountMonthDays ( startDate + 1 ; endDate ; monthNumber ) )

)

[Raybaudi]

Hi comment

not so slowly...< 1 sec for 25 years !

BTW: Beautifull calc but...can you explain for us mere mortals !

( I think true (1) + recursion : is it correct ?)

[comment]

Ah. I did this in a file with some previous CFs dealing with date ranges, so it seemed slow, because all the other functions were calculating at the same time.

Your explanation is correct: test returns 1 if the date falls in the specified month; then move on to the next date, adding up as you go.

I was thinking it could be optimized by jumping, Edoshin-style, straight to the next 1st of the month. So say for 25 years, instead of evaluating app. 365*25=9125 days, it would only require 25 iterations or so.

[LinaML]

Hello!

Thank you all! I finally did it with my own calculation (if and case functions), but I will try yours.

Thank you very much!

LinaML