1 2 Previous Next 28 Replies Latest reply on Apr 27, 2017 8:59 PM by philmodjunk

    Working Day Calculation

    sam0723

      Hi i am new to filemaker.

       

      Recently, i am working on date calculation.

       

      if i would like to get the target date, which is target date = starting date + two working day.

       

      so how could i calculate the target date? starting date could be chosen by user.

       

      I am using Filemaker 15. Thank you.

        • 1. Re: Working Day Calculation
          fmpdude

          Calculating working days can be involved.

           

          Are weekends working days?  In many environments people work over the weekends.


          What about holidays? Which holidays? There are many, many holidays. Looking at holidays on the web, it's easy to find over 100 different holidays you might need to consider.

           

          For me, computing workdays was involved enough where I did it outside FileMaker and then used FileMaker to get that future date (or number of days).  Unfortunately, FileMaker's date functions are very rudimentary (sadly lacking) and, for me at least, writing code like this is in the "Script Workspace" would be extremely frustrating also.

           

          Regardless of your approach, however, I would first DEFINE what you mean by a "workday". Consider creating a table in FMP with all the holidays. Then write code (a script) using "weekends", if applicable, and your holidays from the holiday table to get two days from now.

           

          My program says that there are 19 workdays between 3/15/17 and 4/30/17 considering weekends and the holidays I am tracking. Your program would be different, probably.

          • 2. Re: Working Day Calculation
            sam0723

            Thank you. Working day means Monday to Friday excluding weekends and holidays. For holidays, it should be holiday of my city. Could i take the data from the web as it may change annually. If i create a holiday table, i may need to edit the table annually.

            • 3. Re: Working Day Calculation
              Menno

              You can calculate the next date after x working days with the next formula

              Let ( [

              // The number of weekdays to add (should be an integer):

                   ad = Int ( $number_of_workingdays_to_add ) ;

               

               

              // The date you start to calculate from:

                   dd = $starting_date ;

               

               

              // The day of the week from 1...7 where 1 = sunday:

                   dy = DayOfWeek ( dd ) ;

               

               

              // Number of times the addition can be devided by 5 (the length of a "working-week") . This must be multiplied by 7:

                   dv = Div ( ad ; 5 ) ;

               

               

              // Result of the startdate plus the calculated full weeks:

                   fw = dd + dv * 7 ;

               

               

              // Restvalue of the addition when the max-div-value is substracted:

                   md = Mod ( ad ; 5 ) ;

               

               

              // Correction in case the startdate is in a weekend:

                   c1 = Case ( dy = 1 ; 1 ; dy = 7 ; 2 ; 0 ) ;

               

               

              // Correction in case enddate would fall into a weekend:

                   c2 = Choose ( c1 + dy + md - 7 ; 2 ; 2 ; 2 )

              ] ;

                   fw + md + c1 + c2

              )

              It calculates the days added to the starting date for example today is Thursday april 20th + 3 working days results in april 25th.

               

              This formula does not take any holidays into the equation. So you need to check that against a table containing the holidays for the period you are calculating for. There are lots of ways to do that, but I would use an array to check which can be made with a CF:

              ## Format:  Date_Array ( StartDate ; WorkingDays ; Result )

               

              Let ( [

                   dow = DayOfWeek ( StartDate ) ;

                   theList = List ( Result ; If ( dow ≠ 1 and dow ≠ 7 and WorkingDays > 0 ; GetAsText ( StartDate ) ) ) ;

                   WDs = Workingdays - If ( dow ≠ 1 and dow ≠ 7 ; 1 )

              ] ;

                   Case ( WorkingDays > 0 ; Date_Array ( StartDate + 1 ; WDs ; theList ) ; theList )

              )

              The startdate in this formula should be the first valid date in the range you calculate. So in the previous example that date should be tomorrow: april 21st

              Date_Array ( Date ( 4 ; 21 ; 2017 ) ; 3 ; "" )

              and the result may look like (depending on system-settings):

              21-04-2017

              24-04-2017

              25-04-2017

               

              or

               

              4/21/2017

              4/24/2017

              4/25/2017

              And you could compare this to your holiday-table. If there are any overlaps, count them and add that number to the last date of the previous array of dates in the first formula (you should check this addition to your dates as well against the holiday-table)

               

              Hope this helps :-)

              2 of 2 people found this helpful
              • 4. Re: Working Day Calculation
                fmpdude

                Exactly. That's why you would want a holiday table. And, if that holiday table varies by year at all, make sure to add a date field to that holiday table so you're using the correct set of holidays.

                 

                Make sure you have FMP Advanced since this kind of date logic can be tough to get EXACTLY right under all day, holiday, and weekend situations. You'll almost assuredly need to step through your script (I would never, EVER, use an FMP calculation for this type of complex computation I would need to debug) and see why your date in the future isn't what you expect (unless you get it right the first time).

                 

                Just have patience and the debugger and coffee.

                 

                Test various situations looking at both your list of holidays and a calendar. Make sure your program always gets the right days under various start date plus days conditions.

                • 5. Re: Working Day Calculation
                  Menno

                  Well that calculations for Easter, Pentecost, Christmas and Newyearsday probably will never change. But holidays like the dutch "Kingsday" which is april 27th will definitely change when his daughter Amalia comes to reign.

                   

                  So I absolutely agree with you there, that a holiday-table is the way to approach this.

                   

                  As for your other remarks, I agree with you there too, it is a question structured and precise working and logic thinking. After which you should test and test and test and test and test until you're out of coffee ;-)

                  • 6. Re: Working Day Calculation
                    fmpdude

                     

                     

                    Yep!

                     

                    When I wrote this logic a year or so ago to compute the number of workdays between two dates (and today, after your posting, I added a new routine to compute the future date from a start date and a number of workdays), I spent lots of time on it. It's funny sometimes what the actual error turns out to be.

                     

                    Bottom Line: Dates can be a royal pain in the a**. Lots of special cases to consider.

                     

                    In my case, I have the holidays in a CSV file I downloaded from the Internet. I load those dates into a memory structure and do my logic that way (outside FMP). Then I use FMP to call my logic and pass back the date computed, or the number of days between two dates.

                     

                    But, in your case, using a table instead of a CSV in FMP doesn't change what you're doing only how you do it. A little.

                     

                    Like most coding, it's cool code to have working.

                     

                    Thanks for writing back.

                     

                    Enjoy your coffee.

                    • 7. Re: Working Day Calculation
                      sam0723

                      Thank you. Could you please explain more in details of the array, i still don't understand how to handle the holiday.

                      • 8. Re: Working Day Calculation
                        Menno

                        FileMaker can handle multikey relations. So if you have a textfield like the array of dates above, you can use this field to have a relation from 1 record to many others whom are unique. So if you have a table of records with just the date of each individual holiday, you can have a working relation to that table from a field with multiple values stacked in it.

                         

                        Have a look in the attached file to see how that works.

                         

                        For some reason FileMaker knows that the stack of dates are dates and the relation from a text-field to a date-field works just fine (don't see that in any other database-engine!).

                        3 of 3 people found this helpful
                        • 9. Re: Working Day Calculation
                          sam0723

                          Thank you. i would like to ask a question. Now i am using script to correct the working date. The holiday table should be updated annually as the date of some holidays may be changed so my script have to be changed annually. Is there any other good method to prevent this issue?

                          • 10. Re: Working Day Calculation
                            philmodjunk

                            In a similar situation, I set up a layout that presented dates in a simple calendar format with date fields, buttons, and scripts to make it easy to manage holidays, I found it simpler and more flexible to treat weekend dates as just more holidays in the same table.

                            I could then use a relationship to just the "open dates" and getNthRecord to get a date N working days from today.

                            • 11. Re: Working Day Calculation
                              Menno

                              If you've set your script(s) up generically, you would only have to keep the holiday-table up to date. The dates of lots of holiday can be found in lots of places on the internet. What would you need to change in your script every year?

                              • 12. Re: Working Day Calculation
                                Menno

                                Yes I agree, some businesses have a working-week form Tuesday until Saturday (like in retail) and then the approach with just a table with the non-workingdays, including the weekends and holidays, is much easier to work with.

                                • 13. Re: Working Day Calculation
                                  sam0723

                                  some of the holidays may fall on Sunday and the government will make another day as that holiday so the date may be changed annually.

                                  • 14. Re: Working Day Calculation
                                    Menno

                                    Yes I understand, but it seems to me that you check for a certain holiday and then see if the date you have assigned to it matches. That would be the wrong way around and would mean that you would have to change the records every year, plus you can check for that holiday only once and only for that particular one year.

                                     

                                    What you should do is have a table with dates, only for the dates which have holidays assigned. So for easter-monday  for 2017, 2018, 2019 , 2020 you would have 4 entries with the dates :

                                    16-4-2017

                                    1-4-2018

                                    21-4-2019

                                    12-4-2020

                                    and each them named easter-monday, but still a unique record, because the date is. Afterwards you can add records for holidays as you need them or even remove them if that particular date is not a valid holiday anymore. Passed dates can be left in the DB so that there is a history available.

                                    1 2 Previous Next