Thoughts on Excel for HR Vacation Time Tracking 2

[kjv1611]

One thing I've been asked to look at, help at least a little (already made some decent changes), and possibly revamp so long as it's simple enough for the time tracking lady, is our current time off tracking. Where I worked previously, that was all in a database driven system, and so I'm sure that gave more flexibility. However, our time setup is super simple, so Excel has no trouble with it.

What we currently have is 2 separate workbooks, one for summary, and one for detail. The detail item has one sheet per employee, with one row per month, and then about 5 columns for each of the various details.

What I would like to do is somehow convert this to have only ONE detail table, and then build some pivots or queries or formula-driven tables which pull summary-type values from the main detail data table.

One initial thought I had should work, except I'm afraid it may look too complicated to the end user.

My thought is to have some semblance of these columns:[ul]
[li]Month[/li]
[li]Department[/li]
[li]Employee[/li]
[li]Time On Check (this is what the employee had accrued prior to this calculation cycle - this month)[/li]
[li]Time Taken During Month[/li]
[li]Balance (Time on Check - Time Taken)[/li]
[li]Time Earned (this month)[/li]
[li]Paycheck Time (final calculation: Balance + Time Earned)[/li]
[/ul]

I realize the in between step "Balance" may seem overkill, but it's a field they are already using.

What I think would make this seem more difficult for the user, maybe, is how I've got Month and Employee. For their understanding, I could see them probably preferring 12 worksheets (1 for each month) and then a list of employees on each sheet. However, I think that takes away from having a one centralized data source.

Keep in mind, we have a very simple time system here:[ul]
[li]One box of time for vacation/personal/sick/whatever (not holidays)[/li]
[li]No rollover, as your time never expires.[/li]
[li]One paycheck per month[/li]
[li]Time is calculated outside excel for each employee in each month. I was told really quickly they are not interested in changing this step (maybe we can visit down the road)[/li]
[/ul]

I initially thought this one source should work, but I just keep seeing the lady panicking about copying and pasting the employee names from Month1 to Month2 once that month begins.

Can anyone here offer some suggestions? Surely there's some simple/easy way to do this that I'm not thinking of.

Thanks in advance for any thoughts.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

One variation I've thought of would be to have a section of columns for each month, so it's Employees along the left and months along the top. However, when you have 5 data columns, then that gets a bit cumbersome, I think, as you end up with 60 data columns. Also, I think it reduces charting/reporting abilities.

Thanks again for any suggestions.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

Isn't this all part of a time system? So you have time transactions for an employee that they clock in or out on a particular date with straight time, over time, vacation time, holiday time, family leave etc. from the transactions, the elapsed time can easily be calculated for any type of time. A table should not include aggregations. That occurs at report time for a given set of criteria. You could do it with a query or on a sheet (form) with selections for employee and date range, using SUMPRODUCT() quite simply.

Vacation is nothing special datawise.

EmployeeID
TransDate
TimeIn
TimeOut
TransType

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

There is no time system, to date, here. There has just recently been introduced a time system for hourly clocking in and out, but that is not used for tracking time off. Crazy as it sounds, everything is pretty much kept track of on paper and in Excel.

I thought about keeping the calculations out of the table portion, and that would reduce the number of columns. However, I'm not yet sure I can present such a format to the end user that will be acceptable. I am willing to give keeping calculations out of the table a shot, but the actual data values entered will be calculations that are just not calculated by Excel (sounds silly, I know).

Keep in mind, I've also on another occasion noticed her typing away at a paper-feed calculator instead of using Excel for some other calculations (I would not be shocked to figure out that she does the time calculations using the calcular) She's been doing her job for somewhere north of 30 years, has never had to use technology for these functions apparently, and she's generally averse to change. So it makes it more challenging to consider a suggestion.

So for now, all Excel is used for is final numbers, more or less. Here's a brief overview of the process as I understand it:
[ol][li]Everyone fills out a time sheet each week (hourly and salary alike) - this is primarily to give HR Lady something to gather "vacation time" usage from to fill in.[/li]
[li]HR Lady adds up all time off usage for each employee, and plugs it into that employee's Worksheet in the "Detail" workbook.[/li]
[li]For some reason, of which I haven't seen nor understood yet, she prints out all those worksheets.[/li]
[li]Then she gets some final calculations from the Summary workbook which points to the Detail workbook.[/li][/ol]

It's been a mind blowing experience switching to this type of setup.

The company does have Quickbooks and another system used for accounting, but they do not use that at all for "vacation" time tracking. I would have thought Quickbooks has this ability built in, myself, but my exposure to Quickbooks has only been helping a couple other really small businesses with technical issues, and installing it on machines here.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

So there's already a "system" on paper.

