Time Off Accrual Spreadsheet

respinosa · #1 06-01-2009, 02:41 PM

Ok, so I'm not too advanced with Excel, can do simple things, but I need to be able to track on going time off by my employee's via a spreadsheet. Here's what I would like for it to do:

1. Name of Employee's down Column A
2. Bi-monthly dates across Row 2
3. Column "AA" is Total used that year
4. Column "AB" is what their balance remaining is for the year
5. If Column "AB" goes negative, number turns Red and bold

So basically if they took off 8 hours for the entire month of June, Column "AA" would show "8", Column "AB" would show much Paid Time Off they have left to use. If no time off is taken, then that number in Column "AB" would increase by the amount of Paid Time Off they are allowed to accrue based on company guidelines of say 5 hours each pay period.

I hope this is enough information on what I would like to accomplish. I tried searching the forums but have no idea where to start!

Thanks in advance for any and all help!

NBVC · #2 06-01-2009, 02:42 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

NBVC · #3 06-01-2009, 03:08 PM

Not sure if I understand you correctly, but perhaps formula in BB3 should be:

=(81.57-AA3)+COUNTBLANK(C3:Z3)*5

where 81.57 is the starting number of Hours and 5 is the hours/pay period aloud for periods where no hours taken off.

respinosa · #4 06-01-2009, 04:00 PM

Actually I "think" I figured it out!

Here's what I used:
=SUM(81.57-AA3)+COUNTIF(C3:Z3,0)*5
The only think I need left to do, is if it goes negative, to make cell "AB" turn red and bold, so it stands out.

Thanks again for the help.

NBVC · #5 06-01-2009, 04:44 PM

Select the cells and go to Format|Conditional Formatting

Cell Value Is >> Less than >> =0

Click Format and choose from Font tab

Welcome to the Excel Forum

Forum Statistics