Free Microsoft Excel 2013 Quick Reference

Time Off Accrual Spreadsheet

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!


Post your answer or comment

comments powered by Disqus
I work for a company that owns hotels and casinos all over the States.
Company wide I would say we have about 13,000 employees. The hotel I work at
has around 125 employees (even w/ the constant turnover after Katrina hit).
My problem is, that my corporate office refuses to update our payroll
operations. We still use punch time cards that every other Friday I have to
manually enter time into the payroll server timesheet. Our system does not
calculate paid time off, among a lot of other things. My employees come to
me almost daily asking if I can tell them how many PTO days they have left.
The only way I have to figure it out is by going through every PTO bi-weekly
request form spreadsheet until I have added up the days taken throughout
their service year.
I am trying to build a spreadsheet in Excel 2003, that consists of:

Emp#, Name, Job Class, Hourly Rate, Hire Date, Birthdate, Last Review Date,
PTO per yr, PTO taken, PTO days left....among other personnel information.

I want to create a formula that will calculate the PTO per yr the employee
has. After 6 months = 2 days
1 year = 10 days
2 years = 14 days
6 years = 19 days
16 years = 25 days
26 years = 30 days
(If the employee does not use the days within their service year, they loose
it.)

I also want in the PTO taken column a formula that will pull the # of days
taken from the PTO request spreadsheet that I have to send in to home office.
The employee's differ every spreadsheet, as does their placement. Once the
days taken are in their proper column, I want the PTO left to be calculated
from PTO days per year minus the days taken. I hope someone can help, I am
at a total loss!

--
Thank you!!

Crystal

I am trying to do a 52 pay period, 50 employee, workbook that summarizes employee timeoff accruals for three different types of time off. This is for an underfunded non-profit so there is no budget. I have created the employee worksheets (dummies) with the simple formulas and rates. I have also, started the summary sheet and linked the cells. However, I cannot complete the last portion so that when the pay day is entered it pulls from the coreect row.

I have a cell that needed to keep a running total of the current balance of Paid Time Off, where F5=Beginning balance of hours F6=Accrued Hours (goes up at a steady rate every 2 weeks), F7=Total hours taken off (goes up whenever an entry is made) and F8= The Cap/Max Amount of hours
F9 could equal total current balance (F5+F6-F7).

The displayed amount needed to ignore or simulate ignoring any hours over the cap earned by accrual, but go down from the cap when ANY additional hours taken off, and then allow accruals again once its down below the cap.

Any functions I've made have pulled new hours taken off from a running total of everything, higher than the cap. So if there's technically 60 hours available to an employee and the cap is 50, if they take 5 hours off, it still just shows the cap. Help greatly appreciated, thank you!

UPDATE:

The way it's looking to me, the formula will need to know what order time off was taken in. For instance, when there is a cap of 60:
Scenario 1
Event 1: 50 hours opening balance
Event2: 20 hours accrued
Event 3: 25 hours taken
The ending balance is: 35 (60-25)

Scenario 2
Event 1: 50 hours opening balance
Event 2: 25 hours taken
Event 3: 20 hours accrued
ENding balance: 45 (50-25+20)

Sime time off is taken randomly disperse throughout the static growth of the earned time, it seems its going to get impossible or extremely complicated. Is there a better way to do with, multiple formulas?

-Brant

Hello, does anyone know how to remove a line that begins in the row heading and goes all the way thru the entire spreadsheet and continues off the spreadsheet? It's not a gridline and it's not a border either...I've checked both of those numerous ways and times, in both the cell/row above the line and below. Pls help!

I am currently working with a spreadsheet to work with time off requests. I want to have an idea oh how occupied our hotel is as far out and accurate as possible. I am working through the rest of the year, and will have a new sheet for next year. Every weekday we receive a 90 day update that only has rooms occupied, and we receive a 5 day update with rooms occupied, arrivals and departures. So 5 days out, I need to pull arrivals, departure, and occupied rooms, and between 6 and 90 days I need to pull occupied rooms from a different sheet. I would like to be able to update these numbers every time it is sent out. Would any body be able to provide guidance as to how to do this. If you would like to see the spreadsheets just let me know and I can them to you, I cannot post them on a public forum though.

