You are viewing limited content. For full access, please sign in.

Question

Question

Restrict weekend calculation when using tables for Date selection

Forms Version 10 How To

Updated October 3, 2017

asked on October 2, 2017

Hi All,

I have a Form that is being used for Leave applications. The users have the option to select the start date and the end date of their leave and the type as per below:

The Number of days in Total is a calculation based on the below:

=DAYS360( INDEX(Table1.Date11,ROW()) , INDEX(Table1.Date12,ROW()) )

This works perfectly but it is also adding Weekends as whole numbers. I was wondering if there is a way to not calculate Weekends in the total days leave?

I did try using this: https://answers.laserfiche.com/questions/94430/JavaScript-support-for-excluding-weekends#94436

But, not sure if I have the correct data in there as I am using a table? I am getting blank calculations.

Below is my Script:

$(document).ready(function () {
{
$(".sdate input").on('blur change', sumtotal);
   $('.cf-table-add-row').click(function () {
   $(".sdate input").on('blur change', sumtotal);
});
{
$(".edate input").on('blur change', sumtotal);
   $('.cf-table-add-row').click(function () {
   $(".edate input").on('blur change', sumtotal);
});

function sumtotal() {
var s = 0;
var e = 0;
//Get dates from input and reformat, then create date var
$('.sdate input').each(function () {
s1 = $(this).val().replace("-", " ");
s2 = s1.replace("-", " 20");
s = new Date(s2);
});
$('.edate input').each(function () {
e1 = $(this).val().replace("-", " ");
e2 = e1.replace("-", " 20");
e = new Date(e2);
});
var one_day=1000*60*60*24;
var startMillis = s.getTime();
var endMillis = e.getTime();
var totalDays = (endMillis - startMillis)/one_day;
var weekend = 0;
var days = 0;
for (var i = startMillis; i < endMillis; i += one_day) {
var currentDay = new Date(i);
if (currentDay.getDay() == 5 || currentDay.getDay() == 6) {
weekend++;
}
}
days = (totalDays+1) - weekend;
//Output value in the total class box
if (isNaN(days)) {
$('.total input').val('Select dates');
} else {
$('.total input').val(days);
}
if (e.getTime() < s.getTime()) {
$('.edate input').val($('.sdate input').val());
sumtotal();
}
}

Thank you in advance,

Ziad

0 0

Answer

SELECTED ANSWER

replied on October 2, 2017 • Show version history

function sumtotal() {
    $('#q23 tbody tr').each(function() { //Targeting table id qxx
  
        var s = 0;
        var e = 0;
        //Get dates from input and reformat, then create date var
        $(this).find('.sdate input').each(function () {
            s1 = $(this).val().split(/\//).reverse().join('/');
            s2 = s1.replace("/", "-");
            s = new Date(s2);
        });
        $(this).find('.edate input').each(function () {
            var e1 =  $(this).val().split(/\//).reverse().join('/');
            var e2 = e1.replace("/", "-");
            e = new Date(e2);
        });
      var one_day=1000*60*60*24;
      var startMillis = s.getTime();
      var endMillis = e.getTime();
      var totalDays = (endMillis - startMillis)/one_day;
      var weekend = 0;
      var days = 0;
      for (var i = startMillis; i < endMillis; i += one_day) {
        var currentDay = new Date(i);
        if (currentDay.getDay() == 5 || currentDay.getDay() == 6) {
          weekend++;
        }
      }
        days = (totalDays+1) - weekend;
            //Output value in the total class box
            if (isNaN(days)) {
                $(this).find('.total input').val('Select dates');
            } else {
                $(this).find('.total input').val(days);
            }
     
})
};

$(document).ready(function () {
  
    $(".sdate input").on('blur change', sumtotal);
 	 $(".edate input").on('blur change', sumtotal);
    $('.cf-table-add-row').click(function () {
    $(".sdate input").on('blur change', sumtotal);
      $(".edate input").on('blur change', sumtotal);
  });
});

Hi Ziad,

See above, it looks like you needed to target each row within the Table, so make sure you change the id field that the code is targeting just after the function is declared. Also, because we are dd/MM/yyyy the format needed to be changed to MM/dd/yyyy or yyyy-MM-dd for the calculations to work correctly. This is done through the modification of the value split and reverse.

Kind Regards,

Aaron

0 0

replied on October 3, 2017

Hi Aaron,

That worked perfectly, thank you,

Regards

Ziad

0 0

Sign in to reply to this post.

Asked October 2, 2017
Updated October 3, 2017
289 views