Expand my Community achievements bar.

Don’t miss the AEM Skill Exchange in SF on Nov 14—hear from industry leaders, learn best practices, and enhance your AEM strategy with practical tips.
SOLVED

Annual Leave Form

Avatar

Level 2

Hi,

I have an annual form which i would like for it to calculate the working days between two dates. I have an error when i select the start date to be e.g. Thursday 31 October and end date Monday 4th Nov it only seems to caluculate 1 weekend day? I am using formcalc

I also have another issue as my table has buttons that allows for more lines to be added if more that one set of dates is to be added, how to i get the table to calculate the different date working days and then calculate the total days altogether?

Any help would be great

Thanks

1 Accepted Solution

Avatar

Correct answer by
Level 2

There's a problem with the calculate script where you're calculating the weekend days.

The problematic line is this one:

    var currentDateNum = Date2Num(xfa.resolveNode("#subform.#subformSet.#subform[1].#subform[2].Table_SF.Table1.row1.Start_Date1").formattedValue,"DD-MM-YYYY")

If you print out the value, you'll see that it is always set to zero.

Change it to the following to fix the issue:

    var currentDateNum = Date2Num(xfa.resolveNode("#subform.#subformSet.#subform[1].#subform[2].Table_SF.Table1.row1.Start_Date1"), "YYYY-MM-DD", "en_IE")

View solution in original post

15 Replies

Avatar

Level 6

Question 1:  this is javascript on mouseUp:

var dtStart = new Date(this.getField("StartDate").value);

var dtEnd = new Date(this.getField("EndDate").value)

var nDayStart = dtStart.getDay()

var nDayEnd = dtEnd.getDay()

 

var OneDay = 24 * 60 * 60 * 1000;

var TotalNumDays = (dtEnd.getTime() -  dtStart.getTime())/OneDay;

// Fix up for Starting on Saturday or sunday

if(nDayStart == 6)

 

{

nDayStart = 1;

  TotalNumDays - = 2;

}

else if(nDayStart == 0)

{

nDayStart = 1;

TotalNumDays - = 1;

 

}

// Fix up for ending on saturday or sunday

 

if(nDayEnd == 6)

{

  nDayEnd = 5;// Make it friday

TotalNumDays - = 1;

}   

else if(nDayEnd == 0)

 

{

   nDayEnd = 5;// Make it friday

   TotalNumDays - = 2;

}

var NumWeeks = Math.floor(TotalNumDays/7);

 

var extraDays = 1;

 

var DaysLeft = TotalNumDays -  NumWeeks * 7;

if(DaysLeft)

 

{

   var sepDays = nDayEnd -  nDayStart;

   if(sepDays > 0)

      extraDays += sepDays;

   else if(sepDays < 0)// Week end Split

      extraDays += sepDays + 6;

}

var totalWorkingDays = NumWeeks * 5 + extraDays;

this.getField("TotalWorkingDays").value = totalWorkingDays;

I have a sample I can email if interested.

Avatar

Level 2

Hi Paul,

yes please if you could send a sample through that would help.

Where do i place this formula, in the total days box? Will it still work as i normally use forcalc?

Mnay Thanks

Rhian

Avatar

Level 10

That's Acrobat JavaScript so it will need to be edited to work for LiveCycle.

Avatar

Level 2

Is anyone able to have a look at my form please?

I am still stuggling on the calculation for all the days to total up taken and plus it always shows the number that is in the first collumn of the table and not the number between the two dates.

Thanks

Avatar

Level 2

Post your form somewhere and it will be much easier to help you.  It's extremely difficult to debug a problem with only an explanation of what is occurring.

Avatar

Correct answer by
Level 2

There's a problem with the calculate script where you're calculating the weekend days.

The problematic line is this one:

    var currentDateNum = Date2Num(xfa.resolveNode("#subform.#subformSet.#subform[1].#subform[2].Table_SF.Table1.row1.Start_Date1").formattedValue,"DD-MM-YYYY")

If you print out the value, you'll see that it is always set to zero.

Change it to the following to fix the issue:

    var currentDateNum = Date2Num(xfa.resolveNode("#subform.#subformSet.#subform[1].#subform[2].Table_SF.Table1.row1.Start_Date1"), "YYYY-MM-DD", "en_IE")

Avatar

Level 2

Thanks Justin it works! Now for the other problem how can i get it to claculate each new line and total the working days?

Avatar

Level 2

Add this to the calculate event for your workdays field (I also cleaned up the code a bit):

var fromDate = Start_Date1

var toDate = End_Date1

if ( HasValue(fromDate) & HasValue(toDate) ) then

          var fromDateAsNum = Date2Num(fromDate, "YYYY-MM-DD", "en_IE")

          var toDateAsNum = Date2Num(toDate, "YYYY-MM-DD", "en_IE")

          if (toDateAsNum > fromDateAsNum) then

                    var TotalDays = toDateAsNum - fromDateAsNum + 1

                    var currentDateNum = fromDateAsNum

                    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

                    $ = TotalDays - dayCnt

          else

                    $ = ""

          endif

else

          $ = ""

endif

Avatar

Level 2

Thank you Justin!!!! I have been trying to sort this out for ages and didnt think it was possible!

Only one small thing now, what is the calculation for adding them all together in the totalwork days?

Amzing!!! Thank you!

Avatar

Level 2

its ok i have done it Thanks for all the help!

Avatar

Level 2

is there any way that if you only enter the strat date and the end date to be the same date it will give you 1 day as currently it doesnt show anything?

Thanks

Avatar

Level 2

To add them all together, put this in your calculate script for the total field:

  $ = Sum( row1[*].workdays )

Avatar

Level 2

The reason start and end date shows nothing is because of this line:

        if (toDateAsNum > fromDateAsNum) then

The calculation only runs if end date is greater than begin date.  Change the expression to >= to get the desired result.

Avatar

Level 2

Thank you Justin My form is complete! (hopefully)