PDA

View Full Version : Annual Time Sheet (including flexi/TOIL) - Enter Negative Time/hours



mykal66
04-05-2014, 05:44 AM
I've created the attached time sheet for work(the print & email buttons don't work yet) which will be used for a complete year (i've only added January and February so far).

Flexi/Toil is automatically carried over to following month including any negative hours (hours owed). As the template will be set up for an individual at the start of each year there may be the need to manually carry flexi/toil over from previous year which is fin if it's a plus value but i cannot manually enter a negative value into cell K6 in January - can anyone help please?

I've attached the time sheet so anyone can download/use it if it's of any use to them too (it will just need January copied into new tabs for remaining months). Only cells shaded grey need to be filled in and the the time sheet has the functions listed below:

works out daily hours to be worked
toil/flexi carried over to each week/month
non working weeks not taken into account/previous toils carries over
cell backgrounds changes to green or red if actual hours worked or more or less than contracted hours (easy for HR Admin to spot at a glance)

I f anyone can help me with the negative value issue in January that would be great

Thank you

SamT
04-05-2014, 11:11 AM
That is because Excel is smart enuff to know that there ain't no such thing as time travel, so you can't go back in time. :banghead:

The only way to manually enter a negative time value is to enter it as a negative SerialTime value. A SerialTime value is a decimal value between 0 and 1 where one minute = 1/(24*60).

One alternative is to make a pop-up box that shows whenever someone selects January!K6, then let the code convert whatever time is entered in the box into a serial time and place that value in K6.

Before I get into that, let me see if I understand how this will be used...

Every month the managers open all the workbooks for their team, find the new month tab, fill out D3:D6, and repair all the dates in C10:C68. In every book! Figure 6 minutes per employee per month

Every morning the managers must open every book and enter a start time (in a specific and unnatural format,) or enter a code for why they are absent, then close and save the book and open the next. Every day at lunch, they must repeat the process for everybody who took a lunch, then, at the end of the day, after the crew is all gone, they must again repeat the tedious process of recording all the quit times. Repeat after, (or the night before,) Bank Holidays. Figure 2 minutes per person times three incidents per day.

In other words, a manager must spend about 2 hours per month per employee on timekeeping. 20 employees = a weeks manager's pay for timekeeping. Every month! Using Excel VBA and a VBA UserForm, everything can be automated.

Imagine...
A typical day, everybody works from normal starting time to normal quitting time and takes the normal lunch: In the morning, manager opens the master timekeeping book, sees that all is normal, clicks the button labeled "Done". The book records everybody as showing up on time and closes. In the evening, he repeats the process: Open... Click... quit.

Oops! SamT didn't show, book is kept open, manager calls SamT' home #, Mrs T says, "He's on the way." Ten minutes later Sam walks in. Manager selects "SamT" from a list, clicks "Enter Current Time," clicks "Done," (or "Save," if Mykal is also late.) Goes back to manager work.

Everything can be automated or reduced to a few clicks and/or keyboard characters.

Figure an average of 6 minutes per day per manager per 20 employees. The reverse of the figures above.

The most important consideration, because business is about money, and this is a Capital Investment... It will take about 3 paid weeks of your time, with our help, to get you up to speed to develop a rough prototype, that must be used for 5 manager weeks, (average 3hrs/wk bug reporting,) in order to find the most frequently encountered bugs. Only then, another 2 weeks of your paid time to get the first production version distributed throughout the company.

So. What do you want to do now?

Bob Phillips
04-05-2014, 04:36 PM
You get negative time by derivation in a formula, as you can see in your February example. So you can either link Jan to the other workbook, or have another cell (say M2 for the sake of this discussion) with the number of hours as a decimal figure, which can be negative, and use =M2/24 in K6.

SamT
04-05-2014, 04:42 PM
Mykal,

Xld is better than I am at this, He will take care of you.

mykal66
04-05-2014, 09:01 PM
Sam

Thanks for your help / advice and words of encouragement! :banghead:

The time sheet was just replacing one that already exist but requires much more work for the user to complete - it's not even my job to rework it but i always get asked to fix the sheets when users enter wrong format or overwrite the formulas.

The team enter their own times and every month someone else in the team manually creates a new sheet for every one, transferring over any toil/flexi. The sheet would be set up for entire year as template before use so no one would have to fix the dates ever month, I was just trying to make things easier for the team and girl that manually sets up a the new time sheet every month.

mykal66
04-05-2014, 09:02 PM
Many thanks XLD - i will give this a shot

Bob Phillips
04-06-2014, 02:38 AM
If it is that time consuming to create new sheets, why don't you add some VBA to automate it. There are many people here who could help you with it, and add some validation to trap the most common mistakes.