If you propose a solution like you have outlined, it will become a maintenance nightmare when someone sort of likes the idea (bad as it is, cuz they don't understand the problems that can occur) and then they want it modified, ON HOW MANY EMPLOYEE's SHEETS, or they want the system expanded to include other kinds of time recording? Oh, yes, Steve knows how do do Excel macro stuff, so you get the job of fixing a horrible design. Been there. Have spent hours doing what should have taken minutes.

Unless they're willing to do it right, I wouldn't get into the middle. Paper is better than a poorly designed "automated" system, IMNSHO.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

Here's an accrual time sheet I made years ago for myself. The biggest problem is the coding to add additional time due to different conditions. Where I work, you start out with 12 vacation days, and get bonus days after 5, 10 and 20 years of service (bonus days +3, +8 and +13) on your accrual date. Additionally, you can accrue up to 3 personal days, with any extra time going over to sick time. Finally, you get up to 9 sick days. Fortunately, you never lose any time, so the numbers just keep increasing.

I don't know if it will help, but it's a start.

 

[SkipVought]

So what's the reason for "them" asking you "to look at, help at least a little?"

Is the HR lady getting bogged down with "2 separate workbooks, one for summary, and one for detail. The detail item has one sheet per employee, with one row per month, and then about 5 columns for each of the various details?" Too much calculator work every month?

Seems that the implication is to somehow put the manual system into a computer to provide some sort of help for the gal. Aren't computers supposed to be better in some way? It's almost like there magic in that silver box.

Maybe you need to be an advocate for some meaningful change, if that's possible. But doing what I think you're being asked to do, isn't going to help them. It might make things worse.

I just "helped" a friend who's running a fledgling cottage candy business. They have 7 products with 3 packaging options for each (well it turned out there were sometimes 4, oh yes 5). I suggested a proper table and some other related things, but no, he needed it in a day and his foreman needs to be able to see what's been ordered for the next 7 days. So to get something quick I caved in and designed a one sheet, one line per order, product/size across the top, total for this week, next week visible above. Works OK for the Christmas rush of orders. He was pleased. But come January, he's gonna come looking for me to fix things, cuz getting other meaningful aggregations are gonna be a bear! I actually wrote a simple routine to convert it all to a proper table, so if and when he pops up, I'll have a solution and some follow on.

The tyranny of the immediate and the ignorance of the possibilities.





Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Skip, you've obviously not been to the movies recently or been watching TV!!

Of course computers are magic.

Oh, and let's not forget the teenage nerd who gets past any firewall in a matter of (tension-filled) minutes, "I'm in!!"

Ha ha!

Many thanks,
D€$

 

[SkipVought]

War Games

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Skip,
Yeah, I'd LOVE to be able to work with the entire process to make it more smooth.

It started with a minor change they needed where changing from one time tracking system to another used HH:MM format instead of HH and decimal format for time tracking. So I took care of the immediate on that without changing anything. The next definite needed change will just be one more step in that same regard - no code, no formulas, really.

Yeah, I'm inclined to think that the simplest thing will end up being to just help the little bits, but leave it otherwise as is. I know I have loads of bigger fish to fry anyway. I just kept thinking SURELY there is something I'm missing to make this work super simple.

I did consider building a user input form, but as you say, I'll end up having to put in loads of work down the line because she'll desire to have something changed.

It may be something we can perhaps look at more seriously down the road. If nothing else, I think the one super easy change would be to move the "Summary" sheet from it's workbook into the other workbook, so there are no external links to worry about. That should definitely simplify things, should be easier to maintain, and should provide much less end-user shock. One issue that I noticed when they asked for my help just the other day is that the management person had accidentally changed the formulas to look at a copy from their personal Outlook email temporary files rather than the actual "data source" file. So removing the 2 files setup should eliminate that issue.

I'll be working with the administrative/HR lady on the issue likely early or mid January. For the time being, I am 99.999999% certain we'll just fix up what needs be fixed, hopefully merge 2 files to 1, and call it a day. From what I've seen so far, I'm sure that'll go over best anyway.

Thanks a ton for the real life scenarios you've dealt with. It always helps to understand things when you can refer to an actual event.

zelgar,

Thanks for sharing. I tracked my own time in similar (though different) fashion at my previous employer. However, that sort of setup gets quickly messy when you're tracking 60 people instead of 1.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kendue]

I also have a version of a vacation accrual for your perusal. It is set up for a $$ basis of vacation cost, but could be altered to use time in hours instead.

additional columns can be added for department breakdown, etc.

Maybe, if you get enough input, the correct answer will make itself known.

 

[kjv1611]

kendue,

Thanks for the suggestion. Actually, that falls in line with one of the ideas I considered, and mentioned above in my post, 20 Dec 16 15:40


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kendue]

kjv,

When I saw that post you referenced, it reminded me of the vacation accrual sheet I had already designed several years back. If you decided on that type layout, I figured it would give you a good starting point to try out some sample data rather than having to start completely from scratch.

 

[SkipVought]

Good for one person. Bad for a company record keeping system.

Pay me now or really pay me later.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kendue]

Skip,

I'm sure you could design a very sophisticated system to handle the information in question, but in kjv's real world situation, it seems that a K.I.S.S. approach might be more advantageous, at least for the time being.

If the person working with the system either cannot understand how to use it, or will not take the time to learn it properly, you will have a fail.

 

[SkipVought]

I understand. You just need to know that it is likely that a price will be paid downstream.

The OP seems to be leaning toward consolidating into one workbook with one sheet per employee perhaps.

If that needs to be the next step, I'd surely write a routine to produce a proper table to be the source for the necessary aggregations. That would at least give you a leg up on reporting and analysis.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

LOL
Just now looking back at this.

Skip,

Yeah, that was what I was initially headed for. I didn't think about adding a script to just compile everything to one table for analysis. Easy enough for me to do, and it would be better in that I wouldn't have to bother the end user with changing their process much. Thanks for that thought. I might revisit for that piece in the next week, depending upon availability from other non-related items.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

That's what I did for the fledgling candy manufacturer. It simply recreates the table each time it runs. Didn't try to design an update at this point.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Since the system here is so simple and basically does not change, if I do put something in place, it should be able to "just work" for a long time. I already built a piece or two, at least, for what I'd need anyway, when I was first looking at consolidating it to revamp the setup.

Thanks for the inspiration, Skip!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Hope it helps in the overall usability.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]