Help with a Execel program

[Peppered]

I will be making a schedule for work. what I would like is to be able to keep count of vacation days taken and hours taken in a running total for each person. I used the count function to keep a total of each V (vacation ) used. I also need to keep track of hours taken because we have to split hours taken some times.

It would look something like this V12 , 12, 12, V8 thse would be in Excel cell's V12 shows 12hrs vacation 12 shows 12 hrs worked V8 shows 8 hours vacation.

So in the running total it should have 20.

Many Thanks

 

[Markbnj]

I have no idea what you're asking, or little enough idea that it is functionally the same as no idea.

 

[piasabird]

Is this one person per sheet, everyone on the same sheet, how is the information getting there? There could be sick hours, excused hours, personal time, work, vacation, Holiday, Inclement weather, External Conference/Meeting. What exactly is the problem? One colum for V one colum for work and then cross put with Sum. Why did you use count? What does the data look like?

"ID Person Hrs W hrs V "
"99 xxxxx,xxx 4 4 sum "
" sum sum sum "

Sounds like you have "WWWWVVVV" and you are counting the W's.

 

[Peppered]

Is this one person per sheet, everyone on the same sheet, how is the information getting there? There could be sick hours, excused hours, personal time, work, vacation, Holiday, Inclement weather, External Conference/Meeting. What exactly is the problem? One colum for V one colum for work and then cross put with Sum. Why did you use count? What does the data look like?

"ID Person Hrs W hrs V "
"99 xxxxx,xxx 4 4 sum "
" sum sum sum "

Sounds like you have "WWWWVVVV" and you are counting the W's.

No it will be 98 Personel on one sheet and yes I am counting hours worked and vacation hours used over a years time

 

[Krioni]

Have you considered something other than excel?

I have to believe there are some applications out there that are cheap or free that will give you a better solution than writing this stuff into excel. Not to mention, you'll be in deep do-do if you do this and the excel file gets corrupted or something... so you you're going to need a good backup plan too.

 

[Evadman]

I am so confused as to the goals of the sheet. Can you expound please?

 

[Peppered]

I may be locked into using excel. I have put in for a job that requires me to make and manage the schedule for Maint. And Operations in a Plant that runs 24/7.
There is a Schedule out already but everyone hates it. It is a Excel format.
My goal was to keep track of Vacation hours, Sick time, Tardy, Personnel time used and so on.
At this point the Supervisor has to enter data onto different form to keep track I wanted to make a way to pull the info from the schedule and keep a running total.

If there is a better and free way to do it I am all for it. But I have to show the Production Manager it is better too.

 

[beginner99]

http://www.capterra.com/attendance-tracking-software

 

[PowerEngineer]

Oh man... You're in for a fun time!

I know exactly what you are talking about because I also had to come up with a scheduling and timekeeping system for a 7/24 operation (12-hr shifts) using Excel. We've been using it now for several years to create work schedules, track actual hours worked (and vacation, sick, etc.), produce monthly reports and calculate bonuses.

I'm a really big Excel fan, but even I have to admit it's not the best tool for this kind of job. On the other hand, I couldn't find any off-the-shelf timekeeping software packages that really handled 7/24 scheduling while allowing us to configure all the idiosyncracies (e.g. relief schedules, etc.) we needed. It took me about two years to capture and program all the odd combinations of scheduled and actual work that can happen.

If it's any encouragement, I use roughly the same kind of number-letter entries to enter daily deviations from the work schedule (e.g. "4V" is four hours of vacation leaving eight hours of normal work). IMHO this isn't something that is doable using just cell formulas. I have a sizable (several hundred line) set of VB macros that do the lion's share of the processing for timekeeping.

Good luck!

 

[jolancer]

Im not the best but I have become very very very familiar with Excel. and not sure if some have miss-interpreted your question, but I would believe there is a big difference between have Excel create the schedule for you, or use Excel to just manually input the Hours like it sounds like you described. Because of how familiar Iv become with it, I believe both is more then doable with Excel, however the latter is much easyer.

From the sound of it you dont seem to familiar with the variety of functions.. I have become familiar with Excel but not VB macro's. So I dont know how they affect Excel on a processing level, I'd imagine VB is more efficent(if done right) but I wont comment on that at all because im not qualified for VB.

For starters though, I would recomend you google and learn Volatile Functions for excel.. So you can minimize the use of them, especialy if used with arrays. You'd be saprized how many non-volatile alternatives there are, even if you dont know VB as I. If you google these functions you'l realize quickly why i mentioned it first, so i wont bother explaining.

next depending on how much data or different peoples hrs in your example you want mixed on the same sheet you can create a chart anywhere, up top, another sheet, etc... As long as u keep the same fields of columns in order and don't leave spaces when filling out the data , you can have the data automatically total'd and sorted in the chart. but without VB macro's or seperating each person to a different sheet... you'l need to do either of these things = 1) learn Array formula's (=sumproduct() and (=if()"ctrl+shift+enter") 2)learn arrays and then break down the individual process's of it into its own function and have each step of the array's functions(not arrays anymore) fill the column's beside the Data columns or on a seperate sheet, and use these formula columns as the 'markers' or 'reference' cells for the chart formula's. 3) learn VB

only reason i mention 2) is because all arrays are tecknically volatile.

EDIT: oh yea just fyi the most common formula i'v used to break down arrays incorperate usually =IF/SUMIF/INDEX/VLOOKUP/COUNTIF/ and a slew of other basic ones.

EDIT again: and oh just fyi for anyone that it interests, and doesnt know or care at this point in learning VB.... If you go by ex.2) even though it drastically increases the physical size of the Data Sheet... The proccessing time of the data when modified is nil compaired to using a volatile function or array(if your data sheet/columns are large or contain a lot of data)

 

[veri745]

*wrong thread, sorry*

 

[Peppered]

I want to thank everyone so much for all the info. I would of replied sooner but I have been working anywhere from 48 to 88 hrs of overtime ( 2 week schedule pay ) for the last month and a half.

It is doubtful that I will get the job due to it seems to be made for some one that is about to lose the job he has, do to there is no need for what he was doing. If this happens he will go back to being my supervisor or get the same job I am trying to get.
I have a lot better background for the job than he has but being better qualified doesn’t mean you will get the job.

I will have some time off this Sunday so I hope to take that time and you the advice I have seen, and make a mock Schedule to use for a example once I have the interview.

Thanks again all