Skip navigation
Currently Being Moderated

Calculate the number of week days between two dates

May 10, 2009 5:54 PM

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.

 

 
Replies
  • Currently Being Moderated
    May 10, 2009 9:21 PM   in reply to Jo_2005

    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

    Attachments:
     
    |
    Mark as:
  • Currently Being Moderated
    May 11, 2009 9:08 AM   in reply to Jo_2005

    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

     
    |
    Mark as:
  • Currently Being Moderated
    May 11, 2009 9:32 PM   in reply to Jo_2005

    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

    Attachments:
     
    |
    Mark as:
  • Currently Being Moderated
    Dec 26, 2011 10:43 PM   in reply to Steve L Walker

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 27, 2011 3:39 AM   in reply to shyamkg

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 27, 2011 4:38 AM   in reply to Niall O\'Donovan

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

     

    Wish you a great year ahead...

     

    Shyam

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points