Timesheet Hours in Excel

“You can’t use a timesheet and a spreadsheet together!” That used to be the old saying…

Not anymore. (see video below)

Timesheets and spreadsheets have finally made up. They’re friends again with the new Excel® Add-on named XLST. XLST pulls timesheet data directly from your Standard Time timesheet and puts it into your Excel spreadsheet. This video shows how. Essentially, you’re using Excel formulas to query the ST database, and putting the results into cells. Each cell uses one formula. The results of that formula come from the ST database. You supply parameters for each cell formula, which results in different data for each cell.

For example: Cell A1 might use employee ‘Buzz’ while A2 might use ‘Fred’. Since you’re supplying different data to the same formula, the results will change. That means you can build tables of data in a spreadsheet. Use those cells for pivot tables in Excel.

The data is always hot. You simply open the Excel spreadsheet and the Add-on pulls data in from the Standard Time database.

Go ahead and take a look at the video. It’s pretty nice.

 

Excel Add-in for Timesheets

Have you ever wanted to pull your time and expense data into Excel for analysis? Now you can, with the XLST Excel Add-in. (scroll down for a video)

XLST is a new Excel add-in that pulls timesheet and project management data from your Standard Time® timesheet, and puts the results into Excel spreadsheets. It uses Excel formulas to extract timesheet data, and places the results into a single cell. In fact, the XLST functions act exactly like any other Excel function. They take “parameters” from other cells and use that data to get results.

What kinds of results can you get?

You can query the Standard Time database for all the actual work entered by employees. Or, you could query for all the expenses. Or all time off taken by employees. Or the hours available for time off.

Here is a partial list, which might spark your imagination:

  1. XLSTActualWork
  2. XLSTActualSalaryCost
  3. XLSTActualClientCost
  4. XLSTExpenseTotal
  5. XLSTTimeOffHours
  6. XLSTTimeOffAvailable
  7. XLSTScheduledHours
  8. XLSTExceedDailySchedule
  9. XLSTExceedWeeklySchedule
  10. XLSTProjectTaskScheduledHours
  11. XLSTProjectTaskDuration
  12. XLSTProjectTaskActualWork
  13. XLSTProjectTaskPercentComplete
  14. XLSTProjectTaskModified
  15. XLSTPayPeriodStart
  16. XLSTPayPeriodEnd
  17. XLSTEmployeeTimesheetApproved
  18. XLSTEmployeeTimesheetSubmitted
  19. XLSTEmployeeExpensesApproved
  20. XLSTProjectClientRate
  21. XLSTProjectSalaryRate
  22. XLSTEmployeeClientRate
  23. XLSTEmployeeSalaryRate
  24. XLSTCategoryClientRate
  25. XLSTCategorySalaryRate
  26. XLSTProjectManager

Just click in an empty cell and then click the function icon near the formula line. Then choose XLST as the category. You’ll see all the functions listed above. Each one takes different parameters. Many parameters are optional, so you can quickly get results right away.

You must start by downloading Standard Time from the stdtime.com website, and then connecting it to SQL Server. XLST requires SQL Server or SQL Express. Once ST is connected to SQL, then you can download and install XLST.

Watch the video to get started.

 

Double the Value of Your Timesheet

Information collected in your timesheet can double the value you get from it. Especially if you are only using the basics of client billing or employee payroll. Those things are great, but they are only half what you can get.

Check this video out.

Lingering behind your timesheet is a wealth of new information. It’s behind the “Project Tasks” tab. Your timesheet is feeding information to project tasks every time you enter hours or start a timer. And the information that is collected is completely free.

That is to say… completely free of managing computations like costs, percent complete, and budgets. For example: as soon as you receive an email notification that your project has reached 90% of its budget, you’ll understand “free.” You didn’t have to do anything to get that; it just happened for free.

Microsoft Project Syncs with Timesheet

Microsoft Project can automatically sync with Standard Time®. You set it and forget it!

Watch this video for the description of a little console app that syncs MPP files with your timesheet. You specify the input settings in a batch file or scheduler. The console app will run when instructed to do so. And when it does, it syncs the tasks and actual work with your timesheet.

The timesheet it works with is Standard Time. You’ll see the MS Project tasks down on your timesheet, and you’ll see employee hours up in the Task Usage view in MS Project. That sync will occur as many times each day as you want. Just put it on a scheduler and let it run.

Find the scheduler here:

  1. Open the Control Panel
  2. Open System and Security
  3. Open Administrative Tools
  4. Open Schedule Tasks

 

Define Consulting Utilization Rate

I’m scheduled for 40 hours this week, but I’ll probably work closer to 90. If those are billable hours, then that over 200% utilization! Awesome! Unsustainable, but awesome.

Define Consulting Utilization Rate: The percentage of scheduled hours spent on client billable work.

A 200% utilization rate is pretty amazing, and also completely unsustainable over the long run. That is why Standard Time® forces you to choose a date range when examining the utilization rates. It’s not going to be the same for every week, or every month, or even for ever year. It changes all the time, depending on how much time you spend on client jobs.

How to calculate utilization rate: Divide client billable hours by scheduled hours. The results is a percentage of utilization.

Employee utilization is also closely connected to effective billing rates. If you are scheduled for 40 hours, but only work 20, then your billable amount must be spread over the full 40. In that case, it’s 50% of what you got. In other words, for every scheduled hour, you got only 50% of your total billable rate.

