Advanced formulas in Excel

[moose]

Hi,
Usually when I've got a computer question someone on here is smart and kind enough to help me out... so here is this weeks dilemma.

I need to set up a spreadsheet to track my employees' vacation and sick time. Obviously I can do this manually, putting hire dates in and periodically updating them.. but it would be nice if Excel would generate automatically on its own days employed.. and then calculate based on that number vaca time earned, and sick time earned.. etc.

Is there a way to do this?

Our employees get 1 sick day per quarter, 2 personal days a year, and .833 days of vacation per month.

Alright, any suggestions as always are greatly appreciated! Thanks!

[moot]

What is your present spreadsheet looking like?  How much automation are you looking for exactly?

[myelo]

Go to Microsoft Office Online and search for templates. I'm sure they have some already set up that you could use or modify as you need. Try "weekly timesheets" "biweekly timesheets".

[Baitman]

What year of Excel are you using? If older upgrade to 2007 it has become easier to use. 

[moose]

my office machine has *gulp* 2000. i believe our other machine has 2003. i dont think i can budget out upgrading to 2007 when i can't even get the funds to upgrade the pc i use!

[Baitman]

Go to Microsoft Office Online and search for templates. I'm sure they have some already set up that you could use or modify as you need. Try "weekly timesheets" "biweekly timesheets".

Moose try what Myelo has sugested you may be able to find something for the 2000 excel. 

I have a tuff time remembering what I did yesterday let alone in 2000

[Reschke]

my office machine has *gulp* 2000. i believe our other machine has 2003. i dont think i can budget out upgrading to 2007 when i can't even get the funds to upgrade the pc i use!

Best Buy has a copy of the student/teacher version for $99 this week.

[Scherf]

First question is - are you tracking their employment days on a spreadsheet too?

The reason I ask is, you don't want to have bits of data hanging around on separate spreadsheets.

Do all your folks work 5-day weeks, or are some of them coming in on some days and not others? If folks earn leave but don't take it, do they lose it? Gets pretty complicated pretty quickly.

Really, what you're looking at is using the TODAY() function (don't forget the parentheses). That automatically updates when you open Excel, any functions which depend on that cell therefore also update.

Either that, or just apply a % to whatever spreadsheet you're using to track their time (assuming that's what you're doing...)

[ROX]

Go to Microsoft Office Online and search for templates. I'm sure they have some already set up that you could use or modify as you need. Try "weekly timesheets" "biweekly timesheets".

Also, be very careful if you are biweekly or bimonthly.  There is a difference.  Every two weeks vs Twice a month.


ROX

[Dichotomy]

I've got one I use to track my own that can probably be easily converted.  PM me with your email address and I'll collaborate on this with you.  I don't do a lot of 'advanced' programming on it but yours can probably be done with straight math. 

[Nwbie]

http://www.usd.edu/trio/tut/excel/

Excel tutorial

[Gunslinger]

What year of Excel are you using? If older upgrade to 2007 it has become easier to use. 

B feakin S

While it may have more umph to it....THEY MOVED ALL THE BUTTONS.  It takes me twice as long to find a function know because they completely changed the way the GUI works.

[Nwbie]

Anyone have a template already setup for inventory?

I have to keep track of about 15 items - in and out with a current balance for each item - been draggin my arse to do it myself - but if someone has one setup already - I can suffer the humility of asking for a copy of the template

I have excel 2003 - and i agree - the 2007 version is too dang fancy - you need to do way to much searching around to figure out the simplest things - they went way to far trying to make it simple- they complicated it more...my 2 cents

[OOZ662]

Now, I have no knowledge of spreadsheets, but it seems like OpenOffice is doing their best at keeping up with Excel, so if you do need to "upgrade," look into it. It's free, afterall.

[DiabloTX]

B feakin S

While it may have more umph to it....THEY MOVED ALL THE BUTTONS.  It takes me twice as long to find a function know because they completely changed the way the GUI works.

Gunny, typically what that means is...it's easier to use once you know how to use it.  lawlz