I'm trying to set up a spreadsheet to keep track on staff vacation time off on accruel basis. I would like to quickly identify how many day each person has remaining, how many days have been used up, etc... I know the basics of excel, but this formula is too complicated and would appreciate someone's help. Here are the facts,
0 days -90 days - earn 0 vacation time91 days - 12 months - earn 0.83313 months - 60 months - earn 0.83361 months - 120 months - earn 1.250121 + months - earn 1.667Rollover from last period capped at 110%, stops acrruing until it's used up.

Thank you.

Trying to format the attached to calculate paid time off by multiplying hh:mm worked times accrual rate (eg. .039), then convert totals to hh:mm and ensure they are adding correctly. I know it can be done but I'm getting turned around with combining formats and formulas.

Thanks!

So. I'm trying to make a spreadsheet for my payroll person to help her keep track of our employees paid time off hours as they earn them.

How we do it here is as follows:
You work 80 hours, you get 4 hours of PTO. Hours roll over every week until you get 80, then they are reset to 0 and 4 hours are added to your PTO.

How would I go about making a spreadsheet that has our employees in A, their hours worked in B (or over B, C, D, E for each week of the month they work) and their PTO hours in C and have it add the PTO hours up when we type in hours worked and have it add up the PTO and reset the hours worked when it hits 80.

Any help on this would be appreciated.

Thanks for your time.

Hi All,

I thank you in advance for any input you may have regarding the attached spreadsheet.

I am creating this spreadsheet mainly for myself, but also intend to distribute it to my coworkers to keep better tabs on their time off balances.

The cell I need help with is E21 (hi-lighted in yellow) - the Birthday Holiday. I want to implement a constant formula from E11 on to basically disregard the Birthday Holiday (PTO-B) and continue on with the normal calculation. I have tried several IF THEN statements and can't seem to get it to work properly. I imagine I will need something to read each individual code, but since there are less than 7 it shouldn't be a problem, right?

I apologize if this has already been addressed - I might be searching for the wrong tidbits. This is my first post, but I searched for anything pertinent and didn't find anything.

I have set up our PTO workbook but am having a problem. Is it possible to setup the worksheet so that it automatically converts the accrual rate. So on 09/07/11 it stops accruing at the .2.4615 rate, without losing that ending balance it picks up and adds the new accrual rate of 4.000 from that point on?

Then at the end of the year (12/31/11) it stops the accrual rate so that when a new sheet for 2012 is started it can calculate the appropriate balance from the previous year?

Attached is the workbook.
PTO Workbook 2011.xlsx

Thanks

Microsoft Outlook 2007 Calendar Appointment .vcs Time Off

We're running Office 07 SP2 and Office 03 SP3 on our network computers.  We are running into an issue when opening an Appointment using the .vcs file.  The time behind by -4 hrs in Outlook 07.  It's not off on Outlook 03 computers.  The issue is consistence on all machines.  We believe it to be a Microsoft bug but need help on figuring it out.  Our Domain Controller, Exchange and Computer time is correct.

I needed help in creating a spreadsheet that would calculate accrual vacation, sick and personal days. We also have listed that you are allowed to carry over a portion of your unused vacation days to the following year. We are not on a calendar year, instead we use the anniversary date. I have tried looking at older threads to find a template to work with and possibly help me get started, but most templates only calculate vacation time as a whole and not broken out into vacation, sick, or personal time. Not sure if the easiest method might be to create a tab for each individual employee. Below is how we accrue our time:

Vacation Time Years 0-2 Years 3-5 Years 5+

Hours Earned Per Month 3 1/3 6 2/3 10
Hours Earned Per Year 40 80 120
Days Earned Per Year 5 10 15

Sick Time After completion of 3 months of service. 3 sick days per anniversary year. Sick days will accrue at 2 hours
per month from date of employment.

Personal Time After completion of 3 months of service. 3 personal days per anniversary year. Personal days will accrue at 2 hours
per month from date of employment.

Any help or ideas on how to get this started would be appreciated.

