15 Replies Latest reply: Aug 18, 2014 4:35 AM by BR001 RSS

    Calculate the number of week days between two dates

    Jo_2005 Community Member

      Hello,

       

      Can any one please help modify my formcalc script to calculate the number of weekdays between two date fields.  My script currently calculates the total number of days between two dates, including the weekends which need to be excluded from the total.

       

       

       

      if

      (HasValue(Start_Date1) & HasValue(End_Date1) ) then

      $

      = Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") - Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE") + 1

      else

      ""

      endif

       

      Any help will be most appreciated.

       

      Thank you.

       

       

        • 1. Re: Calculate the number of week days between two dates
          Steve L Walker techies

          Try this....

           

          // form1.page1.subform1.WeekEndDays::calculate - (FormCalc, client)

           

          if (HasValue(Start_Date1) & HasValue(End_Date1) ) then
              var totalDays = Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") - Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE") + 1
              var currentDateNum = Date2Num(Start_Date1.formattedValue,"YYYY-MM-DD")
              var dayCnt = 0
              for i=1 upTo totalDays do
                  var dayOfWeek = Num2Date(currentDateNum,"E")
                  if (dayOfWeek == 1 | dayOfWeek == 7) then
                      dayCnt = dayCnt + 1
                  endif
                  currentDateNum = currentDateNum + 1
              endfor
              $ = dayCnt
          else
              ""
          endif

           

          Steve

          • 2. Re: Calculate the number of week days between two dates
            Jo_2005 Community Member

            Dear Steve,

             

            Thank you very much for your reply.  I have placed the script into my calculation field called "days1".

             

            There is an error message when the pdf is previewed.

             

            I have attached the error message on a file for you to look at to see where I could be going wrong.

             

            eg. syntax error near token " on line 2, column 0.

             

            Thank you for your assistance.

            • 3. Re: Calculate the number of week days between two dates
              Steve L Walker techies

              Check...

               

              1) You stated you put the script on the calculate event of "days1". My sample mimics the variable names used in the original post, "Start_Date1" and "End_Date1". If the variable names for the start and end dates are different you will have to change the script to reflect those names.

               

              2) The Date2Num functions in the calculation of "totalDays" uses the date format "YYYY-MM-DD". If your date patterns differ from "YYYY-MM-DD" FormCalc will complain.

               

              Steve

              • 4. Re: Calculate the number of week days between two dates
                Jo_2005 Community Member

                Hello Steve,

                 

                I have changed the display and validation pattern in the fields of Start_Date1 and End_Date1 to YYYY-MM-DD and the scripting works a treat.

                 

                Thank you very much for all of your help Steve, it is very much appreciated.

                 

                 

                 

                 

                 

                 

                 

                 

                 

                • 5. Re: Calculate the number of week days between two dates
                  Jo_2005 Community Member

                  Hello Steve,

                   

                  I have one last question for you.

                   

                  I would like to change the date format from YYYY-MM-DD to DD-MM-YYYY.

                   

                  The Start_Date1 and End_Date1 pattern fields have been changed to the new format of DD-MM-YYYY.

                   

                  I have also revised the form calcs in Weekend Days and Total Days to the format of DD-MM-YYYY.

                   

                  The script will not calculate the number of days after changing the date format.

                   

                  Can you please advise what the problem could be?

                   

                  Thank you once again, Steve.

                   

                   

                   

                   

                  • 6. Re: Calculate the number of week days between two dates
                    Steve L Walker techies

                    The attached now displays the start and end dates as DD-MM-YYYY. I added script to validate the end date is greater than the start date, also.

                     

                    Steve

                    • 7. Re: Calculate the number of week days between two dates
                      Jo_2005 Community Member

                      Hello Steve,

                       

                      I have adapted the changes to the script in my form and the number of working days are now calculating between the two dates.

                       

                      Thank you Steve, you are an absolute legend.

                       

                       

                      • 8. Re: Calculate the number of week days between two dates
                        Jo_2005 Community Member

                          Hello Steve,

                         

                        I have revised the script to take into account that if the start date and end date are the same that the total of days will = 1.

                         

                        Before being revised the script would not calculate the number of days if start and end date were the same.

                         

                        The revised script is posted as below for reference:

                         

                         

                         

                         

                         

                         

                        // Weekdays field *This will subtract the total days - the weekend days to give the total number of weekdays between two dates*

                         

                        if (HasValue(Start_Date1) & HasValue(End_Date1) ) then

                         

                               if (Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") >= Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE")) then

                        $ = TotalDays1 - WeekEndDays1

                         

                         

                         

                         

                        else

                        ""

                         

                         

                        endif

                        else

                        ""

                        endif

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                         

                        // Weekend Field *this will calculate the number of weekend days between two dates*

                        if

                        (HasValue(Start_Date1) & HasValue(End_Date1) ) then

                         

                         

                         

                        if (Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") >= Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE")) then

                        var totalDays = Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") - Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE") + 1

                         

                         

                        var currentDateNum = Date2Num(Start_Date1.formattedValue, "DD-MM-YYYY")

                         

                         

                        var dayCnt = 0

                         

                         

                        for i=1 upTo totalDays do

                         

                         

                         

                        var dayOfWeek = Num2Date(currentDateNum, "E")

                         

                         

                        if (dayOfWeek == 1 | dayOfWeek == 7) then

                        dayCnt

                        = dayCnt + 1

                         

                         

                        endif

                        currentDateNum

                        = currentDateNum + 1

                         

                         

                        endfor

                        $ =

                        dayCnt

                         

                         

                        else

                        ""

                         

                         

                         

                        endif

                        else

                        ""

                        endif

                         

                         

                         

                         

                         

                         

                        // Total Days Field - *This will calculate the total number of days including weekend days*

                        if

                        (HasValue(Start_Date1) & HasValue(End_Date1) ) then

                         

                         

                        if (Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") >= Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE")) then

                        $

                        = Date2Num(End_Date1, "YYYY-MM-DD", "en_IE") - Date2Num(Start_Date1, "YYYY-MM-DD", "en_IE") + 1

                         

                         

                        else

                        ""

                         

                         

                        endif

                        else

                        ""

                        endif

                         

                         

                        • 9. Re: Calculate the number of week days between two dates
                          shyamkg Community Member

                          Sir,

                           

                          Belated Merry Christmas...

                           

                          Just today (27-12-2011) I have come across your solution for calculating the number of weekdays between two dates. It is working perfect.

                           

                          But I have a small problem. I am residing in Middle East (Dubai - U.A.E). Here the weekends are Friday and Saturday.

                          If I want to know the number of weekdays between 13-Jan-2012 and 20-Jan-2012, your solution will show 6 days, whereas actual is only 5.

                           

                          How can I modify the script to consider the weekends as Friday and Saturday. Your kind reply would be highly appreciated.

                           

                          Regards

                           

                          Shyam Kumar

                          shyamkg@hotmail.com

                          • 10. Re: Calculate the number of week days between two dates
                            Niall O'Donovan Community Member

                            Hi,

                             

                            If you have a look at the script, you will see an if statement that is testing the day of the week. Sunday=1 and Saturday=7. You just need to change the 1 to a 6.

                             

                            if (dayOfWeek == 6 | dayOfWeek == 7) then

                               dayCnt = dayCnt + 1

                            endif

                             

                            Good luck,

                             

                            Niall

                            • 11. Re: Calculate the number of week days between two dates
                              shyamkg Community Member

                              Great !! Niall, it worked..Thanks..

                               

                              Wish you a great year ahead...

                               

                              Shyam

                              • 12. Re: Calculate the number of week days between two dates
                                Shekh1991 Community Member

                                HI Niall/Steve,

                                 

                                Greetings!!

                                 

                                I need a help in calculating no of days which excludes weekend days.

                                My requirement is that I will have the current date to which I need to add 5 days which excludes weekend days. I need the date to be populated in one of the date fields in the form

                                 

                                for eg: current date is 14/08/2014 and I need the output to be populated as 20/08/2014 Which excludes sat and Sunday.

                                Could you please let me know how to achieve this.

                                 

                                Many Thanks!

                                • 13. Re: Calculate the number of week days between two dates
                                  BR001 MVP

                                  Hi,

                                   

                                  Try this script, which is JavaScript not FormCalc like those above.

                                   

                                   

                                  var Days = { Sunday : 0, Monday : 1, Tuesday : 2, Wednesday : 3, Thursday : 4, Friday : 5, Saturday : 6 }; 

                                  function addWorkDays(date, days, weekend)

                                  {

                                     if (weekend === undefined) weekend = [Days.Saturday, Days.Sunday];

                                     for (var result = date; days > 1; result.setDate(result.getDate()+1))

                                     {

                                         if (weekend.indexOf(result.getDay()) < 0)

                                         {

                                             days--;

                                         }

                                     }

                                     return result;

                                  }

                                  console.println(addWorkDays(new Date(2014, 07, 14), 5))

                                   

                                   

                                  Regards

                                   

                                  Bruce

                                  • 14. Re: Calculate the number of week days between two dates
                                    Shekh1991 Community Member

                                    Thank you Bruce for the reply. I will try your solution given above.

                                     

                                    Just wanted to check if the same can be achieved in process workflow Using a Set value or execute operation.

                                     

                                    Any inputs highly appreciated!

                                     

                                    Thanks.

                                    • 15. Re: Calculate the number of week days between two dates
                                      BR001 MVP

                                      Hi,

                                       

                                      Can help you with process workflow, it's not something I've used, but this is standard JavaScript so would be surprised if it didn't.

                                       

                                      Regards

                                       

                                      Bruce