How to calculate effective billing rate: Multiple total client revenue by utilization percentage.

Crazy stuff? Don’t worry; there’s a simple report in ST that calculates all this. Just click it and go. But you may not want to know the results. Just kidding… you’ll be just fine, and probably happy that you knew.

Define Comp Time, and Time Off in Lieu

Honey, you have to work again? Yes, but I get all those comp time hours. We’ll take a nice vacation to the south of France. (Hint, hint) That pretty-much sums up compensatory time, or time off in lieu.

Define Compensatory Time: Employee time off given in exchange for overtime.

Getting comp time always feels good. It’s like a free holiday. Of course everyone is expected to work a little extra from time to time. So that’s only natural. But getting a three-day weekend is golden!

But what software are you using to keep track of this? Let’s say you have fifty employees. Each one gets comp time at least twice a year. That’s one hundred special-case time off situations you have to juggle. How do you handle that? Is there some software to do it? Or do you use a spreadsheet?

Did you know that your project tracking app has it? Yep, ST can track PTO and comp time. Watch the video below, and then click the link at the end. You will like this almost as much as a three-day weekend.  🙂

Define Invoice Milestone

Invoice Milestone: what is that? This video helps to explain that question. It’s actually a pretty simple concept. You sign a contract where the client pays you at agreed-upon intervals. Those intervals are called milestones. That is the only time you can bill the client.

Define Invoice Milestone: A date when a client has authorized a certain billable amount to be invoiced.

You agree to a fixed bid for a project and the dates when you’re allowed to bill the client. The client knows what to expect, and you know when you’ll be paid, and both parties know the exact amount it will be. Often the terms of these fixed-price contracts are one-third up front, one-third in the middle, and one-third at the end.  These kinds of deals are signed all the time. So Standard Time® supports them with invoice milestones.

You set up the invoice milestones for a project in advanced. You determine exactly how much each milestone is to be charged. And then you get email notifications when they are coming due.

Sometimes invoice milestones are a percentage of the total. Other times, they are fixed amount. And finally, they are sometimes just a date range that includes all the time and materials for the job.

Watch the video and give this a try on your next project.

Define PTO Accrual

This little video will help explain paid time off or PTO. Scroll down for a look. If you’re tracking vacation or PTO in a spreadsheet, you probably already know there are hundreds of computations that can go wrong. Using paper and pencil is almost impossible.

HR managers appreciate the flexibility of PTO rules from the Standard Time® app. To begin, employees have their banks for hours. There is a bank for vacation. A bank for personal time. And banks for every other kind of time off, like sick, training, maternity leave, and jury duty.

Now that you have your banks of hours set up to represent the exact number of hours available to each employee, and for each kind of time off you should set up some rules for accrual. Every employee accrues (earns) hours periodically. The longer they work, the more they earn. And then they use those hours on their own time schedule.

Set up PTO accruals earnings weekly, monthly, semimonthly, or yearly. As time ticks by, the hours add up. But what if too many add up? You have an anti-hording device to cut off too many hours. That enforces your company “use it or lose it” policy.

Watch the little video cruise over to the ST website for a look at the real thing.

Define Timesheet

Do all your employees hate filling out project timesheets? If so, you may have the wrong timesheet. The “hassle factor” may be just too high for comfort. Go down farther on the page for a delightful little video by Zach the project geek. He wants to hear from you.  🙂

Define timesheet: An entry form for period accounting of employee and project hours. Standard Time® is an example of employee timesheets.

Normally, timesheets are weekly. They normally list projects that employees are assigned to. Sometimes those projects can be expanded to show tasks. Often task hours roll up to the project level so employees can see how many hours they spent on each project for each day.

Sometimes timesheets show only the seven days of the week, and sometimes they show all the days of a pay period. Totals are often shown at the bottom of each daily column, and weekly totals are shown below those. Pay period hours may also be displayed, where the expected number of hours are compared with actuals.

Graphical timesheet, as shown in Standard Time, may display time segments in a graphical form, as you might see in a Microsoft Outlook calendar. Drag blocks around to change time or actual work.

Behind the scenes, project timesheets often collect billable amounts based on actual work. Billing rates usually depend on the person performing the work. Invoices collect all those time segments, with their billing rates into one bill. Clients can see details and rates associated with every task and person on the job.

Good timesheets make life simple for both companies and employees. Let’s see what Zach has to say in the video below.

Define Project Tracker

Is your project wrecking your company and leaving you destitute? The problem may be your project tracker. Watch the video below for a definition.

Define: Project Tracker. A system for monitoring long-term and cohesive activities, usually in software.  Standard Time® is such a project tracker.

It’s true; projects can go crazy without a project tracker. Employees camp out on favorite tasks, and convince managers that more time is needed. Tasks go over budget, and nobody notices slowdown trends that triple the project duration. Feature creep balloons up the scope until you wake up one morning and realize you’re chasing a moving target… or the moving target is chasing you. You have to get things under control, but you don’t know how.

Those are some of the things a good project tracker can do. You can set “do not exceed” percentages for tasks. That holds tasks to a reasonable duration. It prevents the slow lag from developing. You can monitor new tasks added to the project to prevent feature creep. You can view the health of your project with Project Triangle charts. You can check resource levels to make sure nobody is over or under allocated.

Are you running a project tracker? Why not try Standard Time?