Calculating weekends and public holidays

Classic Servoy
1

Hi all

I have built a human resources solution which is perfect apart from one nagging hurdle that I have stumbled on.

I have a leave table which calculates time off for holiday and sickness for each employee. It works fine in the sense that it works out how many days elapse between the beginning of leave and the end. It does not however make any provision for weekends or public holidays. I am beginning to think that I am going to have to put each day off into an array and count off how many days are in the array that are not [0,6] (Sunday and Saturday). Trouble is that in a calculation, I don’t know how to do this. I also don’t know how to approach the question of public holidays. Can these be calculated (I guess Easter would be a problem).

Has anyone had to tackle these problems and if so, I’d be grateful to know the approach you used. I’m happy to work it out, just need a pointer in the right direction to get started…

Thanks

Bevil

2

Just from the top of my head: use a loop starting from start_date and ending on end_date, increase one day each loop; inside the loop use the getDay() function and icrease a variable only if getDay() is not 6 or 0. At the end of the loop your variable will contain the number of working days in the time frame. Shouldn’t be too difficult to code.

For public holidays I think the only viable solution is to preload an array with all the holiday dates for the current year and run a check against this array too in the loop before increasing the variable.

3

for anyone following this path in the future, this is the way:

if (start_date && end_date)
{
var start = start_date
var end = end_date
var days = ( ( end - start ) / 86400000 ) + 1
var workingdays = 5 * parseInt( days / 7 )
for ( var i = 0; i < days%7; i++ ) {
var d = (new Date( Number( start ) + i * 86400000 ) ).getUTCDay()
if( d > 0 && d < 6 )
workingdays++
}
return workingdays
}
else
{
return 0
}

it is a given that in any 7 days, 2 are weekend so we only need to concentrate on the number of weeks and the remainder of days. I did not write this myself… :)

4

Thanks for sharing the date calc, Thunder!
Now I have another challenge:

Calc the end date, given by an amount of workingdays and a startdate.
The workingdays vary between 35 and 5 and need to be subtracted from the startdate (so the enddate is always earlier than the startdate).

Is that possible?

5

Simple:

Divide the number of days that you have to go back by 5 and floor that number, then multiply by 7.

Substract the number of days you got from the previous calc from your startdate, then check the day of the resulting date.

If the day = sunday: substract 2 more days
else the day = saturday: substract 1 more day

Voila.

Paul

6

I need to use this and was hoping someone might have complied the code.

I want to be able to enter a start date and end date and subtract the weekends and holidays, so it can be run on a daily basis to calculate turn-around-time.

I have similar code for FM but it does not account for holidays.

Thanks
Erich

7

I don’t see where the calcs & approaches being proposed take into account holidays, especially ones that fall on different dates each year (such as Bevil’s Easter example).

When having to set something up like this in the past, the approach I took was to, year by year, generate one row in a Dates table for each day of the coming year. Non-holiday and non-weekend records are flagged in a WorkingDay boolean field, and the end-users can remove that WorkingDay flag for those holidays whose dates vary (and special company holidays, such as national days that one union might have off but not another).

Then, to calculate an end date, given a start date and # of working days … or to calculate # of working days given a start date and end date becomes simple. Plus, you now have a table of the official company working days and “off” days, so that if your solution is to show a calendar it can display that sort of status … and if you wish to enhance it some day to, for instance, calculate overtime or differential pay for workers who come in on “off” days, etc., you’re ready-to-go.

kazar

8

Yes, that was what I thought regarding the holidays, I was hoping someone had already come accross this issue and written the code.

I guess I will wait and see if someone else writes it before I need it. If not I will post the code with place holders for the holiday varibles.

Thanks,
Erich

9

In my post I forgot 1 important step: subtracting the number of days left in the calculation of weeks.

for example: if you want to go back 9 days, you need to subtract the following number of days:
floor(9/5)*7 + (9-(floor(9/5)*5)) = 11 days.

(Floor can be found in the Servoy Editor under Math, a quick browse through the functions learns that Servoy does not have a mod function onboard, or I missed it)

If the resulting day would be a sundag, subtract another 2 days to get to friday, if the resulting day is saturday, subtract anoter one day to get to friday.

This method does not take into account holidays. as Kazar says: you need to register which days are holidays each year and make corrections for them in your calcualtion.

Paul

10

pbakker:
for example: if you want to go back 9 days, you need to subtract the following number of days:
floor(9/5)*7 + (9-(floor(9/5)*5)) = 11 days.
Paul

Awesome calculation work, Paul! :D
In Servoy terms it would look like:

var workingdays = 35;
var days = Math.floor(workingdays / 5) *7 + (workingdays - (Math.floor(workingdays / 5) * 5));
11

The mod function is an operator in javascript…

5 % 2 = 1
15 % 4 = 3

12

aha, i knew I used it before :D

so, the condensed code becomes:

var workingdays = 35; 
var days = Math.floor(workingdays / 5) *7 + (workingdays % 5);

Paul

13

you guys make it look so easy

14

Challenged to get this done in Servoy, I couldn’t resist…

This adds or subtracts any number of working days from any date…

var startdate = arguments[0];
var days = arguments[1];

if (startdate == null) startdate = application.getServerTimeStamp();
if (days == null || days == 0) return startdate;

var weeks = Math.floor(days/5)*7;
var daysleft = days % 5;
var adjustment = 0;

