How to easily capture Actual Time spent on a Task in SmartSheet?

Vyom Upadhya

Fellow SmartSheet Community Members, 

 

We have recently started using SmartSheet for planning out a roadmap for initiatives, along with types of roles allocated to the initiative (resources) as well as allocation. We are now looking for creative ways to capture "Actual" time spent on a particular initiative. Here are our requirements: 

 

1. Individual resources/team members would capture time, probably on a weekly basis. We need a solution that can scale to 40+ resources conducting this activity. 

 

2. The solution needs to be very easy to use - otherwise it will be hard to get compliance on time capture. 

 

3. The solution needs to be able to automatically aggregate time captured by various team members on an initiative and "roll it up" at the initiative level to give us a nice comparison of planned burn rate vs. actual. 

 

We currently use Harvest as a way to capture time, but there is only a unidirectional sync (per my understanding) from SmartSheet to Harvest, not the other way around. Open to any creative suggestions and ideas from the community on how this could be done or is being done by others. 

 

Appreciate your assistance in helping solve this!

 

Thanks, 

 

Vyom

greg@weclick

I guess your best bet would be to see what time tracking apps integrate with Zapier and approach it that way.

Travis

Jim Hook created a discussion where he talked about how he uses Smartsheet to track hours charged for 35 projects. You could ask him for information on how he handles and tracks this! Here is the discussion: https://community.smartsheet.com/discussion/still-looking-if-function-does-nothing-cell-if-false

Jim Hook

Hi Vyom,

 

I'm now into version five of my project tracking system using Smartsheet after two years of enhancements. Here's a very brief descripton of how my system works.

1. Each employee that works on projects has a unique project timesheet that is titled with their first name (no duplicates so far). The timesheet has as many rows as they need and each row includes a project name selected from a dropdown list that I maintain, a function that they performed (design, development, QA, etc.) that is selected from a dropdown list, a place to enter hours worked on a daily basis on the project (7 days) and a total hours for the week for that project. Each combination of project/function needs to be on a separate row. There is also a total across all projects for the week.

2. I use a Smartsheet report that scans all employee timesheets and extracts the total hours worked by each employee on each project/function. Our projects include some internal projects like company meetings, training, etc. so employees can account for all hours worked during a week. I then copy the results from the report into a master project timesheet that does a bunch of things (you can't do calculations in reports so you have to get it into a regular Smartsheet). First, I validate that all the project names match the official names in case someone has manually entered a project name with an error instead of using the dropdown list (hasn't happened so far). Then, using SUMIFS functions I scan all the data from the report building a table for each project that shows how many hours were worked by each functional area for the week. That data is linked into a unique project tracking sheet for each project that keeps track of the total hours to date against the initial estimate for the project on a week-by-week basis (copying the linked-in data into the current week is where I would love to have the "do nothing if false" IF function to avoid that weekly manual step).

 

From each project tracking sheet the hours to date, estimated hours, over/under calculations and other costs are linked out to a consolidated project financial summary for management and a project hours summary for supervisors and project managers to view all in once place. 

 

I've over-simplified this description in the interest of time but my app also tracks billable/non-billable hours for the various teams and individuals in the company. Also, I use an architecture that has about 60 pre-linked projects that talk to other system sheets making it much easier to open and close projects without having to create new Smartsheets with all their formulas and links. V4 of the app was realtime in that when an employee entered some time on their sheet it automatically rippled through all the subsequent sheets so management could see what was happening almost in real time. I'm going away from that in V5 because management doesn't need the realtime  feature and with the size of our organization and the number of projects and functions, I kept running into the 5000 incoming link on some of the sheets that consolidate data. It takes about five minutes to do an update if someone wants it rather than waiting for the end of a week.

 

I hope that helps and is not too confusing.

Vyom Upadhya

Thanks Jim - that was very helpful! You should find a way to productize your app for the marketplace

robyn.vandever24626

Hi All.  I am new to Smartsheet and I am having difficulty with time tracking.  I would like each user to have a timesheet that lists all of their active tasks.  They would enter the hours daily, the aggregate of those hours would be displayed on the project sheet.  I've seen some good discussion but I'm not sure how to set this up.  Any advice would be appreciated!

Matthew Anthon

Although this thread is raised from an older post, it deals with an issue that I have seen raised over and over again. I have yet to see a good answer. Generally, the problem looks like this....

An organisation will have a master Smartsheet. It will list jobs/tasks/projects and some high level data associated with them. On this sheet users would like to see a listing of the hours/costs currently associated with the jobs/tasks/projects.

Users believe this should not be an issue, because employees already enter their time into timekeeping Smartsheet. Expenses/charges relating to jobs are another common tracking function Smartsheet is used for. 

So long as there is an company admin faithfully ensuring that the "job number" column in the timekeeping and expense sheets is a drop down list that reflects all the jobs on that master sheet, each entry should be easy enough to relate back to the main sheet.

Surely we could just run a scan of a set list of "timesheet" sheets, and all the hours for say, job M100 could be totalled, and displayed on the master sheet M100 row under a column marked TIME USED. The same for expenses, charges, etc.

Except it isn't that easy. You have to run a report to scan those timesheets. Reports can't do math, so then you have to copy the info out of the report into another sheet so totals can be made. Then you have to manually copy the resultant info back into the master sheet.

I work for a small company that manages a lot of disparate projects. Tracking time and expenses associated with them is vital to not going broke. We love Smartsheet because its flexible, and we put it to a lot of uses, but not being able to live collate info from several sheets on a master sheet is a real killer.

Can anyone help us with this problem? 

I have looked at Zapier, but I don't believe it can do the maths unless you are capable of scripting it to do so in Python. There is also the issue of paying for a Zapier account simply to extend the functionality of Smartsheet in one very specific way.

If anyone familiar with the Smartsheet API believes they can set something up which can...

1) Make a dropdown list on several target sheets populate and stay synced to a  specified dropdown list on a master sheet

2) Scan several target sheets, do some math and update a field on the master sheet

... then I am willing to commission you. Contact me here.

Jim Hook

Matthew, instead of using a report that must be copied, pasted into a sheet for calculations and then the results copied and pasted back into the master sheet, there is a way to avoid all that. First, link all the employee timesheet data into one larger Smartsheet. In my case I linked in the project number, employee name, the role they worked on and the hours on a weekly basis. Each combination of project/person/role requires a unique row since each role, in my case, has a different cost associated with it. Then use advanced functions such as SUMIFS() on the larger Smartsheet to combine the hours per project and role across all people. Finally, link the combined totals per project back to the master sheet.

I used a scheme like this for awhile but it had three drawbacks. First, Smartsheet doesn't have a way (without Zapier) to have the dropdown lists created in a central place and automatically distributed to each timesheet so I had to manually update each person's timesheet with new projects as they opened (and remove them when they closed). Second, there was no way in standard Smartsheet to clear out a timesheet after they were processed at the end of a week, another manual step. Finally, I had to freeze the weekly hours per project on a unique Smartsheet for each project. In the end we ended up writing an internal app for timekeeping that uses the Smartsheet API to transfer the list of weekly time charges into a Smartsheet that does the calculations. I work for a consulting company that averages 50 active projects with 15 employees and this approach works great for us. If you don't have access to programmers for capturing the hours worked weekly then transferring them into Smartsheet I don't know of an efficient way to do what you want without Zapier.

Tom Gore

When I attempted to initiate the SmartSheet/Harvest integration it was asking for a SmartSheet to use for the upload.  Is there a template available that I can use to upload to Harvest?  What needs to be included in the SS and what is the format?

Thanks,

Tom