I suggest using a custom function to generate a list of dates from the first to second date in your record. You can then count the number of values in this list that fall in each month. Either that or you need to set up a related table of records with just dates--either one date per day or one record per month spanned by the original dates. In this second idea, you would count the records--which would be very straight forward via aggregate functions, ExecuteSQL or in a summary report.
Awhile back I did a blog post about determining if two date ranges overlap. It is also discussed how to count the number of days there is an overlap.
I see your use case as an overlap issue: how many days did the contractor work in the month of march (3/1 - 3/31) and April (4/1 - 4/30).
Attached is a pic that shows the simple result. The 2nd record is one day off; i think the custom function that runs this can be tweaked to fix that.
It is really simple, but you'd need the full month range in two separate fields (or variables) for the comparison to work.
And here's the blog post: http://www.soliantconsulting.com/blog/2016/03/determining-if-two-date-ranges-overlap
DaysCount.fmp12.zip 68.3 K
The first record is off by 1 day as well.
To compute days from date 1 to date 2, the math should be date2 - date1 + 1.
Don't quite see the need for a custom function here but there's many ways to skin this particular cat.
The custom function takes care of all the work, and is really pretty simple. THere's no need for extra relationships or a complex counting of days for a month.
That's the reason for the CF. As you say there are many ways to solve this problem, and hundreds others in FileMaker. The CF works as well as any other method.
As for the count of days being off, it is simply a matter of extending the start date by one day backwards. This is not inclusive of the start days, so that's why the day is one off.
Phil is commenting that your screenshot and OP both wrong on 3/15..4/6 of March as 16, should be 17. 15...31 has 17 days.
And you are splitting one record into two to handle the issue of days to each month--one option that I also mentioned. Once you do that, the math for the number of days is very simple and there isn't much need for a custom function. The "heavy lifting" would be a looping script to generate the needed set of related records. Maybe your custom function is intended to play a role there, but even in that case it all seems pretty simple.
Yep. I stated that earlier.
By adding 1 to the calculation you get the start date as a counted day. As it stood earlier, the start date wasn't counted. But in the simple CF, i simply added 1 to it.
That makes sense. I was simply pointing out a method.
I don't think CFs are less-simple than anything else. And this could be used in a script rather than records.
CF's shine when:
a) It simplifies something you use a lot in calculations
b) It takes the place of a looping script by using recursion
c) It replaces a long complex expression with a single function call
Don't see any of that here, but as I said, there are many ways to skin this cat.
I'd set up a script performed via script triggers on those date fields that checks for any related records in this utility table and deletes them, then loops to create the 1 to several related records for each month. A Calculation field can compute the number of days represented by each related record and a summary field can provide the needed totals and sub totals.
A summary report based on this utility table can provide the number of days each month for one project, multiple projects, broken by by project then by month, or totaled by month, then broken down by project....
I created a custom function which calculates the "Work Days" as I defined them, which means:
-- not a Saturday or Sunday
-- not on one of the customized holidays or other dates I have listed.
(Note: I have 139 holidays in my test logic so the first date duration below probably seems off.)
So, using your example March 15, 2016 -> April 6, 2016, my logic generates:
(Using the 139 possible holidays): Number of workdays between 2016-03-15 and 2016-04-06 is 12 days.
(But, using no holidays and only weekends): Number of workdays between 2016-03-15 and 2016-04-06 is 16 days.
This is how I did it, anyway, as I have the same work days' calculation need often.
That's a good point and a good use for a CF. The OP didn't specify that, but omitting workdays and holidays might be a requirement here. I usually set up a "calendar" table where weekend and holiday dates are both recorded and then I can count "open" days from date1 to date2 vai a relationship as a way to get the number of work days. Makes for a single tool for both holiday and week end dates..
Having holidays and weekends excluded is definitely awesome, but...
Wouldn't it be better to only include consecutive days in the data?
One worker is not equal to another, same applies to circumstances.
ID Worker StartDate EndDate
1 1 2016-06-28 2016-07-05 Has not worked on 2016-07-04 because of Independence Day
2 2 2016-06-28 2016-07-05 Has worked on 2016-07-04 because he ( had / could / needed ) to
For these two the results would be the same.
Instead in this table:
ID Worker StartDate EndDate
1 1 2016-06-28 2016-07-03 Has worked up to 2016-07-03
2 1 2016-07-05 2016-07-05 Has not worked on 2016-07-04 but on 2016-07-05
3 2 2016-06-28 2016-07-05 Has worked on 2016-07-04 because he ( had / could / needed ) to
For these two counting the days is easy and not depending on data being fed ( dynamically ) from external input.
It's a bit more data but it's reliable data.
People could work weekends and holidays...
EDIT: Had to modify the first date in second table to 2016-07-03. Sorry, it's late here
Just my 2 ct,
Agreed ... How you count workdays is up to you and could be specific down to the actual user.
"Workdays" is a business rule any project needs to define.