in Excel, I made a table where the records of employees work.
In column A2: A31 are the dates in January, etc.
I wrote a function holidays and now
in cell D2 I need a formula that will in cell A2 recognized holiday, and if
the working day to enter No. 8
For example: if the date in cell A2 1.1.2011. (Saturday) in cell D2 should be 0, and if the date 6.1.2011. (Thursday).
should be No. 8.
Holidays on Saturdays and Sundays should be excluded.
Thank you for your help.
Last edited by barkarlo; 05-23-2011 at 03:13 PM.
How about
=(WORKDAY(A5-1,1,holidays)=A5)*8
where holidays is the range of holiday dates
I made a VBA code for dates of the holidays,.but how to apply the formula.Originally Posted by Bob Phillips
Code looks like this:
Please Login or Register to view this content.
It appears that you may be over complicating the situation slightly.
Rather than a separate function for the holidays, there is a pre-existing function =Workday that functions similarly. This function uses an array for the holiday values.
Rather than the imbedded dates within the VBA, I'd recommend setting those to another worksheet, and name the array of holidays (for however many years of data you have), as "Holidays"
Then using Bob Phillip's equation, you can get what you're looking for within the employee data worksheet.
OK, I'll make a separate table with a list of holidays, but would like to know how to implement a written VBA code in the formula.
Why do you insist on that unnecessary step?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks