Applying business hours to custom reports

Friday, March 23, 2018
Avatar

I have created a custom report to display all overdue tasks, and the number of days by which each task is overdue. However, I have only found a way to calculate that number using all days since the overdue date (ex. diffDays(CurrentDateTime(), R.End_Date) . In other words that calculation includes weekends. So if a task is one week overdue, I would like the report to indicate that the task is 5 days overdue, not 7 days. Does anyone know of a way to exclude non-business days from such a calculation?

 

2 Replies
Monday, April 2, 2018
Avatar
re: tdobbelaere Friday, March 23, 2018

Hi Trudy,

Currently the Release includes the number of working / non-working days and the project specifies the number of standard working days per month and working hours per day, but they are not tied to the calendar dates directly, they are aggregate. So you'd need to hard code the calculation to use 5/7 of the value, or write a custom calculation of some kind.

I'd also check the Microsoft Entity SQL Docs to see what other functions exist - https://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.diffdays(v=vs.110).aspx

Regards

Jim

Tuesday, November 16, 2021
Avatar
re: tdobbelaere Friday, March 23, 2018

tdobbelaere , use JavaScript to calculate it at frontend.

<script type="text/javascript">
  <xsl:text disable-output-escaping="yes">
    <![CDATA[
         console.log('hello world');

function calculateBusinessDays(startDate, endDate) {
// Validate input
	let negative = false;
	if (endDate < startDate) {
		negative = true;
		[endDate, startDate] = [startDate, endDate];
	}

// Calculate days between dates
	const millisecondsPerDay = 86400 * 1000; // Day in milliseconds
	startDate.setHours(0, 0, 0, 1);  // Start just after midnight
	endDate.setHours(23, 59, 59, 999);  // End just before midnight
	let diff = endDate - startDate;  // Milliseconds between datetime objects
	let days = Math.ceil(diff / millisecondsPerDay);

// Subtract two weekend days for every week in between
	let weeks = Math.floor(days / 7);
	days = days - (weeks * 2);

// Handle special cases
	let startDay = startDate.getDay();
	let endDay = endDate.getDay();

// Remove weekend not previously removed.
	if (startDay - endDay > 1) days = days - 2;

// Remove start day if span starts on Sunday but ends before Saturday
	if (startDay === 0 && endDay !== 6) {
		days = days - 1;
	}

// Remove end day if span ends on Saturday but starts after Sunday
	if (endDay === 6 && startDay !== 0) {
		days = days - 1;
	}

	return negative ? -days : days;
}

    ]]>
  </xsl:text>
</script>

 

Statistics
  • Started: Friday, March 23, 2018
  • Last Reply: Tuesday, November 16, 2021
  • Replies: 2
  • Views: 8877