Organizing employee timesheets is a process common in most companies. A lot of us take this task for granted. But for the administrator in charge of this job, it is a hassle and a major time suck. So why not automate it?
The Challenge
It’s easy to get floored from the paperwork generated every day at work. Luckily, Automate has an amazing ability to regulate computer processes that create that paperwork—thus saving time and energy. This comes into play especially with office managers or employees responsible for creating spreadsheets, presentations, Word documents, and emails on a daily, weekly, bi-weekly, or monthly basis.
For today’s entry we are going to look at a process that is common in most companies: employee timesheets. Without them, we don’t get paid; and for most, this is a task that we take for granted. But for the administrator in charge of this job, it is a hassle and major time suck.
Let’s go over a basic example of what’s needed: The current method requires someone to manually create an Excel spreadsheet (twice a month for the two pay periods). This spreadsheet serves as the timesheet for all non-exempt employees, and it must contain the numerical values for the days of the month (1,2,3….30 etc.) broken up into two different pay periods. This could be straightforward enough, except the person who creates this timesheet must also take into account weekends and paid company holidays. And if that holiday happens to fall within the current date range, then he or she must manually input the hours worked. (See below.)
From the image above we can see that the 27th is Memorial Day, which is recognized as a holiday for most U.S. companies. Therefore, whoever is creating this timesheet must manually type an 8 and an H in the appropriate cells corresponding to May 27th. The goal of automation is to remove the user from needing to do these small types of changes to the document. This will save time and allow for more office tasks to be accomplished.
Given this timesheet project, there are some conditions that might be viewed as a challenge for automation, but with Automate, the process is very straight forward. This is because Automate works with Excel seamlessly and with advanced functionality.
Project Requirements
- Maintain overall look (within limits) used with previous timesheets.
- Automatically populate current pay period in appropriate cells for any given month.
- Email non-exempt employees the new template and include the due date for sending it back to HR. (Make sure the due date specified in the email doesn’t land on a weekend.)
- Excel should automatically fill in an 8 and H in two different corresponding columns if a holiday exists and the pay period calls for it (this should also work for multiple holidays in any given pay period).
In this example, the timesheet is due three days before the end of the pay period; and if that day falls on a Saturday or Sunday, then the due date is placed on the preceding Friday. The due date must be populated automatically through an email sent out on the 1st and 16th of every month, four days before payday.
Sample Email Output (to employees) for the month of June (2013):
Mark your calendars:
- Timesheets for June 1st to the 15th will be due on Wednesday, June 12th, 2013 @12pm.
- Timesheets for June 16th to the 30th will be due on Friday, June 28th, 2013 @12pm.
Enjoy the rest of your day! |
With the exception of February (which has 28 days), there are four months (April, June, September, November) that contain 30 days and seven months (January, March, May, July, August, October, December) that contain 31 days. This Automate task must be able to determine the pay-period range for each month automatically given the number of days in the month. (Pay period 1 would always be the 1st-15th for either 28, 30 or 31 total days, but pay period 2 could be the 16th-28th,16th-30th or 16th-31s.)
Holiday conditions require Automate to be able to manage both single, as well as multiple occurrences in any given month. November and December are classic examples of this when most U.S companies will be celebrating the Thanksgiving holiday, as well as the day after Thanksgiving. And in December, there are a total of three holidays observed during the second pay period of the month (Christmas Eve, Christmas Day, and New Year’s Eve).
The Solution
The ideal solution for this project involves an Excel timesheet that automatically populates everything the user needs twice a month. As such, the timesheet will have one pay period value which changes twice a month. The Excel document will automatically populate values (8 under REGULAR HOURS WORKED and H under TYPE OF LEAVE) during holidays that correspond to the numbered days. The employees themselves are responsible for filling in their hours worked, but the holiday field input is the responsibility of the automated process.
The days of the month will be located on the left side of the spreadsheet and will correspond and change according to the pay period (1 or 2) with a range between 1-15 or 16-28, 30, or 31. The days of the week (Sunday through Saturday) will also populate and match the numbered day associated in the cell next to them. (This visual aid helps the employees fill in the correct values in the correct fields.)
The Design
Two workflows are being implemented: The first will run the process and output the desired timesheet for the given month. The end of the first process will create a clear message for review.
For the first workflow there will be two schedule triggers used—one that identifies the 1st of the month and one that identifies the 16th. The process will run on both days of the month in question.
This first Workflow has three main tasks—the first sets ups and establishes the variables needed for the program to run successfully; the second uses logic and established values from the necessary data sources in order to find the dates and numbers needed for the correct input into the Excel rows of the document; and the third Task simply sends a notification email of the completion of the process.
Once this Workflow has completed its run through, a timesheet with the correct date will automatically save in a folder specified by the user for easy access. There will also be a notification email sent out to the specified employee responsible for managing the timesheets. This email serves to remind the employee that the timesheet is ready for review.
Sample Output Notification with Hot Key command:
The Timesheet for May 16-31 is now available for review.
The file is located on \\NETWORK\Public\Timesheet\2013.
Please review the timesheet and hit CTRL+ALT+U to send it out to all the non-exempt employees. |
The second Workflow will run a separate series of Tasks and output the emails as the individual hits a specified hot key combination. The hot key function automatically runs the Workflow when the individual wants it to run.
Automating this timesheet project proved successful mainly because Automate communicates with Excel so efficiently. If you can plan out your Excel Actions, then Automate can take care of them step by step.
Applies to Automate 10
