Expand my Community achievements bar.

Guidelines for the Responsible Use of Generative AI in the Experience Cloud Community.
SOLVED

Calculate the number of week days between two dates

Avatar

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.

1 Accepted Solution

Avatar

Correct answer by
Former Community Member

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

View solution in original post

16 Replies

Avatar

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

Avatar

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.

Avatar

Correct answer by
Former Community Member

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

Avatar

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.

Avatar

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.

Avatar

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

Avatar

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.

Avatar

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

 

Avatar

Level 1

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

Avatar

Level 10

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

Avatar

Level 1

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

Wish you a great year ahead...

Shyam

Avatar

Level 2

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!

Avatar

Level 10

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

Avatar

Level 2

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.

Avatar

Level 10

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

Avatar

Level 2

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.

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----