Jump to content

Date Calculation - Days of Month


This topic is 5676 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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? :confused:

Thanks,

LinaML

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5676 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

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