I am trying to set up a spreadsheet to track PTO. I have attached my spreadsheet. first tab is the accrual rate and the second tab is accumulated time off. I have a second spreadsheet that i will connect as a pivot table to give me actual. I am looking for a formula to help me with the accumulated based on accrual rate. Any help would be greatly appreciated!

Sorry about the newbie question, but I've tried using the online help and can't figure it out.

A cycle club I belong to had a time trial race. We start the stop watch at 0:00 and riders went off at 30 second intervals then do several laps of a course and then finish. We recorded lap times and finish times all from the reference starting time (00:00).

I want to make a spreadsheet that I can enter each start time in minutes and seconds starting at 00:00. I imagine this would be column 1 and go in sequence 00:00, 00:30, 1:00 etc etc. But I don't seem to be able to set the format that shows the number as a time that isn't the time of day. When I type in 00:00 excel sees it as 12:00am.
From there I want to be able to enter all the data from the laps and finish times in seperate columns then use simple formulas to subtract the recorded time from the start sequence (30 second intervals) to get the actual lap times and finish time.

Every thing I do tends to become a problem because of excel seeing the time as a time of day rather than a elapsed time from the 00:00 start time.

Help please.

I would like to create a macro that is activated when a spreadsheet is opened. The macro would take note of the time the spreadsheet was opened, the changes that are made and then the time the file is closed. These details would be saved onto a separate worksheet in the workbook. Each time the spreadsheet is opened, the macro would create new 'time stamps' and activity logs on the same worksheet.
Can anyone help me please?
Many, many thanks
Jon

I am looking for a formula. Our employees earn 1.5333333333 hours per week if they are hourly employees and 1.933333333 hours a week if they are salary and I am looking for a way to set that up in an excel spreadsheet so that i can calculate how much is accrued based on the week, then have the next column show pto used and the next colum to show available pto. Is this enough information for someone to help me? Thanks in advance!

I have a spreadsheet with 70 rows. As a user enters a job number in column C, I want the date and time to automatically post in column A. This is the time he started the job. Later, when he enters a task number in column D, I want the date and time to automatically post in column B. This is the time he completes the job. I want the time worked to post in column E. This is a weekly time card maintained throughout each day. I don't know how to make the dates and times permanent as originally posted so I can calculate the time worked on each task.

I am a total newbie at writing VBA and need some help. I have a workbook that contains scheduling data for our production floor. I was asked to find a way to show when (date and time) yarn was going to finish from a particular machine in a way that will show any overlap. I came up with a sub-routine that will copy the relevant data to a transfer sheet and delet any blank rows. I now need to figure out a way to transfer the data from the transfer sheet into the calendar sheet and place it in the correct day/time slots. The calendar sheet is formatted with days of the week (starting with Monday and ending with Sunday) as column headers and hours (starting with 8:00 am and running for a 24 hr. period) as row headers. I can't attach the workbook because of file size limitations, but will include the code that I have come up with below:


	VB:
	
 
Sub CopyPasteValue() 
    Dim i As Long 
    Sheets("New Sched").Range("D6:E72, L6:L72").Copy 
    Sheets("Transfer").Range("A2", "C2").PasteSpecial Paste:=xlPasteValues 
    Sheets("New Sched").Range("T6:U56, AB6:AB56").Copy 
    Sheets("Transfer").Range("A69", "C69").PasteSpecial Paste:=xlPasteValues 
    Sheets("New Sched").Range("AL6:AM72, AT6:AT72").Copy 
    Sheets("Transfer").Range("A120", "C120").PasteSpecial Paste:=xlPasteValues 
    Sheets("New Sched").Range("BB6:BC56, BJ6:BJ56").Copy 
    Sheets("Transfer").Range("A186", "C186").PasteSpecial Paste:=xlPasteValues 
    Sheets("Transfer").Select 
    Sheets("Transfer").Range("A2:C2" & Range("A:C").SpecialCells(xlCellTypeLastCell).Row).Select 
     ' Turn off calculation and screenupdating to speed up performance
    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        For i = Selection.Rows.Count To 1 Step -1 
            If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then 
                Selection.Rows(i).EntireRow.Delete 
            End If 
        Next i 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code copies the relevant data from the New Sched sheet and pastes it into the transfer sheet and deletes any blank rows. I need help with the next step - populating the calendar with the copied data. I appreciate any help that you all can give me. I have already gotten a lot of help just reading posts here, but couldn't find one that matched my needs (although some seemed close).

