Calculate the number of week days between two dates

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

[Former Community Member]

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

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

[Former Community Member · Accepted solution]

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

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

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

[Former Community Member]

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

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

[Former 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

[shyamkg]

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

[Niall_O_Donovan]

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

[shyamkg]

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

Wish you a great year ahead...

Shyam

[Shekh1991]

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!

[_Bruce_Robertson]

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

[Shekh1991]

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.

[_Bruce_Robertson]

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

[abbasm79416867]

Hi, I need a code for calculation like this:

I have three fields like this:

"Date field", "Numbers of Day"s and "Calculate Date fields"

I like to calculate like this:    Date field + Numbers of Days = Calculate Date (Excluding Weekend days)

I need javascript for this calculation.