I have a timesheet that will populate the days of the pay period once
the user enters the start date of the PP. I would like for the dates
to only include the date that is a weekday. The other part would
perform a lookup and if if there is a holiday included in the pay
period it would not return that day. I understand I would have to
build the lookup table with the dates I want to exclude. TIA
Just saw your message and thought that I might be able to help. To
start I'm a little confused about your goal but I think I have a good
idea about what your are trying to achieve. So it looks like you need
two list, 1 = Start date of Pay Period 2 = Pay days which are only
weekdays less holidays (right?).
Ok the first one is pretty easy, if your pay period starts on Monday
than put 1/5/04 in a new excel worksheet (first Monday of 2004). Put
the pointer on this cell, hold down shift and go down 51 rows. With
that selection highlighted click on EDIT-FILL-SERIES on the pop up box
make sure that the DAY radio button is clicked and for the STEP VALUE
chance that to 7. This will give you a list of every Monday in the
year. *NOTE* I did not include the last one since it is a stub
period.
For the second list you'll need every work day in the year. Basically
it is the same thing, put 1/5/04 in a cell, go down 260 cells
(weekdays only ;0)EDIT-FILL-SERIES and this time when the pop up box
comes up under the DAY UNIT click on the WEEKDAY button. Your last
date should be 12/31/04. Now since each company is different you'll
have to go in and for each vacation day instead of a date I'd put in a
"Vacation". So for Christmas instead of 12/25/04 you'll have 0
(except Xmas in on a Sat this year?!? so do change 12/24/04 to
Vacation).
Ok now we have the tools we'll need to build this spreadsheet... For
reference we'll call the two list 1 = PP and 2 = Weekday
Ok in Cell A1(or any cell really) type "Enter Start of Pay Period
Below"
In Cell B1 (or the cell below the other one) click on DATA-Validation
and you should get a pop up box. Under ALLOW chose LIST. Under the
SOURCE click on the little red button next to the input box and then
highlight the PP list; <enter><enter>
Ok now when you put the pointer in cell B1 you should see a drop down
box and you can chose the PP start date (note you'll have to date
format B1).
Now is where this gets tricky assuming that the PP list is in column G
and the days are in column H
in the cell next to the one with the drop down box type
=INDEX($H$2:$H$261,MATCH($A$2,$H$2:$H$261)+1,1)
Copy and paste this into the next 3 cell to the right and in the 3rd
day change the +1 to +2. In the 4th day change the +1 to +3 and in
the 5th and final work day change the +1 to +4. This will call up the
next 1,2,3, or 4th day in the Pay Period and if one of those is a
vacation day it will say Vacation in the cell. On the Drop Down list
you created select 12/20/04 and the last day of the week should come
up with "vacation".
here is what I have..
1 A B C D E F G H
2 Enter Day 2 Day 3 Day 4 Day 5 PP WD
3 12/20 12/21 12/22 12/23 Vacation 1/5/2004 1/5/2004
1/12/2004 1/6/2004
1/19/2004 1/7/2004
Now since I do not know the specifics of what your trying to do I
can't offer much more. If you need to sum up the work week of course
you can't use "Vacation" and we'd have to use an IF statement to drop
a zero in there. Also you'll want to format the page a bit by doing
things like hiding and protecting some rows like the PP and WD.
I hope this helps and if you have any other questions or need to
modify this email me or post here.
Good luck.
chad
