Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
IIf statement, DatePart 1
- Thread starter wvmbark
- Start date
rDodge and I calculate the easterday in two totaly different ways. I used something called Gauss easter algorithm. They seem to give the same result. At there is an explanation of different algorithms.
If you add or change the 'fixed' holiday dates in my version you can easily as OhioSteve wrote call my function with Today - 1 and if it result false call it again with Today - 2 and so on until you get True.
If you add or change the 'fixed' holiday dates in my version you can easily as OhioSteve wrote call my function with Today - 1 and if it result false call it again with Today - 2 and so on until you get True.
The list of functions that I had written were meant as a way to be able to call those functions to calculate the various dates. Yes, you are right, there are other parts to the code. I still have yet to complete my Holiday/WorkDay/NetWorkdays VBA equivalent code as I still need to put in some different options too.
The thought process is this. The company has a list of holidays they recognize every year. Rather than putting in dates (which would require changing every year), why not use the names of the holidays, then call the function by name to calculate their dates.
We still have 2 other issues to address. First, the fixed holidays at times are on weekends while other times are on weekdays. The question then becomes, how do we handle the holiday pay for those dates that falls on a non-business day (normally on Saturday or Sunday)? Do we move the paid holiday to the previous, next, or closest normal business day and mark that day as a non-business day?
Now that we mentioned the first issue, the second issue would be dealing with how to group holidays when you have multiple holidays so close together. Do you want to apply the first rule that was used or do you want to group the holidays as close to each other as possible.
Example:
Christmas Eve, Christmas, New Years Eve, and New Years are all holidays for me. In 2005/2006, all 4 of these holidays will fall on Saturday and Sunday. How would we like to set the paid holidays? Do we apply the first rule as discussed above, or do we want to group these holidays as close together as possible when they fall on a non-business day.
If we use the closest rule, paid holidays would be 12/23/2005, 12/26/2005, 12/30/2005, 1/2/2006. Now, if we want to group them together, so as employees would only need to take 1 vacation day for that week and still be able to enjoy their 9 days off (12/24/2005 - 1/1/2006) while getting their vacation/holiday pay, why not group them like 12/26/2005, 12/27/2005, 12/29/2005, and 12/30/2005. This is the part that I have not coded at this point of time. This is where my thought process has come from.
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
The thought process is this. The company has a list of holidays they recognize every year. Rather than putting in dates (which would require changing every year), why not use the names of the holidays, then call the function by name to calculate their dates.
We still have 2 other issues to address. First, the fixed holidays at times are on weekends while other times are on weekdays. The question then becomes, how do we handle the holiday pay for those dates that falls on a non-business day (normally on Saturday or Sunday)? Do we move the paid holiday to the previous, next, or closest normal business day and mark that day as a non-business day?
Now that we mentioned the first issue, the second issue would be dealing with how to group holidays when you have multiple holidays so close together. Do you want to apply the first rule that was used or do you want to group the holidays as close to each other as possible.
Example:
Christmas Eve, Christmas, New Years Eve, and New Years are all holidays for me. In 2005/2006, all 4 of these holidays will fall on Saturday and Sunday. How would we like to set the paid holidays? Do we apply the first rule as discussed above, or do we want to group these holidays as close together as possible when they fall on a non-business day.
If we use the closest rule, paid holidays would be 12/23/2005, 12/26/2005, 12/30/2005, 1/2/2006. Now, if we want to group them together, so as employees would only need to take 1 vacation day for that week and still be able to enjoy their 9 days off (12/24/2005 - 1/1/2006) while getting their vacation/holiday pay, why not group them like 12/26/2005, 12/27/2005, 12/29/2005, and 12/30/2005. This is the part that I have not coded at this point of time. This is where my thought process has come from.
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
Well, I think that that brings us back to my long post. I readily admit that your functions are really cool. I especially like the code that calculates Easter Day. But is this approach ideal for an actual HR database? I would argue for my "hybrid" approach. It is technically inferior to your approach but it is more practical.
You just create a table or array called holidays. You input the values. The function should have this logical structure...
Input a date
If the date is a Monday
subtract three from the date
otherwise
subtract one from the date
look in the holiday table [array]
if the date appears in that table
subract one from the date and check again
otherwise
Return the date
_____________
This allows for managerial discretion. If the boss says "everybody take Friday off" you can just add Friday to your table.
You just create a table or array called holidays. You input the values. The function should have this logical structure...
Input a date
If the date is a Monday
subtract three from the date
otherwise
subtract one from the date
look in the holiday table [array]
if the date appears in that table
subract one from the date and check again
otherwise
Return the date
_____________
This allows for managerial discretion. If the boss says "everybody take Friday off" you can just add Friday to your table.
I understand what you saying about adding dates to when employees takes days off by the call of management as my place of employment also has that deal. However, I must pose this question with regards to that:
Does the employee get paid for these days that management call off as if they were holiday pays meaning employees don't have to burn a vacation day to get paid for that "lack of work available" day off? If not, from a programming stand point of view, it seems as though you would need to create 2 more columns; Type of Off Day, and Date. The criteria for each record would be either the type of day off would be Holiday (let's say this has an ID number of 1) or Lack of Work Available (let's say this has an ID number of 2). For type of day off, 1 would only need the holiday string name filled in while for type of day off, 2 would only have the specific date recorded. Of course, this could be done in separate tables too. Anyhow, this all comes down to DB Design work as far as what is the most efficient way of doing it, which gets into the planning stage.
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
Does the employee get paid for these days that management call off as if they were holiday pays meaning employees don't have to burn a vacation day to get paid for that "lack of work available" day off? If not, from a programming stand point of view, it seems as though you would need to create 2 more columns; Type of Off Day, and Date. The criteria for each record would be either the type of day off would be Holiday (let's say this has an ID number of 1) or Lack of Work Available (let's say this has an ID number of 2). For type of day off, 1 would only need the holiday string name filled in while for type of day off, 2 would only have the specific date recorded. Of course, this could be done in separate tables too. Anyhow, this all comes down to DB Design work as far as what is the most efficient way of doing it, which gets into the planning stage.
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
On its most basic level, a function is a process that will change input in a predictible way. A function like findEaster() is very interesting intellectually, but it gets away from the basic idea of functions. Ultimately, the boss arbitrarily decides what days will be holidays. So it is NOT really a predictible process. That is why I don't think that we should model that process- because its not really a process.
I am speaking about holidays in the context of an HR database. I guess if the Vatican's dba wanted to write a function to determine Easter Day, I could go for that
I am speaking about holidays in the context of an HR database. I guess if the Vatican's dba wanted to write a function to determine Easter Day, I could go for that
- Status
Similar threads
- Locked
- Question
- Locked
- Helpful tip
