Advanced Timesheet

**DaKohlmeyer** · 05-13-2008, 06:41 PM

I'm looking for some assistance on a timesheet I've been slowly fiddling with over the past several months.

My problems are as follows:
1)First a smaller but perhaps something that should be done first is that I'd like to change all the data formats from two cell in and out times (ie: 4 in one cell and 30 in the next) into a much nicer system where it is just one cell of 4:30. This of course would probably be required before adding in the buttons to input the current time.

2)In the location marked by the Clock In/Out button I would like a button that inputs the current time into the chart into the next available open field from the options of Time-In, Break-Out, Break-In, and Time-Out. Then of course I would need that skip button for days I didn't work and weekends. I don't know how to create a function to grab the current time or how to copy just the data into a field. And I certainly don't know how to create this list of available fields into which the data could be placed.

3)As you will notice the blue bars under each of the days I would also like to have a progress bar that counts up the hours I work and compares that to the current time so that when the bar is full I know I have worked a full 8 hour day with a label denoting the percentage of 8 hours that I worked. Currently I have it set up as a bar graph but that keeps changing scale and so I don't know what the best method is, maybe just create one cell for every half hour and color it in as the time passes. Any suggestions would be great!

4) In addition, however a bit easier, is that I would like to expand the worksheet to an entire year. So basically there would be 26 worksheets of bi-weekly data. Then I would like to have a cover page with the typical fields such as the employees pay, the number of vacation days (both totaled and remaining), sick time(totaled) and perhaps some sort of a summary of statistics as too how many hours were worked in each month.

5) Finally a bit more conceptual question has to do with grouping the projects. I would like to add some additional columns in order to keep track of how many of the hours spent on each project went into each of these 16 categories:

Programming, Site and Environmental Analysis, Schematic Design, Engineering Systems Coordination, Building Cost Analysis, Code Research, Design Development, Construction Documents, Specifications & Materials Research, Document Checking & Coordination, Bidding & Contract Negotiation, Construction Phase—Office, Construction Phase—Observation, Project Management, Office Management, Professional and Community Service.

The question then is, is there a way to keep track of the number of hours that went into a specific category for each project without adding 16 extra columns? I'd like to keep it simple and have it easily removable for the employees who don't need to keep track of this information.

If anybody has any suggestions as to how I can go about doing any of these things I would be greatly appreciative. In addition, I have no VB abilities so if that is the only way to complete this I would appreciate any help. Furthermore, if anybody has some fun ideas for making the timesheet even better I'd love to hear them. Thanks so very much for any assistance.

**DaKohlmeyer** · 05-20-2008, 06:26 PM

Ok so I've made a LOT of progress.

1)I've changed all the times from decimal to time stamp format.
2)I've added a button that inputs the current time into the current cell.
4)I've expanded the worksheet into a year long book with a coverpage and a bunch of summary data. Some is just for experimentation, I'm not sure if the formulas are correct.

However, I still have some problems.
First I need some help with my VBA, take a look at it.
I think it is close but I can't make it work.
Obviously I've got pages 1-15, 1-30, 2-15, etc. but the current macro just goes from page to page right to the end.
I'd like to just push a button and it to grab the current date and bring the workbook to the correct page. IE:if the date was 5/20 and the user clicked the button it would take them to the page 5/30 because that pay period ends at 5/30.

Here's the code:

Please Login or Register  to view this content.

3) I'd like some way to make a graph mapping the current and remaining time but not sure how.

5)And of course I'm still looking for help on this one.

Thanks for any assistance.

**shg** · 05-20-2008, 06:31 PM

I've added a button that inputs the current time into the current cell.

Ctrl+Shift+: enters the current time also.

A date constant in VBA looks like this: #5/20/2008#

Please read the Forum Rules and then wrap your code with Code Tags.

**shg** · 05-20-2008, 06:39 PM

You might use this instead:

Please Login or Register  to view this content.

**DaKohlmeyer** · 05-21-2008, 05:49 PM

