Two Dates, Calculate Business Days

Two Dates, Calculate Business Days

Calculates the number of business days between date A and date B. Holidays must be set in advance. Company’s original holidays can be added. For example, this function can be used for calculating the number of business days for the month in advance and planning the work.

Auto Step icon
Configs for this Auto Step
conf_DataIdA
A: Select DATE DATA *
conf_DataIdB
B: Select DATE DATA *
conf_HolidayMasterFileName
C: Holiday Master File Name *
conf_IsShareHolidayMaster
D: Share Kind Of Holiday Master File (ON: Shared)
conf_DataIdE
E: Set Days of Week to be Closed (eg “0,Sun” )#{EL}
conf_DataIdF
F: Select DECIMAL DATA that stores Calced Date (update) *
Script (click to open)

main();
function main(){
//////// START "main()" /////////////////////////////////////////////////////////////////
//// == Config Retrieving / 工程コンフィグの参照 ==
const startDateObj          = configs.getObject("conf_DataIdA");
const endDateObj            = configs.getObject("conf_DataIdB");
const holidayMasterFileName = configs.get("conf_HolidayMasterFileName");
const strIsShare            = configs.get("conf_IsShareHolidayMaster");
const closedDaysConfigs     = configs.get("conf_DataIdE");
const daysCountObj          = configs.getObject("conf_DataIdF");

//// == Data Retrieving / ワークフローデータの参照 ==
const startDate  = engine.findData( startDateObj );
let   endDate    = engine.findData( endDateObj );
let   strEndDate = "";
//// == Calculating / 演算 ==
if ( startDate === null){
  engine.log("Start Date is null. Business Day Count is zero.");
  engine.setData( daysCountObj , new java.math.BigDecimal( 0 ) );
  return;
}
if ( endDate === null ){
  engine.log("End Date is null. Force Set Today to End Date.");
  let now = new Date();
  endDate = java.sql.Date.valueOf(now.getFullYear() + "-" + (now.getMonth()+1).toString().padStart(2, '0') + "-" + now.getDate().toString().padStart(2, '0'));
  engine.setData( endDateObj , endDate);
  now.setDate(now.getDate() + 1 );
  strEndDate = now.getFullYear() + "-" + (now.getMonth()+1).toString().padStart(2, '0') + "-" + now.getDate().toString().padStart(2, '0');
}else{
  strEndDate = toBpmDateStr( endDate.addDays(1) );
}
engine.log("strEndDate: " + strEndDate);
// Check starDate > endDate
const milliSecStartDate = toJsDate( toBpmDateStr( startDate ) ).getTime();
const milliSecEndDate = toJsDate( strEndDate ).getTime();  // include last day
const milliSecDuration = milliSecEndDate - milliSecStartDate;
if ( milliSecStartDate >= milliSecEndDate ){
  engine.log("Start Date >= End Date. Business Day Count is zero.");
  engine.setData( daysCountObj , new java.math.BigDecimal( 0 ) );
  return;
}

let excludeCount = 0;
let closedDays = new Array();
if (closedDaysConfigs !== null){
    closedDays = closedDaysConfigs.split("\n");
}
engine.log("closedDays lenght: " + closedDays.length);
const allDurationDaysCount =  milliSecDuration /1000 /60 /60 /24 ;
engine.log("All Duration Days: " + allDurationDaysCount);
// Chack Master Kind
let isShare = true;
if (strIsShare === "false"){
  isShare = false;
}
// Holiday Master Check
const holidayConfigList = itemDao.findAll( holidayMasterFileName , isShare);
for (let i=0; i < holidayConfigList.size(); i++){
  let config = holidayConfigList.get(i);
  let dateObjHoriday = toJsDate( config.getValue() );
  let milliSecHoridayDate = toJsDate( config.getValue() ).getTime();
  let horidayName = config.getDisplay();
  if (( milliSecStartDate <= milliSecHoridayDate )&&( milliSecEndDate >= milliSecHoridayDate )){
    engine.log("Exclude Holiday: " + config.getValue() + " " + horidayName);
    excludeCount++;
    // Avoid Duplication (Holiday and Weekly Closed Day)
    for (let j=0; j < closedDays.length; j++){
      let closedDay = closedDays[j].split(",");
      if (closedDay[0] === (dateObjHoriday.getDay() + "")){
        engine.log(config.getValue() + " " + horidayName + " " + closedDay[1]);
        excludeCount--;
        break;
      }
    }
  }
}
// "Days of Week to be Closed" Check
for (let i=0; i < allDurationDaysCount; i++){
  let dateObjTargetDate =  toJsDate( startDate.addDays(i) + "" );
  for (let j=0; j < closedDays.length; j++){
    let closedDay = closedDays[j].split(",");
    if (closedDay[0] === (dateObjTargetDate.getDay() + "")){
      engine.log("Exclude Close Days: " + (startDate.addDays(i) + ""));
      excludeCount++;
      break;
    }
  }
}
//// == Data Updating / ワークフローデータへの代入 ==
engine.log("Exclude Count: " + excludeCount);
engine.log("Business Days Count: " + (allDurationDaysCount - excludeCount));
engine.setData( daysCountObj , new java.math.BigDecimal(allDurationDaysCount - excludeCount) );
} //////// END "main()" /////////////////////////////////////////////////////////////////

