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.

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

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
>
>

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)))

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.

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,
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???

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!!!!

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

Good luck with your project!

Regards
JG