woah, I have no clue how that little statement works at all but I know it works perfectly! Thanks

**shg** · 05-21-2008, 05:53 PM

C'mon, take a look. You expect to be helping out here someday, right?

It selects the tab whose name is the month number followed by a hyphen (that's the format(date, "m-") ), and either "15" or "30" depending on today's date.

**DaKohlmeyer** · 07-28-2008, 04:49 PM

Ladies and Gentlemen,
I present to you, the new! the improved! Timesheet!! Tadaaa
Now stick with me because there’s a lot of stuff to cover!
It is all set up to be protected, the formula cells are locked all you have to do is pick a password and set up protection, then save as a macro enabled template and its all set to be used for your own company.

1)Essentially what you see in the beginning is a summary page, this is where the magic begins! You’ll find the current date in the upper left just to help you remember what day it is. First insert your name into the Upper right, then you’ll want to verify that the current year is displayed under your name. This will help in your personal record keeping and is essential to the date sensitive formulas in the time sheets.
Next you’ll see the yearly chart. The blue fields indicate information that is needed and the green fields are what is returned. First you’ll notice that the past time periods have been darken in to help you find the current period, if there’s any doubts just glance back to the current date.
Next go ahead and insert your current pay rate for a calculation of each paycheck. Of course totaled to provide a yearly amount. This will be calculated from totals of each of the timesheets you’ll fill in later. Then as a little bonus you will also notice that your Vacation and Personal Time is also totaled up across all of your timesheets and displayed for your convenience. Simply fill in your allotted number of hours for each and it will automatically keep track of how many you have remaining! Of course for your convenience.

2)Now we’ll jump into the cool parts. Click on the button in the upper left labeled “Current Period”
This of course will take you directly to the sheet corresponding to the current time period. *Applause*
I’m sure you’ll recognize the original timesheet, but now it’s been marked up to make it easier to read and fill in. What you’ll notice here is that 1) your name is automatically filled in (from the summary sheet), 2) the current date is displayed in order to make it easy for you to find the current column on your timesheet, and 3) the end of pay period date is displayed in the upper left. This date is automatically calculated based on the year that was input on the summary page. It then cascades from timesheet to timesheet displaying either the 15th or the last day of the month depending on which is needed.
Go ahead and try it, this will make this timesheet work for years and years to come!
*Applause*
First notice the three buttons under the pay period date. There’s the clock In/Out button which we’ll cover in a bit. The Summary button which will take you back to the summary page, and the clear data button in order to clear the timesheet for the next year. Now let’s scroll to the right. You’ll find a chart with a row for each day of the pay period. This of course automatically updates from month to month. Simply place your cursor into the correct cell to insert the timestamp. Then you can either insert the time (be sure to get the AM/PM stamp correct!) or click on the Clock In/Out button on the left to insert the current time. This allows you to come into work and click one button and it’ll take care of the rest. Insert times for TimeIn, TimeOut and if you took a break insert your Break Out and Break In times and it will automatically calculate your hours on break, hours of work, and the decimal value of hours at work which you can break up into projects and insert into your timesheet on the left. This will hopefully be easy enough to do that people can do it each day instead of trying to remember the past two weeks, thus increasing accuracy and simplicity! You’ll also notice that underneath each of the days, it will calculate, based on your break time and your clockin time, what time you should leave if you wish to get in 8 hours a day.
*Applause*
Back to the left side you’ll recognize the typical form to insert your project, sub, phase, project name, and amount of hours. You’ll also want to insert the applicable hours into the holiday, vacation, and personal time off fields so the summary page can keep track of those for you. Then notice that at the bottom of the page you’ll see how many hours you should put in to get the full hours for the week. And finally you can type anything in the Remarks field.
*Applause*

Thanks for reading through all the improvements, hope it works well for you all and everybody enjoys it. Thanks for everyone's help, I got some help from you all so I figured I'd return the final result.
The only thing I couldn't get in is some sort of tax tracking fields on the summary page to calculate how much of each paycheck went to each tax.