How to automate a weekly employee timesheet in Excel using formulas to auto-populate the dates in the pay period, how to calculate time in and out including breaks, as well as how to accurately calculate overtime for hours worked over 40 in the work week. We will also create a macro to automatically create 52 weekly timesheets in a Workbook. This template will automatically update the pay period end date, and all days and dates in the pay period based on entering/updating the pay period start date. Also, simply enter base pay rate and all other totals will auto-calculate.
Check out my other videos on timesheets:
https://youtu.be/YTtpcf4vg1A - Create a Timesheet Template from Scratch
https://youtu.be/HDyyZzC_CPU - Automate a Bi-Weekly Timesheet
https://youtu.be/dSOKqNcq2I8 - Automate a Semi-Monthly Timesheet
Link to purchase the fully automated templates if you are interested in getting a jump start: https://www.etsy.com/listing/1745072850/employee-timesheet-template-for-payroll
TIMESTAMPS
00:00 Automate Weekly Timesheet in Excel
00:29 Auto-populate Dates in Excel
02:05 Format Time Calculations in Excel
03:44 Calculate Overtime in Excel
05:26 Auto Create Weekly Timesheets with Macros
VBA Code Used in this Video:
Sub CreateWeeklyTimesheets()
Dim ws As Worksheet
Dim i As Integer
Dim newSheet As Worksheet
Dim startDate As Date
' Set the starting date in Week 1's G3 cell
Set ws = ThisWorkbook.Sheets("Week 1")
startDate = ws.Range("G3").Value
' Loop to create Week 2 through Week 52
For i = 2 To 52
' Copy the Week 1 sheet
ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
' Rename the new sheet
Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
newSheet.Name = "Week " & i
' Update the start date in G3
newSheet.Range("G3").Value = startDate + (i - 1) * 7
Next i
MsgBox "52 weekly timesheets created successfully!"
End Sub
FREE COURSE: How To Create Fillable Forms in Microsoft Word - A Step-by-Step Guide
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro!
https://youtu.be/438pCPCSuG4
CHANNEL LINK
https://www.youtube.com/@SharonSmith
Visit my Channel page on YouTube to see all my videos, playlists, community posts and more!
TEMPLATES
Check out my helpful list of templates available for purchase: https://creatoriq.cc/43c51cv
Thank you for supporting my channel!
FREE DOWNLOADS
Visit https://mailchi.mp/6a0859ea0844/sharonsmithhrformulasdownload to sign-up for my e-mail list and get FREE downloads of super helpful spreadsheet formulas, dashboards and Org Chart templates for HR professionals.
CONNECT WITH ME
Visit my website: https://www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: https://www.linkedin.com/in/sharonsmithhr
Twitter: https://twitter.com/SharonSmithHR
Instagram: https://www.instagram.com/sharonsmithlearning/
Facebook: https://www.facebook.com/SharonSmithLearning
GEAR
Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!)
Screen Recording Software: https://techsmith.z6rjha.net/NZG5b
Green Screen: https://amzn.to/2DnHsY2
Camera: https://amzn.to/39KvpQA
Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
RESOURCES
JotForm: https://www.jotform.com/pricing/?utm_source=sharon-smith&utm_campaign=jf1&utm_medium=blog
Links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
SUPPORT THIS CHANNEL
- Hit the "$Thanks" button on any video, or
- Donate through my PayPal link: https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url
If you found this content helpful, please consider donating to my channel. Your donation, no matter what amount, is greatly appreciated and goes towards producing more content that enhances your productivity and elevates your skills.
You can also support my channel just by watching, liking, and sharing all my videos! Thank you so much!
PLAYLISTS
Jotform Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuO84pPbnHdTdgurmxHgZcyD
Creating Fillable Forms: https://www.youtube.com/watch?v=YktZ3B-tyqs&list=PLb80efKTbWuOe1LfFMNExEabkRkB5ehcv
PowerPoint Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMcFRbsZO5wkXgYjBG4meRM
Excel Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuNjHDGgNeuGrGwOvh4JQC90
Excel Tips & Tricks: https://www.youtube.com/playlist?list=PLb80efKTbWuPI7fEWtroXHvJHhx5FnVMW
Word Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMotoYEXBFMlB3PtsBlFn45