Excel as Attendance Tracker

[Donna123]

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.

 

[pinmaster]

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

 

[Guest]

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

 

[Donna123]

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

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

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

 

[Lost4Now]

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

 

[pinmaster]

It may be that your data was not in the range B1:B10 or you did no
enter the formula as an array formula, array formulas need to b
entered using SHIFT+CTRL+ENTER.
One way to find out if it was entered as an array is to select the cel
with the formula and look in the formula bar, if the formula i
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
J

 

[Donna123]

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

 

[Donna123]

Thank You Pinmaster!!

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

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

 

[pinmaster]

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

Good luck with your project!

Regards
J