1 2 3 Previous Next 41 Replies Latest reply on Sep 13, 2016 10:09 AM by realgrouchy

    Calculating number of working days between two dates

    Abingdon

      Hi All,

       

      I need to calculate the number of working days between two given dates.  For simplicity's sake I am assuming that working days are always Monday to Friday, ignoring weekends and any national holidays etc.

       

      Basically, I want to enter a date in Date1 and a date in Date2 and have the result return the number of working days, including the two dates I entered.

       

      Help much appreciated as always.

        • 3. Re: Calculating number of working days between two dates

          I looked at the referenced Web sites listed below your posting and one person posted that the custom function doesn't work consistently or sometimes produce anything. (I haven't tried that CF myself.)

           

          Here's the deal: Date Math is a (can be a) Royal Pain.

           

          And, IMHO, FM's date functions leave a lot to be desired.

           

          But in this case, "workdays", there is almost no environment that I know of that has a way to immediately calculate it. Taking holidays and leap years into account only complicates this endeavor.

           

          Therefore, regardless of whether you use FMP or some other environment for this workday calculation, you either need to use a custom function like the one mentioned (test test test, first) or create your own.

           

          I would probably do this workday calculation in a loop and if each day is between MONDAY and FRIDAY inclusive, I'd increment the workday counter. Again, holidays and Leap years need to be considered, but those are relatively easy to include in the workday calculation logic.

           

          Additionally, with certain industries, like healthcare - EVERY day is a workday, but this doesn't apply to your case.

           

          Good luck.

           

          - m

           

          P.S. Please post back with your testing results of the custom function noted in this thread.

          • 4. Re: Calculating number of working days between two dates
            Abingdon

            Thank you everyone.

             

            I will take a look at the CFs this weekend and post what I get and anything I might add to it.

             

            Thanks again.

            • 5. Re: Calculating number of working days between two dates
              beverly

              Agreed! and what about the places where Sat. & Sun. are NOT the weekends (other days off in a week)? or the professions (firefighters) where the work days are 3 on, 2 off (or whatever they use)? "days off" (whether holidays, weekends or ??) certainly can throw a monkey wrench into the works.

               

              These may need a scripted loop.

              beverly

              • 6. Re: Calculating number of working days between two dates
                Extensitech

                I agree wholeheartedly, and from experience, that "working days" is a pretty flexible concept. Sometimes the best way is to offer an administrator preference fields where they can check off the days of the week they work (either system-wide, or per employee) and also just create a list of upcoming holiday days.

                 

                I've done a recursive cf that worked for a banking customer, where they had rules like, "If Christmas falls on a Tuesday, we get the day before that off, too." (Going from memory. Unfortunately, I don't have ready access to that solution.)

                 

                I disagree that it would necessarily have to be a scripted loop, though. The advantage of a cf here is that you can use it for a calculated field, auto-entry calculation, conditional formatting, hide when, and so forth. You wouldn't want a bunch of these unstored and evaluating all at once (i.e. in a list view), but while I can certainly imagine writing this as a script, I cringe at the thought of getting it to properly trigger at all the right times and all the right places.

                 

                Just my humble opinion, though. I tend to pull the cf tool out of my toolbox sooner than most.

                 

                Chris Cain

                Extensitech

                • 7. Re: Calculating number of working days between two dates
                  Vaughan

                  Danielle's CF above allows a list of "holidays" to be excluded from the calc, so it gets pretty close. There may be some assembly required but that's usually the case.

                   

                  I posted a CF to Brian Dunnings site some time ago that generates public holidays. It's Australia-centric but can easily be customised. It handles Christmas, New Year's Day and Easter which should be global. It's not exhaustive for public holidays but it does not try to be.

                   

                  Let's not repeat the error of 100% rejecting a solution because it only solves 95% of the problem. For most business, business days are M-F excluding public holidays.

                  • 8. Re: Calculating number of working days between two dates
                    Abingdon

                    Hi and thanks to all of you.

                     

                    Am replying to Vaughan as I also believe that suggestions posted here are not necessarily the end result but the spark for some creative ideas.

                     

                    I used Danielle's CF as a starting point and as far as I can see it does do what is says on the tin.

                     

                    This is the starting point for what I have been asked to do by a client.  It will be a horribly complex and painful!  But if you are one-nil down, then you need to score the first goal before you can go on to score the winner!

                     

                    Thanks again.  I hope the attached file is helpful.

                    • 9. Re: Calculating number of working days between two dates

                      Here's some "workdays" Java code I found looking around the Internet -- see below (I have not yet tested).

                       

                      Calculating Work Days In Java · GitHub

                       

                      To make it more useful, you'd need to add another method call inside the IF statement in the do...while loop below to check for holidays, but this is the general idea. Very easy and compact.

                       

                      There are other versions of this computation that don't require any loops but just do date math, but the holiday stuff would need to be integrated into those too. I'm not sure if FM's date functions could do that date math.

                       

                      When trying to do something like this in FM, I'm amazed that Custom Functions are really more like Custom Calculations are therefore limited from a programming standpoint. A script might be a better way to go to do this in FMP.

                       

                      Actually, I would probably just add this class to an exiting Ajax server and then call it from FMP passing the two dates. That solution would require two steps and possibly an Internet connection (flexible, but point of failure), if not local. Doing it all in FMP would be more tedious but will be self contained. Pluses and minuses but choices!

                       

                      Enjoy!

                       

                      - m

                       

                      P.S. The way this "function" is written is as a "static" class, which means it doesn't require an instance so you can just call it directly like any non-OO "function".

                       

                      -----------------

                       

                      public static int calculateDuration(Date startDate, Date endDate)

                      {

                        Calendar startCal = Calendar.getInstance();

                        startCal.setTime(startDate);

                       

                        Calendar endCal = Calendar.getInstance();

                        endCal.setTime(endDate);

                       

                        int workDays = 0;

                       

                        if (startCal.getTimeInMillis() > endCal.getTimeInMillis())

                        {

                          startCal.setTime(endDate);

                          endCal.setTime(startDate);

                        }

                       

                        do

                        {

                          startCal.add(Calendar.DAY_OF_MONTH, 1);

                          if (startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SATURDAY && startCal.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY)

                       

                        // need to add holiday logic in if statement

                       

                          {

                            workDays++;

                          }

                        }

                        while (startCal.getTimeInMillis() <= endCal.getTimeInMillis());

                       

                        return workDays;

                      }

                      • 10. Re: Calculating number of working days between two dates
                        Abingdon

                        Thank you Morkus.

                         

                        Much appreciated!

                        • 11. Re: Calculating number of working days between two dates

                          Yeah, well, I know it's not FileMaker code, but at least it's good "pseudo code" for how you might do it in FileMaker.

                           

                          The bottom line, I guess is that there is no built-in function for workdays I could find. JavaScript has a similar function to the one above on the web too.

                           

                          Searching for "Hoildays", the first site that came up was this one:

                           

                          http://www.timeanddate.com/holidays/us/

                           

                          Which has over 200 possible holidays. Probably just the "Federal Holidays" is sufficient, but with this list, you could build a data driven interface (holidays to be set/updated/changed by an administrator) to have total flexibility over just about any holiday (or add new ones!).

                           

                          I attached the CSV of the Holidays from the link above (to import this CSV into FileMaker just drag the CSV to the FileMaker Icon and it imports in one step....nice!).

                           

                          Good luck.

                           

                          - m

                          • 12. Re: Calculating number of working days between two dates
                            mlkyazilim

                            Hi,

                             

                            You can try the below formula

                             

                            Case( (DayOfWeek ( DateStart)=1 and DayOfWeek ( DateFinish )=1) or (DayOfWeek ( DateStart)=7 and DayOfWeek ( DateFinish )=7);

                            DateFinish - DateStart  - (Ceiling ( (DateFinish - DateStart) / 7 )) *  2;

                            DateFinish - DateStart  - (Ceiling ( (DateFinish - DateStart) / 7 )) *  2+1)


                            Best Regards


                            Mehmet Cabi

                            • 13. Re: Calculating number of working days between two dates
                              siplus

                              Hi mate.

                               

                              There is no true formula that will satisfy you. As you might already know, I'm from Switzerland. Even in a small country like ours, we have an explosion of holiday situations, as you can easily see here.

                               

                              What do I do ? Simple stuff, already explained on this forum. I create a database with days. 11k records = 30 years from now.

                               

                              In every record I can calc a 1 if it's sunday, I can calc the same for saturdays, and I can have a field "local holiday" which can be set at 1 by users. These fields can be summed together into a "Hday" field.

                               

                              From any database in your system, you set up a relationship with 2 dates towards this auxiliary table, and get the sum of Hday. This will solve your problem, individually, for the next 30 years, provided you set the local holiday field to 1 whenever you know the year's official holidays.

                               

                              xSQL ("Select sum (Hday) from Days where day BETWEEN ? AND  ?";"";""; begDay; endDay)

                              • 14. Re: Calculating number of working days between two dates

                                Siplus -- I did find an Oracle SQL that supposedly would calculate working days, but it's complicated and not applicable here anyway.

                                 

                                ---------------

                                 

                                So, I just coded this calculation up in Java using the holiday list I found and posted on this thread.

                                 

                                (over 200 holidays, most could be removed, but it was amazing how many "holidays" there were so I just posted the list.)

                                 

                                http://www.timeanddate.com/holidays/us/

                                 

                                Using Java is both easier and more complicated than FileMaker in various aspects. On the one hand, unless you connect to a database in Java, you need to simulate a "lookup" (for the holiday) of some type. Dates are always time consuming regardless of the environment. However, coding in a Java IDE is much simpler and quicker than FMP.

                                 

                                In FileMaker, though, you have the database handy and readily accessible so that's a VERY good thing. The script you'd write would tend to be a lot slower than a byte-code complied language (and only work in that FMP application), but that's probably OK too.

                                 

                                -----

                                 

                                Testing...

                                So, using the two dates December 24, 2015 and "today" (January 31, 2016), my program gives me these many working days between those dates (Saturday and Sunday excluded and holidays from that huge holiday list.)

                                 

                                Number of workdays between dates: 16

                                 

                                (Again, you wouldn't want all those holidays in the lookup, like Groundhog day to be included, but I left all of the holidays in there for testing.)

                                 

                                Look forward to hear how this goes for you in FileMaker! 

                                 

                                - m

                                1 2 3 Previous Next