if (days > 0)
{
	if (startdate.getDay() == 6) //saturday
	{
		adjustment = 1; //saturday + 1 + 1...5 (possible values for daysleft)
	}
	else if (startdate.getDay()+daysleft >= 6) // >=saturday
	{
		adjustment = 2; //X + 1...5 (possible values for daysleft) 
	}
}
else
{
	if (startdate.getDay() == 7) //sunday
	{
		adjustment = -1; //sunday - 1 - 1...5 (possible values for daysleft)
	}
	else if (startdate.getDay()+daysleft <= 0) //<= sunday
	{
		adjustment = -2;
	}
}
return  new Date(startdate.setDate(startdate.getDate()+weeks*7+daysleft+adjustment));

Enjoy

15

ooops, only little bug…

var startdate = arguments[0]; 
var days = arguments[1]; 

if (startdate == null) startdate = application.getServerTimeStamp(); 
if (days == null || days == 0) return startdate; 

var weeks = Math.floor(days/5); 
var daysleft = days % 5; 
var adjustment = 0; 

if (days > 0) 
{ 
   if (startdate.getDay() == 6) //saturday 
   { 
      adjustment = 1; //saturday + 1 + 1...5 (possible values for daysleft) 
   } 
   else if (startdate.getDay()+daysleft >= 6) // >=saturday 
   { 
      adjustment = 2; //X + 1...5 (possible values for daysleft) 
   } 
} 
else 
{ 
   if (startdate.getDay() == 7) //sunday 
   { 
      adjustment = -1; //sunday - 1 - 1...5 (possible values for daysleft) 
   } 
   else if (startdate.getDay()+daysleft <= 0) //<= sunday 
   { 
      adjustment = -2; 
   } 
} 
return  new Date(startdate.setDate(startdate.getDate()+weeks*7+daysleft+adjustment));

Paul

16

I will venture to re-post Bevil’s original statement of his requirements (italic/underline emphasis is my own)…

I have built a human resources solution[/i] which is perfect apart from one nagging hurdle that I have stumbled on.
I have a leave table which calculates time off for holiday[/i] and sickness for each employee. It works fine in the sense that it works out how many days elapse between the beginning of leave and the end. It does not however make any provision for weekends or public holidays[/i]. I am beginning to think that I am going to have to put each day off into an array and count off how many days are in the array that are not [0,6] (Sunday and Saturday). Trouble is that in a calculation, I don’t know how to do this. I also don’t know how to approach the question of public holidays. Can these be calculated (I guess Easter would be a problem)[/i].
Has anyone had to tackle these problems and if so, I’d be grateful to know the approach you used. I’m happy to work it out, just need a pointer in the right direction to get started… [/quote]
… and to reiterate my opinion that in a human resources solution where a particular Calendar Date has attributes that are significant, CalendarDates probably deserves its own table in the solution. Regular work day, weekend day, holiday, weekendHoliday can mean different pay differentials. Some holidays are unpredictable or might be specific only to that company and will need to be user-entered each year. One might be tracking how many “alternative days off” an employee has accrued by working on holidays, and all that sort of thing. There might be specific business rules regarding what happens to someone’s holiday pay if they were out sick on that day. Et cetera. And with CalendarDate rows, a company calendar can be generated, and other things a HR solution probably needs to do …
The calcs are great, I certainly never could have written that stuff myself (give me a couple of years, maybe) and I have bookmarked them for dissection and further study. But let’s not get buried so deeply in the beauty of calculations that we lose sight of what was needed.
It looks to me like the complex calc approach would need to be enhanced to take into account rows created in a Holidays table … and it still looks to me like simply entering every date in the year into a CalendarDates table and flagging Working Days in that table makes the counting of working days or the calculation of end dates much simpler overall, and empowers the end-users to enter their own company schedule rather than attempting to get everything into a hard calculation that will require a developer’s adjustments.
That said, I’m sure Paul’s calc could be adjusted to account for how many Holiday dates (from a Holidays table) impact the calc, and I’d love to see that added to the code (being selfish here: “as part of my learning process”). But it might take a lot of convincing for me to drop the idea that a table of dates is - IMO - the more flexible, forward-looking way to go that simplifies the programming at the same time.
kazar

17

To add (special) holidays into the equation, what needs to happen with my calc would be something in the line of:

Have a table where you enter all the dates per year that are a holiday.

Then, after running my calculation, retrieve the number of holidays that occur on weekdays.

Then, if the number of retrieved holidays <> 0, call my calc again, with the previously calculated date and the number of retrieved holidays.

You keep doing this untill the number of retrieved holidays equals 0.

So, in all, I would say you need 2 extra methods:
1: to retrieve the number of holidays between two dates. If stored properly in a table, I would use a direct SQL statement to retrieve the count
2: an overall method with a while loop, which first calls my method with the startdate and number of working days that need to be added/substracted, then calls the method that retrieves the no of holidays between the startdate and returned date (store the returned date for further use). If the no of holidays <> 0, then it loops, now using the returned date as startdate and the no of holidays as days to add/subtract and after that retrieves the nu of holidays between the previously stored returned date and the newly returned date. This keeps going, untill no more holidays are returned.

All in all, not really complicated and difficult to code.

From what you wrote, kazar, there might be all sorts of other requirements, which require less generic solutions, but for most occassions, I think this will be sufficient.

Storing every date in a table, to be able to flag it as a holiday sounds to me like a lot of redundant data.

Regards,

Paul