Calculate Working Days Between Any 2 Dates

I am working on a labor hours report which compares actual hours worked to theoretical hours worked. I need a sql or function that calculates the number of working days in a date range.

Example: Actual Sum of Hours Worked is 38
Theoretical Hours is 40

Parameters used in calculation:
Beginning and Ending Dates (Date range can be anything and is not limited to precise week, month, year)
Calculated Number of Employees Per Group and Total
Assume 8 hours per day and all employees are 1.0 FTE

Forsee calculation being something like this:
[Num Emp per Group] * [Num Work Days] * [8]

Thanks,
Berglan

Find more posts tagged with

Comments

mwilliams

Are you planning on taking into account holidays as well? The way I would see to do it would be to find what day of week the first date of the range falls on, figure out how many days it is from that date til the next monday, determine whether 0,1 or 2 weekend days were in there. If Monday is first day, 0. If Sunday, 1. If Tuesday through Saturday, 2. Do the same with the end date only going backwards back through the last Monday. Keep track of the days you took off the end of the range and the weekend days in it. You are now left with a perfect set of weeks. Divide the remaining total number of days by 7 and multiply that by 5. Add on the days you took off the front of the range and the end of the range to make your perfect block of weeks and subract the days you determined were weekends in those days. You now have the working days in your range. If you are considering holidays as well, you'll need to have a list of those to determine if any of them fall in your date range and need to be subtracted from your total.

Hope this helps. Let me know if you have questions.

PurdueUniv

Thanks for your help. I knew the solution was not straight forward from the attempts that I have done. <br />
<br />
I was not going to take holidays into account as this would require a calendar which contained not only the "normal" holidays, but also the "business" holidays (which change yearly).<br />
<br />
Thanks,<br />
Berglan<br />
<br />
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75818" data-time="1302657070" data-date="12 April 2011 - 06:11 PM"><p>
Are you planning on taking into account holidays as well? The way I would see to do it would be to find what day of week the first date of the range falls on, figure out how many days it is from that date til the next monday, determine whether 0,1 or 2 weekend days were in there. If Monday is first day, 0. If Sunday, 1. If Tuesday through Saturday, 2. Do the same with the end date only going backwards back through the last Monday. Keep track of the days you took off the end of the range and the weekend days in it. You are now left with a perfect set of weeks. Divide the remaining total number of days by 7 and multiply that by 5. Add on the days you took off the front of the range and the end of the range to make your perfect block of weeks and subract the days you determined were weekends in those days. You now have the working days in your range. If you are considering holidays as well, you'll need to have a list of those to determine if any of them fall in your date range and need to be subtracted from your total.<br />
<br />
Hope this helps. Let me know if you have questions.<br /></p></blockquote>

mwilliams

Not a problem. Glad to help. Let us know whenever you have questions.

PurdueUniv

Michael,

I found my solution after re-thinking the calculation. I used a combination of datediff statements.

((DateDiff(dd, { ts '2011-01-01 00:00:00' }, { ts '2011-03-30 00:00:00' }) + 1)-(DateDiff(ww, { ts '2011-01-01 00:00:00' }, { ts '2011-03-30 00:00:00' })*2)) as DaysWorked

The first datediff calculates the number of days between and including the end date....
((DateDiff(dd, { ts '2011-01-01 00:00:00' }, { ts '2011-03-30 00:00:00' }) + 1)

The second datediff calculates the number of weekend days in the date range....
DateDiff(ww, { ts '2011-01-01 00:00:00' }, { ts '2011-03-30 00:00:00' })*2))

Then it was a matter of subtracting the 2 values.

Thanks for your help.

Berglan

mwilliams

Berglan,

As long as your calculation takes into account starting on a weekend and ending on a weekend, etc., you should be good. Because starting on a Saturday and going 16 days, you have 6 weekend days, but starting on a Monday and going 16 days is only 4 weekend days. Good luck.

rtre

<div>function workingDaysBetweenDates(startDate, endDate) {</div>
<div>  </div>
<div> startDate = new Date(startDate.toString().replaceAll("-","/"));</div>
<div> endDate = new Date(endDate.toString().replaceAll("-","/"));</div>
<div> </div>
<div> // Validate input</div>
<div> if (endDate < startDate)</div>
<div> return 0;</div>
<div>   </div>
<div> // Calculate days between dates</div>
<div> var millisecondsPerDay = 86400 * 1000; // Day in milliseconds</div>
<div> startDate.setHours(0,0,0,1); // Start just after midnight</div>
<div> endDate.setHours(23,59,59,999); // End just before midnight</div>
<div> var diff = endDate - startDate; // Milliseconds between datetime objects </div>
<div> var days = Math.ceil(diff / millisecondsPerDay);</div>
<div>   </div>
<div> // Subtract two weekend days for every week in between</div>
<div> var weeks = Math.floor(days / 7);</div>
<div> days = days - (weeks * 2);</div>
<div> </div>
<div> // Handle special cases</div>
<div> var startDay = startDate.getDay();</div>
<div> var endDay = endDate.getDay();</div>
<div>   </div>
<div> // Remove weekend not previously removed.   </div>
<div> if (startDay - endDay > 1)   </div>
<div> days = days - 2; </div>
<div>   </div>
<div> // Remove start day if span starts on Sunday but ends before Saturday</div>
<div> if (startDay == 0 && endDay != 6)</div>
<div> days = days - 1 </div>
<div>   </div>
<div> // Remove end day if span ends on Saturday but starts after Sunday</div>
<div> if (endDay == 6 && startDay != 0)</div>
<div> days = days - 1 </div>
<div>   </div>
<div> return days;</div>
<div>}</div>