Has anyone used Smartsheet to create/track timesheets for employees/time off banks?

Lynn.McEnaney

My org does not currently have a timekeeping system and had been using paper timesheets up until 2020. During Covid they had mish-mashed together an Excel sheet to be filled out and submitted to a smartsheet (created for each pay period). This smartsheet template that is remade each week is really only a notification system asking for an attachment of that week's timesheet. It does not store that time information.

I would like to change the system so that a smartsheet form is sent out where people can either check off that they worked their normal hours (salaried employees), write in their hours (part-time/irregular employees), and add time off taken (we have different buckets: sick, vacation, personal, admin, comp). Another layer of complexity though- we have employees paid out of multiple areas- some through a grant, others through overhead.

I've started by creating a smartsheet grid with employee data (employee ID, name, contact, supervisor, full time vs part time, benefitted?, typical hours) But this is where I get stuck- how do I create something to send a weekly form and how to store that data without creating a weekly smartsheet? I envision this whole thing to be a roll-up with reports and a dashboard to ensure people can edit/approve as necessary

Erik Jensen

Add a date column and have people submit their time by date. I opted to create two sheets. One for time tracking/invoicing/job costing and another for tracking sick/vacation/snow days/etc.

For the form, we have created a custom form for each employee to fill out with their employee ID/name/whatever you want to use filled out and as hidden field on the form.

Hope that helps,

Erik

Will Jeffords

Hi Lynn!

I did this, check it out:

So, we have a handy form for EEs to fill out (we do half-day increments hence the weird am/pm questions).

The results feed a master sheet, there are metric sheets that then feed the dashboard above to give counts and the names of folks off this week and next week. Also I keep the current and next year holidays there for everyone's convenience (across US, Canada, and India where we have teammates)

Also use the Calendar app to show the PTOs on the calendar and their are EE-level (only filtering to current user) and Manager-Level Dynamic Views.

All this is in a WorkApp too which is awesome for mobile use.

We have "Flex PTO" now, but I started this before then so my sheet actually tracks and has math for accruals and deductions and days remaining if that is needed too.

Let me know if you want to chat about how to do any/all of this!

Our employees really dig it! 😎

-Will

Paola Barlow, PMP

Hy Lynn,

I suggest a PUR set to a recurrent weekly basis, this way you can also view who missed entering their time.

This suggests the following steps:

1- Create a sheet and set it as a DB to store all your employees (names and emails) first

2- Set PUR to collect hours every week

3- Set automation to copy row after entry (store/archive rows in another sheet)

4- Set automation to clear cell data (recurrent)

5- Create reports from (stored/archive sheets)

5.1-Add the summary options to your reports to summarize hours and group them by weeks by employee.

If you need extra help shoot me a message ;)

Paola Barlow

pbarlow@teopm.com

www.teopm.com

Sally Wico

I use "Update Request" automation, based on "When a Date is Reached", then customize it to go out every weekday. Employees fill out start/stop times (lunch is calculated in). The "extra" data (in my case, which building and which shift, in your case which fund they are paid out of) is captured in their own drop-down columns.

HOURLY EMPLOYEES: I have columns to add up their hours weekly, so the only columns the employees are sent through automation are: their name, the date, and the start/stop time. I created an Attendance REPORT fed by the grid and use it to summarize by employee at the end of each week (the only thing to change weekly are the date parameters).

SALARIED EMPLOYEES: much simpler - a separate automation that goes out weekly: they receive their name, the date range and a field for "how many hours have you logged this week" (this will alert their supervisor via automation if they reported less than their contractual amount).

PTO (Paid Time Off) is tracked on a separate grid (employees submit a Form for PTO); however, it is still tied to the same Attendance REPORT.

Hope this helps!

MichelleWatkins_GPP

Hi Lynn - we are a Smartsheet implementation partner and I've done time tracking a couple of ways.

The most streamlined option is to have one master database where time tracking hits that only you/admin have access so that you can include other confidential calculations as needed (i.e. pay rates x hours to also generate payroll). I also use the time tracking to hit my client project engagement budgets for burndown, etc.

You would give your employees direct access to the database so you need to find ways to surface their information so they can see what they submitted and get calculations, etc.

The way I optimized and solved for this for 2024 was to create reports from the database per consultant that groups by week range + I used the Dynamic View premium app for another straight list visual that they can use pre-set filters, set their own filters and click a button to add a new entry and click the submitted row to correct/change something.

If you want to get a little more sophisticated with using this data to generate payroll visuals for each person individually and overall - auto-grouped and calculated by pay date / pay period / employee - the Pivot premium app is key here.

Feel free to use my meeting link if you would like a demo of the solution I put into place to manage our Smartsheet consulting business as an implementation partner.

(I also happen to have 20+ years of HR/Payroll system implementation experience with ADP & UKG so I can give you some tips & tricks to consider to mimic an HR/Payroll system for your time off, etc).

Meet with GPMO :: Michelle Watkins (hubspot.com)

Sarah Keortge

We're using Smartsheet for Out of Office tracking (both PTO and business travel) and for weekly capacity surveys (not time tracking but 'how did your week feel?'). Both tools rely on a combination of locked-down data sheets where the submissions are stored, a centralized staff sheet that is used for cross-referencing additional information (like the person's manager, team, role), Dynamic View for allowing users to view and interact with their own submissions, DataMesh for keeping the data sheets and the staff info synced up, and Microsoft Power Automate for sourcing user information (i.e. mapping in their manager to the staff sheet from Active Directory) as well as for sending out Outlook messages ('remember to fill out this week's capacity survey') and invites that block their calendar for OOOs. In addition, we're using CalendarApp to display the out of office information in a user-friendly yet secure way. Happy to chat if any of this sounds intriguing.

Coop22

Lynn,

We used the time and date to create date/time values. We then subtract the clock out value from the clock in value to get the time clocked. Pretty similar to the way that Excel handles dates being the number of days from 1/1/1900.

Genevieve P.

Hi all!

This is a great discussion and we believe it could be beneficial for the broader, public Community. Since it's posted here in the private EAP group, the content is hidden and the post can't be shared - however there's no EAP content being discussed at the moment (that I can see!).

@Lynn.McEnaney would you be comfortable with me moving this out into the public Community site?

Lynn.McEnaney

Hi @Genevieve P. Yes, that would be fine!