Home > Enterprise >  Get total elapsed business hours between two datetimes
Get total elapsed business hours between two datetimes

Time:01-01

I need to calculate the amount of vacation hours taken between two datetimes. Since this is a vacation (leave, PTO) application, it's important that I find the amount of elapsed vacation time taken between two datetimes.

Vacation time is measured by the number of hours taken off during the company business hours, which is between 9:00:00 and 18:00:00.

For example :

let start_at = '2021-12-27 14:00:00';
let end_at = '2021-12-28 14:00:00';

start_at - end_at results in 1 day. 24 hours.

The result I expect is 9 hours, not 1 day completely.

How can I count distinct intervals of business hours (e.g., 9 hour blocks) instead of the actual time range?

CodePudding user response:

First, ignore the hours and calculate the difference in days between the two dates (Ignoring holidays as you pointed out). 2021-12-25 -> 2021-12-29 = 4 days difference. Multiply that amount by 9 hours, 4 * 9 = 36 hours.

You can also do some more arithmetic with the minutes if needed: (14:25:00 start, 15:00:00 end, would mean you do 36 - (25/60))

Then when you want to show how many "days" the user has just divide back the hours by 9 and make sure to turn the decimals into minutes.

CodePudding user response:

This isn't as simple as getting the time between two Dates.

You need to resolve whether the start time is before, during or after business hours on the start date, then work out the business hours for that day.

Then work out the business hours for each whole day up to, but not including, the last day.

Then calculate the business hours for the last day similarly to the first day, except you want the hours before the end date not after.

The result will then be the first day hours intermediate hours last day hours expressed as days and hours.

The following is a fairly simple implementation based on business hours being 8 to 17 Mon to Fri in whole hours. The intention is not to provide a robust solution for the OP's requirements but to explain the complexity and issues involved.

// Return ISO week number as YYYY[W]WW, e.g. 2020W1
// https://stackoverflow.com/a/6117889/257182
function getWeekNumber(d) {
    d = new Date(Date.UTC(d.getFullYear(), d.getMonth(), d.getDate()));
    d.setUTCDate(d.getUTCDate()   4 - (d.getUTCDay()||7));
    var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
    var weekNo = Math.ceil((((d - yearStart) / 86400000)   1) / 7);
    return `${d.getUTCFullYear()}W${('' weekNo).padStart(2,'0')}`;
}

// Assume business days are Monday to Friday
// If start time is not 00:00:00, count from start of next day
// If end time is not 00:00:00, count to start of day
// Part days must be handled by caller
function getWholeBusinessDays(start, end) {
  // end must be >= start
  if (end < start) return;
  // Helper - set Sat or Sun to Mon
  let setToMon = d => d.setDate(d.getDate()   (d.getDay() == 6? 2 : 1));
  
  let s = new Date( start),
      e = new Date( end),
      days = 0;
  // If s doesn't start at midnight, set to end of day
  if (s.setHours(0,0,0,0) !=  start) s.setHours(24);
  // Set e to start of day
  e.setHours(0,0,0,0);
  // If s is on a weekend, set to Monday
  if (!(s.getDay() % 6)) setToMon(s);
  // If e is on a weekend, set to Monday
  if (!(e.getDay() % 6)) setToMon(e);
  
  // If s and e are same week, get days between them
  if (getWeekNumber(s) == getWeekNumber(e)) {
    return e.getDay() - s.getDay();
  // Otherwise get days from s to end of week  
  // days from start of week to e   5 * weeks between
  } else {
    days  = 6 - s.getDay()   e.getDay() - 1;
    weeks = (Math.floor((e - s) / (7 * 8.64e7)));
    // If s.weekday on or before e.weekday, have too many weeks
    if (s.getDay() <= e.getDay()) --weeks;
    // Add weeks to days
    days  = weeks * 5;
  }
  return days;
}

// Get leave as [days, hours] between two dates
// Business hours are 8 to 17 Mon to Fri
// Only whole hours considered
// If start or end day hours not zero, only business hours on that day are counted
// If end day is 00:00, counts as whole day (i.e. inclusive)
function getLeave(start, end) {
  // end must be >= start
  if (end < start) return;
  // Helper - set Sat or Sun to Mon
  let setToMon = d => d.setDate(d.getDate()   (d.getDay() == 6? 2 : 1));
  let setToDayStart = d => d.setHours(0,0,0,0);
  let setToDayEnd   = d => d.setHours(24,0,0,0);
  // Setup
  let s = new Date( start),
      e = new Date( end),
      hours = 0,
      days = 0;
  // Move weekends to start of Monday
  if (!(s.getDay() %6)) {
    setToMon(s);
    setToDayStart(s);
  }
  if (!(e.getDay() %6)) {
    setToMon(e);
    setToDayStart(e);
  }
  // If start not 00:00, get hours and set to next day
  if (s.getHours()) {
    if (s.getHours() < 8) {
      s.setHours(8);
    }
    if (s.getHours() < 17) {
      hours  = 17 - s.getHours();
    }
    // Accounted for start day hours so set to next day
    setToDayEnd(s);
  }
  // If end not 00:00, get hours and set to start of day
  if (e.getHours() > 17) {
    e.setHours(17);
  }
  if (e.getHours() > 8) {
    hours  = e.getHours() - 8;
  }
  setToDayStart(e);
  
  // If hours are more than 9, add excess to days
  if (hours > 8) {
    days  = Math.floor(hours / 9);
    hours = hours % 9;
  }

  // Add business days between dates
  days  = getWholeBusinessDays(s, e);
  
  // If original end was a business day and 00:00,
  // include it
  if (end.getDay() % 6 && end.getHours() == 0) {
      days;
  }
  
  return [days, hours];  
}

// Examples
[[new Date(2022,0, 3, 0), new Date(2022,0, 3, 0)], // Mon to Mon 1 day
 [new Date(2022,0, 3, 0), new Date(2022,0, 4,12)], // Mon to Tue noon 1 day, 4 hours
 [new Date(2022,0, 3, 0), new Date(2022,0, 4, 0)], // Mon to Tue 2 days
 [new Date(2022,0, 7, 0), new Date(2022,0,10, 0)], // Fri to Mon 2 days
 [new Date(2022,0, 7,12), new Date(2022,0,10,12)], // Fri noon to Mon noon
 [new Date(2022,0, 7,12), new Date(2022,0,17,12)], // Fri noon to Mon noon   1 week
 [new Date(2022,0, 4, 0), new Date(2022,0, 6, 0)], // Tue to Thu 3 days
 [new Date(2022,0, 6, 0), new Date(2022,0,18, 0)], // Thu to Tue   week 9 days
].forEach(([s, e]) => {
  let [days, hours] = getLeave(s, e); 
  console.log(`${s.toString().substr(0,21)} - ${e.toString().substr(0,21)} `  
              `${days} day${days == 1? '':'s'} ${hours} hour${hours == 1? '':'s'}`);
})

  • Related