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!

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!

- Desperately trying to build a paid time off accrual worksheet...
- Time off accrual workbook
- Accrual Cap Paid Time Off Function Help Needed
- Line going thru spreadsheet and off the spreadsheet too
- Updating Spreadsheets based off new spreadsheets
- Formula needed for Calculating Vacation/Sick/Personal Time Off on Accruel basis
- Calculalting Time off in decimals / converting totals to hh:mm
- Paid Time Off Automatically?
- Personal Time Off
- Paid Time Off Accrual Workbook
- Microsoft Outlook 2007 Calendar Appointment .vcs Time Off
- Vacation, Sick, and Personal Days Accrual Spreadsheet
- PTO Accrual
- Trouble with time(?) function in spreadsheet
- Macro to create time stamps when spreadsheet is open
- Formulas for creating paid time off spreadsheet
- Freeze date and time in two spreadsheet columns
- Populate Calendar sheet with Style, Set, and Time Off data from New Sched sheet
- Changing date/time to run a 12 hour production schedule, and not 24 hour
- Subtraction of Time
- Calculating times, but not the right answer...
- Time Card Data Spreadsheet
- Timed shutdown of spreadsheet
- Updatable Spreadsheet

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

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

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.

Thanks!

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.

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.

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

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.

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.

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.

Can anyone help me please?

Many, many thanks

Jon

VB: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).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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you for your consideration!

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

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.

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.

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,

activity for a particular amount of time? We have a problem with several

users needing access to the same workbook.

Thanks

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.