function toJsDate( bpmsDateOrDatetimeStr ){
// BPMS Date:     "2020-04-01"  (subtype "Y/M" "M/D" "Y", not supported)
// BPMS Datetime: "2020-04-01 23:59"
let year       = 0;
let monthIndex = 0;
let day        = 0;
let hours      = 0;
let minutes    = 0;

//  The ECMA/JavaScript Date object has a large number of methods.
// "Date.parse" is danger (strongly discouraged)
// - new Date("2014-11-10") // Mon Nov 10 2014 09:00:00 GMT+0900 (JST)
// - new Date(2014, 10, 10) // Mon Nov 10 2014 00:00:00 GMT+0900 (JST)
let arrDatetime = bpmsDateOrDatetimeStr.split(" ");
if( arrDatetime.length === 1 ){
  let arrDateParts = arrDatetime[0].split("-");
  year       = parseInt(arrDateParts[0], 10);
  monthIndex = parseInt(arrDateParts[1], 10) - 1;
  day        = parseInt(arrDateParts[2], 10);
}
if( arrDatetime.length === 2 ){
  let arrDateParts = arrDatetime[0].split("-");
  let arrTimeParts = arrDatetime[1].split(":");
  year       = parseInt(arrDateParts[0], 10);
  monthIndex = parseInt(arrDateParts[1], 10) - 1;
  day        = parseInt(arrDateParts[2], 10);
  hours      = parseInt(arrTimeParts[0], 10);
  minutes    = parseInt(arrTimeParts[1], 10);
}
return new Date( year, monthIndex, day, hours, minutes );
}
/* toBpmDateStr()
 param : com.questetra.bpms.util.AddableDate
 return: string
*/
function toBpmDateStr(q_date){
    if(q_date !== null){
        return dateFormatter.format('yyyy-MM-dd', q_date.getFirstTimeInDate());
    }else{
        return '';
    }
}

Download

warning Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.
(Installing Addon Auto-Steps are available only on the Professional edition.)

Notes

  • Range for calculating the number of business days
    • Between “A:” Data Item (Date-type) and “B:” Data Item (Date-type). Include days (“A:”,”B:” day).
  • “D: Share Kind Of Holiday Master File (ON: Shared)”
    • “C:” Master file of Select-type Data Item [ON] App-shared Add-on file , [OFF] Add-on file
  • “A:” Data Item is Null
    • Number of business Days is stored as “0”.
  • When a national holiday and a regular holiday are on the same day:
    • Count as one day.

Capture

Appendix

  • “C:” Holiday Master File (example: Japanese Holiday)
<items>
<item value="2022-01-01" display="元日" />
<item value="2022-01-10" display="成人の日" />
<item value="2022-02-11" display="建国記念の日" />
<item value="2022-02-23" display="天皇誕生日" />
<item value="2022-03-21" display="春分の日" />
<item value="2022-04-29" display="昭和の日" />
<item value="2022-05-03" display="憲法記念日" />
<item value="2022-05-04" display="みどりの日" />
<item value="2022-05-05" display="こどもの日" />
<item value="2022-07-18" display="海の日" />
<item value="2022-08-11" display="山の日" />
<item value="2022-09-19" display="敬老の日" />
<item value="2022-09-23" display="秋分の日" />
<item value="2022-10-10" display="スポーツの日" />
<item value="2022-11-03" display="文化の日" />
<item value="2022-11-23" display="勤労感謝の日" />
<item value="2023-01-01" display="元日" />
<item value="2023-01-02" display="休日" />
<item value="2023-01-09" display="成人の日" />
<item value="2023-02-11" display="建国記念の日" />
<item value="2023-02-23" display="天皇誕生日" />
<item value="2023-03-21" display="春分の日" />
<item value="2023-04-29" display="昭和の日" />
<item value="2023-05-03" display="憲法記念日" />
<item value="2023-05-04" display="みどりの日" />
<item value="2023-05-05" display="こどもの日" />
<item value="2023-07-17" display="海の日" />
<item value="2023-08-11" display="山の日" />
<item value="2023-09-18" display="敬老の日" />
<item value="2023-09-23" display="秋分の日" />
<item value="2023-10-09" display="スポーツの日" />
<item value="2023-11-03" display="文化の日" />
<item value="2023-11-23" display="勤労感謝の日" />
</items>

See Also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: