Excel as Attendance Tracker
 

Merry Christmas Everyone!!

I've posted this on another board - got lots of views but no responses
so I'm hoping Santa will be good to me and someone reading this thread
will have an answer.

We have 100+ employees. We need to track PTO, Vacation in hours and
then also log other instances of time away from work (f= fmla,
t=travel, c=comp time, h=work at home). However these "other
instances" do not need to be tracked in terms of hours used.

The only suggestion that came on the other board was to have 3 rows per
employee - not a good solution.

My solution so far has been to have 3 columns for each day but this is
causing me to run out of columns before I get to April.

I thought I could use SUMIF but apparently I'm wrong. Why can't I put
something like "8v" (indicating 8 hours of vacation used) and then have
my totals column look at the range and sum if it says 8v, the column
next to this would sum if it said 8p.

Any help would be appreciated.


--
Donna123
------------------------------------------------------------------------
Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

I'm not an expert but it think you could.

Try this, in a blank column input some different numbers like 8v, 8p,
7v,4p and so on, next put this formula in any cell, just make sure you
alter the formula to suit your range:

=SUM(IF(RIGHT(B1:B10,1)="v",--LEFT(B1:B10,LEN(B1:B10)-1)))

this formula will look at a range of cells, if the cells ends with "v"
it will add them to the total, you will see that the formula only adds
those cell that ends with "v".

this is an array formula so you will have to enter it with
CTRL+SHIFT+ENTER, just hitting the enter key will not work.

Hope this Helps!
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

How about one column per employee? One row for vacation, one row for
holiday, etc. Whenever hours are expended, just update the appropriate cell.
--
Gary's Student


"Donna123" wrote:


Merry Christmas Everyone!!

I've posted this on another board - got lots of views but no responses
so I'm hoping Santa will be good to me and someone reading this thread
will have an answer.

We have 100+ employees. We need to track PTO, Vacation in hours and
then also log other instances of time away from work (f= fmla,
t=travel, c=comp time, h=work at home). However these "other
instances" do not need to be tracked in terms of hours used.

The only suggestion that came on the other board was to have 3 rows per
employee - not a good solution.

My solution so far has been to have 3 columns for each day but this is
causing me to run out of columns before I get to April.

I thought I could use SUMIF but apparently I'm wrong. Why can't I put
something like "8v" (indicating 8 hours of vacation used) and then have
my totals column look at the range and sum if it says 8v, the column
next to this would sum if it said 8p.

Any help would be appreciated.


--
Donna123
------------------------------------------------------------------------
Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

PinMaster,
Your formula seems to be getting me closer to something - at least its
showing a zero in the field.

If nothing else, this tells me excel CAN do this, I just need some fine
tuning to the formula is all. Any ideas as to why it would come up as
zero? I put the formula in exactly as you showed:

=SUM(IF(RIGHT(B1:B10,1)="v",--LEFT(B1:B10,LEN(B1:B10)-1)))


--
Donna123
------------------------------------------------------------------------
Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

Don't know why the "0" is displayed, however, if you
Format/Cells/Number/Custom and enter a "#" in the Type input box for
the cells that are affected you will eliminate the "0"'s.


--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

It may be that your data was not in the range B1:B10 or you did not
enter the formula as an array formula, array formulas need to be
entered using SHIFT+CTRL+ENTER.
One way to find out if it was entered as an array is to select the cell
with the formula and look in the formula bar, if the formula is
surrounded with brackets {} then its an array formula.
This is how the formula should look like in the formula bar:
{=SUM(IF(RIGHT(B1:B10,1)="v",--LEFT(B1:B10,LEN(B1:B10)-1)))}

Check it out and let me know how it goes

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

Pinmaster,
I tried it several times and my formula appears just as you have it
listed. Now I'm annoyed because I know this CAN work - I just don't
know how to MAKE it work! Any other thoughts as to why its not
working???


--
Donna123
------------------------------------------------------------------------
Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

Thank You Pinmaster!!

Originally I had tried the formula several times and it would not
work!! After my last post I decided to try it one last time and IT
WORKED!!!

I'm sooooooo happpppy!!! Thank you Thank you Thank you!!!!


--
Donna123
------------------------------------------------------------------------
Donna123's Profile: http://www.excelforum.com/member.php...o&userid=26962
View this thread: http://www.excelforum.com/showthread...hreadid=495940

Excel as Attendance Tracker
 

Your quite welcome, glad I could help and thanks for the feedback!

Good luck with your project!

Regards
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=495940