Skip to main content
Participant
February 2, 2023
Question

Insert numbers of days in a field and calculate next date excluding Weekend (Saturday, Sunday)

  • February 2, 2023
  • 2 replies
  • 498 views

Hello!

Can someone help fix this script?

How it’s supposed to be:

  1. I have a "Date field" which is a manually selected date from a drop-down tab and is labeled “1st Meeting” with this format: yyyy/mm/dd
  2. The next date which is labeled as “2nd Meeting” should auto-populate and be 3 business days from the “1st Meeting.” (Skipping every weekend day aka Saturday and Sunday).
  3. Examples:
    • If 1st Meet was Tue 2023/01/03, then 2nd Meet should = 2023/01/06
    • If 1st Meet was Wed 2023/01/04, then 2nd Meet should = 2023/01/09(skip Sat & Sun)
    • If 1st Meet was Thur 2023/01/05, then 2nd Meet should = 2023/01/10(skip Sat & Sun)
    • If 1st Meet was Fri 2023/01/06, then 2nd Meet should = 2023/01/11(skip Sat & Sun)

 

ISSUES/ How it is:

Below is the script/code that is currently in use. The issue with this script/code is that it stops 3 business days after Wednesday. For 1st meetings on Thursdays or Fridays, it chooses Monday as the 2nd meeting date.

  1. Examples of Issues:
    • 1st Meet was Tue 2023/01/03.  2nd Meet = Fri 2023/01/06(Correct date)
    • 1st Meet was Wed 2023/01/04. 2nd Meet = Mon 2023/01/09(Correct date)

 

  • 1st Meet was Thur 2023/01/05, 2nd Meet showed Mon 2023/01/09 (WRONG DATE!!)
  • 1st Meet was Fri 2023/01/06, 2nd Meet showed Mon 2023/01/09 (WRONG DATE!!)

 

SCRIPT BELOW:

 

// Custom Calculate script for NewDate field

(function () {

 

    // Get date entered into the OrigianlDate field

    var sDate = getField("1st Meeting").valueAsString;

 

    // Convert string to date

    var d = util.scand("yyyy/mm/dd", sDate);

 

    // Add days to date

    d.setDate(d.getDate() + 3);

 

   // possible adjustment for weekend

   var nAdj = 0;

 

    // get the zero based day of the week starting on Sunday

    var nDay = d.getDay();

 

    // test for Sunday or a value  day value of 0

     if(nDay == 0) nAdj = 1;

 

    // test for Saturday or a day value of 6

    if(nDay == 6) nAdj = 2;

 

   // adjust date

   d.setDate(d.getDate() + nAdj);  

 

// adjust computed date by needed bump

    if (sDate) {

        event.value = util.printd("yyyymmdd", d);

    } else {

        event.value = "";

    }

 

})();

This topic has been closed for replies.

2 replies

Nesa Nurani
Community Expert
Community Expert
February 2, 2023

Try this script instead:

var sDate = this.getField("1st Meeting").valueAsString;
var d = util.scand("yyyy/mm/dd", sDate);
if(!sDate)
event.value = "";
else{
var nDay = d.getDay();
if(nDay == 0 || nDay==1 || nDay==2)
d.setDate(d.getDate() +3);
else if(nDay==3 || nDay==5 || nDay==4)
d.setDate(d.getDate() +5);
else if(nDay == 6)
d.setDate(d.getDate() +4);
event.value = util.printd("yyyy/mm/dd", d);}
Participant
May 10, 2023

Hello! I have a similar situation of 2 date fields where a date is selected from field 1 "Date 1_af_date" and I need field 2 "Date 2_af_date" to calculate 5 working days out (minus Saturdays and Sundays). I tried the following script as you supplied above (thank you), but unfortunately it did not work for me. I am using the format mm/dd/yyyy, and below is what I entered in the custom validation script field - under Date 1_af_date:

 

var sDate = this.getField("Date 2_af_date").valueAsString;
var d = util.scand("mm/dd/yyyy", sDate);
if(!sDate)
event.value = "";
else{
var nDay = d.getDay();
if(nDay == 0 || nDay==1 || nDay==2)
d.setDate(d.getDate() +3);
else if(nDay==3 || nDay==5 || nDay==4)
d.setDate(d.getDate() +5);
else if(nDay == 6)
d.setDate(d.getDate() +4);
event.value = util.printd("mm/dd/yyyy", d);}

 

Any assistance is greatly appreciated.

 

Bernd Alheit
Community Expert
Community Expert
May 10, 2023

Use the script at calculation of field 2 and use the date from field 1..

Bernd Alheit
Community Expert
Community Expert
February 2, 2023

Check also the day of the week for the 1st meeting.