Need A Better Method

djorge

New Member
Joined
Dec 18, 2005
Messages
4
I attempted to post this last week but didn't get much of a response. Hoping that you all are feeling the Christmas spirit and will feel compelled to come to my rescue!!

Essentially, I'm trying to create a way to track paid time off (PTO) and vacation. I want to have a formula that will calculate the numbers for me rather than our current manual method.

Going down Column A I have all the employees listed. The remaining columns are broken into groups of 10 (representing two 5 day weeks or 1 pay period). Next to this I have one column labled "PTO" and the next column labeld "Vacation"

If an employee is on pto for any given day I'm going to insert the total number of hours off and either a "P" for PTO or a "V" for vacation. In the column labeled PTO, I'd like a formula that will look back at the previous blcok of 10 days and count the number of hours taken as PTO, same thing for the vacation hours.

To ask another way, if I have a series of columns that have 8V, 4P, 2P, 4V Can I have the PTO column at the end automatically total to 6 and the vacation automatically total to 8?[/list][/list]
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
You could play around with this and probably get something tho work. It would, however, be high maintenance. I suggust you use 3 rows for each employee. One for hours worked, 1 for PTO, and 1 for Vacation. THis will be much easier to maintain, will eliminate having to at the P or V, and is easier to read.

Just my thoughts

lenze
 

Watch MrExcel Video

Forum statistics

Threads
1,118,084
Messages
5,570,131
Members
412,305
Latest member
Mozz
Top