13 Replies Latest reply on Jan 8, 2017 5:19 AM by fmpdude

    Count of Days Between Two Dates, Broken Down By Month

    multisonic52

      Hi there:

       

      I'm working on a solution that tracks freelance contractors and the amount of days they work for a specific client.  We input the start and end date for each contractor with a StartDate and EndDate and then perform a simple calculation to get the number of days work.

       

      In an effort to track the overall work loads of our individual contractors, I'm hoping to find a way to break that span of days worked into days worked each month, i.e. 3/3/16 to 3/9/16 would count March 2016 -- 7, whereas 3/15/16 to 4/6/16 would count March 2016 -- 16 and April 2016 -- 6. Is there a way that anyone can think to achieve this?

       

      Additionally, my eventual goal is to generate a report that shows total counts of all this information for a given year, for all freelancers.  It does not matter which client they worked for, just an overall count of days worked. Any thoughts on how to begin to approach this?

       

      Thanks in advance for any guidance you can provide!

      Mike

        • 1. Re: Count of Days Between Two Dates, Broken Down By Month
          philmodjunk

          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.

          • 2. Re: Count of Days Between Two Dates, Broken Down By Month
            jbrown

            Afternoon.

            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.

             

            I've attached the file as well. Screen Shot 2017-01-06 at 2.18.54 PM.png

            And here's the blog post: http://www.soliantconsulting.com/blog/2016/03/determining-if-two-date-ranges-overlap

            Determining if Two Date Ranges Overlap - Soliant Consulting

            • 3. Re: Count of Days Between Two Dates, Broken Down By Month
              philmodjunk

              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.

              • 4. Re: Count of Days Between Two Dates, Broken Down By Month
                jbrown

                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.

                • 5. Re: Count of Days Between Two Dates, Broken Down By Month
                  user19752

                  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.

                  • 6. Re: Count of Days Between Two Dates, Broken Down By Month
                    philmodjunk

                    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.

                    • 7. Re: Count of Days Between Two Dates, Broken Down By Month
                      jbrown

                      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.

                      • 8. Re: Count of Days Between Two Dates, Broken Down By Month
                        jbrown

                        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.

                        • 9. Re: Count of Days Between Two Dates, Broken Down By Month
                          philmodjunk

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

                          • 10. Re: Count of Days Between Two Dates, Broken Down By Month
                            fmpdude

                            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.

                            • 11. Re: Count of Days Between Two Dates, Broken Down By Month
                              philmodjunk

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

                              • 12. Re: Count of Days Between Two Dates, Broken Down By Month
                                electon

                                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.

                                 

                                For example:

                                 

                                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,

                                Thomas.

                                • 13. Re: Count of Days Between Two Dates, Broken Down By Month
                                  fmpdude

                                  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.