Thank you for your consideration!

Hi all,

Firstly, thanks for all you have done for me in the past.
I need your help again.
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.

My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it. Maybe that isn’t the way to go. In any case, I am at a loss for ideas.
Any ideas, suggestions and/or solutions would be greatly appreciated.

Cheers
Jeff

My spreadsheet is a calculation of employee work time off taken and paid back.

eg.
Date Hours Taken Total Date Hours Paid Back Total Balance Owing
25-Apr-08 07:00 - 11:00 4:00 26-Apr-08 07:00 - 11:00 4:00 0:00

In the above example, 0:00 is the correct answer, however, sometimes my formula =sum(d4-i4) returns a negative value by displaying #####. How can I avoid this and return the correct answer of 0:00?

Thanks.

Ok, I'm sure this has been asked before, and I've downloaded a excel spreadsheet from this site that appears to be the same way I've setup my spreadsheet, however I'm still having problems with the calculation on cell H11, which also transfers the problem to cell E27 which is "=H11".

What's happening is "H11" will calculate back to "0:00", even if I simply put "=SUM(H4:H10)". I'm simply summing times which generally is 40 hours for a normal work week. I've tried formating the cell with "h:mm,@", or "[h]:mm", or even the standard "Time13:30" format. If you look at it, I currently have an IF statement simply stating that if all of H4 thru H10 are blank then I do not want to see anything in the "Total" cell. This is working quite nice everywhere else I want to keep a cell blank until certain fields are populated and so I know this isn't the problem. And as I mentioned above I can put "=SUM(H4:H10)" and I still get the same problem.

Can anyone please help?

******** ******************** ************************************************************************>Microsoft Excel - template.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC4F4G4H4F5G5H5C6F6G6H6C7F7G7H7C8F8G8H8C9F9G9H9C10F10G10H10F11G11H11=
BCDEFGH2DayDateTIME INTIME OUTTIME WORKED EACH DAYTIME OFFTOTAL TIME3TIME OFF EACH DAY4SUN

5MON

6TUE

7WED

8THUR

9FRI

10SAT

11 TOTAL TIME WEEK #1

Time Sheet Calculator
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

How do I format a spreadsheet set up as a time card entry log so that I can enter 0543 and it appears as 05:43 AM and/or enter 1653 and it appears as 4:53 PM.

I have it formatted right now to show that data but I have to enter it as 05:43 and it appears as 5:43 AM which is what I want and the in/out times calculate with an end result of hours worked.

But -- I want to do it without having to enter the colon.

Any suggestions?

Thanks,

Is there a way to save and close a spreadsheet when there has been no
activity for a particular amount of time? We have a problem with several
users needing access to the same workbook.
Thanks

Please help with the following spreadsheet. I hope it's readable.

CTA 01T Fund

Hourly Beginning Total Leave Hrs Charged Ending Leave
$ Balance
Wages Balance Taken This to CTA this Balance
Allocation at End of
Pay Period Pay Period
% for 01T PP

$40.28 500.00 24.50 31.00 458.00
45.00 $18,448.24

$47.79 620.00 2.00 76.00 543.00
55.00 $25,949.97

$45.23 870.00 0.00 53.00 817.00
78.00 $36,952.91

$47.81 1120.00 33.25 46.75 1040.00
100.00 $49,722.40

$45.50 360.00 0.50 46.00 313.75
32.00 $14,275.63

$47.88 1060.00 5.00 75.00 980.25
95.00 $46,934.37

$209,799.90 Total CTA Funds Remaining
$192,283.52

I need 26 workbook tabs of this spreadsheet that update the balances each
pay period. The ending balance contains a formula to calculate time off,
hours worked and the percentage charged to each fund for time off hours.
There will be 5 or 6 of the above columns in each spreadsheet for all of the
funds employees work in.

Susan


No luck finding an answer? You could always try Google.