Daylight Saving Time
By Richard Rost
3 years ago
Creating a UTC-Based Employee Time Clock in Excel
In this Microsoft Excel tutorial, I'm going to show you how to create a Time Clock sheet for employees. You'll be able to click buttons to clock them in and out. More importantly, we'll store the date in Universal Time (UTC) to avoid any problems with daylight saving time clock changes in the middle of an employee's shift
David from Tallahassee, Florida (a Platinum Member) asks: You created a video a couple of years ago about Daylight Saving Time and implementing it in Microsoft Access. Well, my employer still has us using an Excel spreadsheet to track clocking in and clocking out. Is there any way to achieve the same functionality in Excel, perhaps by associating it with a button that employees can click to clock in and out?
Prerequisites
Links
Members
There is no extended cut, but here's my workbook file:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Recommended Courses
Want More Excel?
Most of what I do is Microsoft Access, so if if you want to see me post more Excel videos, make sure to comment below: "I want more Excel!"
Keywords
excel 2021, excel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #techhelp, dst, daylight saving time, utc, timeclock, Time Clock sheet, employees, clock in, clock out, buttons, Universal Time, time tracking, employee shift, time clock changes, time management, time conversion, local time to UTC, UTC conversion, Excel functions, VBA, macro, automation, custom function, time zone conversion, daylight saving time adjustment, employee time tracking, Excel time clock
Intro
In this video, I will show you how to set up an employee time clock in Microsoft Excel that accurately records clock-in and clock-out times using UTC to handle daylight saving time changes. We'll go through using VBA to add clock-in and clock-out buttons, store both local and UTC times, and calculate hours worked based on UTC to avoid daylight saving issues. I will also show you where to find and use sample code for determining the current UTC offset in Excel, and walk through building a simple, functional timesheet from start to finish.
Transcript
Welcome to another TechHelp video brought to you by ExcelLearningZone.com. I'm your instructor, Richard Rost. Today, I'm going to show you how to build a time clock in Excel using UTC, which is Universal Time.
This comes up every year. I get people asking me about this with daylight saving time if you're doing employee time clocks, and the employee's shift goes over the time change. How do you deal with that? I go through this every year, so we're going to show you how to do it in Excel this year.
This year's question comes from David in Tallahassee, Florida, one of my platinum members. David says, "You created a video a couple of years ago about daylight saving time—it's daylight saving time by the way, people, not daylight savings time—anyways, and implementing in Microsoft Access, which is what I do. Well, my employer still has us using an Excel spreadsheet to track clocking in and clocking out. Is there any way to achieve the same functionality in Excel, perhaps by associating it with a button that employees can click to clock in and out?"
Yes, David, absolutely. Pretty much any code that you can use in Access, you can use in Excel. There are some exceptions. Obviously, if it's Access-specific stuff, like looking things up from a table, you can't do that. But most VBA that you can get to work in any of the Microsoft Office applications works in all of them.
What David is referring to is a video I did about three years ago about daylight saving time in Access, which is pretty much what I just mentioned. In the video, it's pretty much the same thing. It was a question from Paul from New York about the same basic thing. You got a clock shift, you got employees that work over that time shift—how do you calculate it? I have a previous video that showed you how to build a time clock. Employees use the time clock, pick their name, clock in, clock out, and it's pretty simple. But that's local time. And if he clocks in at 1 a.m. and clocks out at 8 a.m., then you got a problem.
The way to solve this is to store UTC, which is Universal Time Coordinated—basically Greenwich Mean Time. It's what they use on the International Space Station so everything runs smoothly. We add two more fields to the table—more fields to our time clock table—and then we save them in UTC time.
There's source code that you can get online. This comes straight from Microsoft, in fact. I have a copy of it in my code vault, which is free for everybody. This particular function, and I'll give you a link to it in a minute, will walk you through installing it in Excel. This basically goes out to your Windows system clock and checks if you are currently on DST or not, and then, if so, it'll adjust based on your offset. For example, I am in Florida, which is Eastern Time Zone. In standard time, we're minus five hours from UTC. This functionality can then determine what the current UTC is for your time zone. This is code that we can install just fine in Excel as well.
Once all that's set, you clock in and it puts the UTC in the UTC out when you clock out. It's pretty simple. Let's see how we do this in Excel.
First of all, this is a developer-level video. That means we're going to need some VBA to accomplish this goal. You'd think that Microsoft would add a function like UTCNOW, which exists in some other platforms. I think Power Automate has it, or Power Query. But in Excel by itself, without those extra add-ins, you have to do it this way—just like how we have to do it in Access.
I'm not surprised that functions are missing from Access because Access is the red-headed stepchild, literally, of the Microsoft Office family. Usually, they give cool stuff to Excel first. Since we need some VBA to do this, if you've never done any VBA programming before, go watch this first to teach you how to get started with Excel VBA. You have to turn on the developer tab and some other stuff. Go watch this first; it'll teach you what you need to know to get started. Then come on back.
Here I am in Excel. We're going to create a basic timesheet. We have our name. Of course, you can make this as detailed as you want. There's the clock in, which will store the local time, and the clock out, the UTC in, and the UTC out. Then we'll calculate our hours worked based on the UTC time in and out.
Let's make it a little bit bigger. I always like to add a splash of color when I'm working. It makes things go a little bit better if you can see things clearly.
The way it's going to work is you put your name here, and you can make this a drop-down list if you want to. I've got other videos on making drop-down lists. Now, you're going to want to click a button over here to clock in and then another button to clock out for this particular row. We'll get the current time, put it there, and then we'll figure out what the UTC time is.
Let's make the button first to just get the regular time. Go to our developer tab, click Macros, and let's give the macro a name. Let's call it ClockIn (no spaces), then click Create. The VB window opens up; we're now in Module1, ClockIn, right here.
Now I want to say whatever row I'm currently in, in column B, put the current time—current date and time technically. So we're going to say:
Range("B" & ActiveCell.Row).Value = Now
That's the ActiveCell.Row, so the row goes in column B. While we're at it, let's format it the way we want. Now, I like universal time. I like the ISO date-time format. If you want to learn more about that, here's a video I did. It's basically year-month-day. It's completely unambiguous, so anyone anywhere in the world knows what it is. I'm on a mission to change everyone to using this time format and to get rid of daylight saving time. I hate daylight saving time.
So we're going to say that same cell .NumberFormat equals (and then set the format). So that's going to put the current date and time in that cell and set the number format.
Now, let's make a button and assign that to this macro, so save that. You have to save this file as an XLSM file, a macro-enabled file, otherwise your code will not run. You can see I was playing with it earlier if today, there's my old one. I'm going to call this one DaylightSavingTime.xlsm—it's a macro.
Now we can assign it to a button. Let's come back over here, go to the Developer tab, and then right here under Insert, under Form Controls, pick a button—the rectangle—and then draw a button out somewhere over here. We're going to assign it to the ClockIn macro. Hit OK, and while you're at it, change the caption to Clock In.
Now click in the row. Make sure this is a training thing for the user—you have to click on that row and then hit Clock In. There you go. You have to make it wider, obviously, so you can see it all. There it is. Make these columns wider too.
We can do the same thing for Clock Out. Since you've got this already started, just come back over here and copy this whole thing. Copy, paste. We're going to make this ClockOut. The difference here is it's going to be in column C. You could pass it as a parameter; there are all kinds of things you can do, but I'm keeping it simple for today.
My philosophy with Excel is I like to keep my programming examples as simple as I can in Excel, because most people who use Excel do not really necessarily want to be programmers. If you did, you'd do something like Microsoft Access. Sorry, that was a dig at Excel users—my bad. I love Excel too. I've been using Excel longer than I've been using Access. I've got some great stuff that I do in Excel, like stock portfolio stuff you cannot do in Access. In fact, I've got my Access databases sometimes opening in Excel in the background, using them because Access lacks the functionality.
Now we have ClockOut written. Now we can make another button. Come back over here. Now you can't click on the first button to select it, so you have to right-click on it, and then click on the border, Ctrl+C to copy, click over here, Ctrl+V to paste. While it's still selected, drag it next to it. Right-click, then assign macro, then pick ClockOut. Hit OK, and change the caption to Clock Out. Again, make sure you're in the right row. Hit Clock Out.
That's working. Left-align all the stuff.
Now let's get to the meat and potatoes of the video—the UTC stuff. You're going to have to come to my website and get this code. It's all right here. This is straight from Microsoft; I did some modifications down here, but this is mostly straight from Microsoft. If you Google it, you can find it somewhere else. I'll put a link down below in the link section. It's called the DST code—the Daylight Saving Time Code. We have to put this in our module so that we can figure out if the computer is currently on daylight saving time or not.
Hit copy right there. That will copy it to your clipboard. Back to Excel, go back to VB. In your module, come right up top here and just paste all that stuff in. I'm not going to go into explaining all this stuff. I don't even explain all of it in the Access video. Sometimes it's like driving a car; you don't have to know how every little bit and piece under the engine works in order to drive the car. This is just stuff you need. Even me as a programmer, I sometimes copy code from other programs and just work with it—bits and pieces.
Give it a quick Debug > Compile, make sure you copied and pasted everything correctly. Now we can use this function here called CurrentUTC. That gets the current universal time.
So down here in our ClockIn and ClockOut, we're just going to copy this. For ClockIn, UTC is in column D and for ClockOut, it's in column E. So we're going to say:
Range("D" & ActiveCell.Row).Value = CurrentUTC
and for ClockOut:
Range("E" & ActiveCell.Row).Value = CurrentUTC
Save it again, Debug > Compile. Always give it a quick debug and compile.
Now, ready? Click Clock In—boom. Clock Out—boom. They're the same because I just clicked them at the same time. There we go.
There's your current local time and the UTC time. There are all kinds of other things you can do. You can protect the sheet, you can make drop-down lists, whatever. You can do a check to see if there's a value here already and warn them, "Hey, you've already clocked in. Are you sure you want to overwrite that?" Millions of things you can do. It's just how much time you want to spend doing it.
Hours worked is pretty easy. For that, I would use the UTC because that's the whole point of this exercise. The local time is going to reflect that daylight saving time change, whereas UTC will not. In Excel, just like in Access, a unit of 1 equals 1 day. To figure out the hours worked, it's going to be:
=(TimeOut - TimeIn) * 24
Because the more recent value is going to be higher, and the difference is in days. So you multiply by 24 to get hours, and there's your number of hours worked.
If you want to see a real value here, just change this to, let's say, 1:00 PM, and change this one to 5:30 PM. There you go: 4.5 hours.
That's how you do it. It's not that hard. The toughest thing is all this code up here, which you do not have to write—just copy it. The rest is all little bits and pieces.
Everybody always asks me, "How do I take existing dates and times and convert them over to UTC?" Unfortunately, there is no easy function for that. This is a function that you can use to generate the current time, because it can look at your system's clock and determine from Windows whether or not you are currently in daylight saving time or not, and then it can apply this offset accordingly. It comes down here and says, all right, if we are in daylight saving time, get time zone information from the computer, and if it is, then it applies daylight saving time; if not, it does not.
If you want to do this with existing times from previous time clock data that you have, you are going to have to use something a lot more complicated. I recommend using an internet time server. There are a lot of different websites that you can use. They have APIs, which means that you can send them a request for information, and they will give you the data back. They can do that calculation and figure it out for you.
In the extended cut for my original Access video, I do show you how to get the current time from WorldTimeAPI.org, which is an internet server. It's basically making a call out to a web API, and then they return a value to you. There are other APIs you can also use; you can send a date and time and a location, and it will also return the UTC for you. There are lots of different things you can do. Check this video out for more information.
If you want to learn more about things like those drop-down menus that I talked about, in my Excel Expert Level 10 class, I cover making custom drop-down lists and all kinds of other stuff. There are lots and lots of Excel lessons on my website. I'm currently in the process of updating them. I should have some new versions out pretty soon.
If you want to see more Excel TechHelp videos, let me know about it down below. That is going to be your TechHelp video for today. I hope you learned something, my friends. Live long and prosper. I'll see you next time.
Become a member—click the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks.
Gold members get access to download all of the sample spreadsheets that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use and more.
Platinum members get access to all the previous perks, plus all of my beginner full courses and one new expert course every week. These are the full-length courses found on my website, and not just for Excel—they also teach Word, Access, Visual Basic, ASP, and lots more.
When you do sign up to become a member, I need you to email me and tell me, "I want more Excel." The vast majority of my videos are for Microsoft Access because that's been my focus for the past three years. However, I'm happy to add more Excel videos if I get more Excel members. So make your voice heard and I'll make lots more TechHelp lessons for Excel.
But don't worry—these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.
Quiz
Q1. What is the main purpose of building a time clock in Excel using UTC?
A. To calculate hours worked while accounting for daylight saving time changes
B. To reduce the size of Excel spreadsheets
C. To improve spreadsheet formatting options
D. To allow users to print timesheets easily
Q2. Why is it recommended to store time values in UTC rather than local time?
A. UTC eliminates ambiguity caused by daylight saving time changes
B. UTC formats are easier to read for all users
C. UTC requires less storage space in Excel
D. UTC automatically generates payroll reports
Q3. What type of code is needed to add UTC functionality to an Excel spreadsheet?
A. VBA code copied into a module
B. A built-in Excel formula
C. An Excel add-in from the Microsoft Store
D. A PowerPoint macro
Q4. Where can you typically find functions for daylight saving time calculations for use in VBA?
A. From Microsoft source code or code vaults online
B. In the regular Excel formula library
C. As a standard feature in all Office applications
D. Only by purchasing third-party utilities
Q5. What is the significance of the CurrentUTC function discussed in the video?
A. It returns the current Universal Time regardless of daylight saving time status
B. It creates a list of employee names
C. It automatically sends emails about clock-in times
D. It prints out timesheets for the month
Q6. When coding clock-in and clock-out macros, what general procedure should be followed?
A. Use ActiveCell.Row to reference the current row for inputting values
B. Reference the first cell in the sheet regardless of where the user is
C. Always hard-code the cell addresses in the macro
D. Only allow macros to input times in the first column
Q7. Why is it necessary to save the Excel file as an XLSM?
A. XLSM files support macro-enabled functionality
B. XLSM files are faster than XLSX
C. XLSM files are required for conditional formatting
D. XLSM files can be edited on mobile devices
Q8. How is the "hours worked" value calculated when using UTC times in Excel?
A. Subtract UTC In from UTC Out, then multiply by 24
B. Add UTC In and UTC Out together
C. Divide UTC Out by UTC In
D. Use the SUM function on the range of values
Q9. What extra feature does the instructor mention you could add for usability?
A. Drop-down lists for employee names
B. Automatic payroll deposit
C. Calendar integration
D. Automatic chart generation
Q10. According to the video, how can you convert historical, already entered local times to UTC effectively?
A. Use an internet time server or API
B. Apply the Excel NOW() function to old data
C. Use the built-in Excel function UTCNOW()
D. Automatically update all old cells with the CurrentUTC macro
Q11. What precaution does the instructor advise when copying Daylight Saving Time code from Microsoft?
A. Use Debug > Compile to check for errors after pasting
B. Always modify at least 50 percent of the code
C. Run the code in Access first, not Excel
D. Only use the code once per workbook
Q12. What format for dates and times does the instructor prefer to use for clarity internationally?
A. ISO year-month-day format
B. U.S. month-day-year format
C. European day-month-year format
D. Julian date format
Q13. If you want to prevent overwriting existing clock-in times, what should you do?
A. Use code to check for existing values before writing new ones
B. Clear the entire row before input
C. Lock all cells on the worksheet
D. Use conditional formatting to block entries
Q14. If you need to get the current UTC time based on your computer's settings, what does the VBA code do?
A. It checks if the computer is on daylight saving time and applies the correct offset
B. It asks the user to input the time zone
C. It randomly guesses the UTC adjustment
D. It assumes standard time all year round
Q15. What is the main advantage of using UTC over local time for time clock calculations?
A. UTC is not affected by daylight saving time or local time zone changes
B. UTC is always faster to generate in Excel
C. UTC is required by all government agencies
D. UTC makes files smaller in size
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's video from Excel Learning Zone focuses on how to create a time clock in Excel that records times using Universal Time Coordinated (UTC). Every year, I get questions about handling employee time clocks, especially when shifts cross over the daylight saving time change. These issues can be tricky, so I want to show you a way to manage it directly in Excel.
David from Florida asked if it's possible to achieve the same function in Excel as the solution I demonstrated for Access a while ago. He wants to allow employees to clock in and out through an Excel spreadsheet, potentially using buttons, and he is concerned about working across time changes. The answer is yes; most VBA code you see in Access can be adapted for Excel with just a few changes.
To summarize the need here: when employees work overnight or past the daylight saving time shift, simply recording local time can create confusion, and calculations might be incorrect. The way to address this is by recording times in UTC, which does not change with local time shifts. This approach is used on platforms like the International Space Station to avoid issues with changing clocks.
To achieve this in Excel, you will need some VBA code because Excel does not include a built-in function like UTCNOW that can give you the current time in UTC. Microsoft Access and many other platforms have this capability, but vanilla Excel does not. So, to get this working, I will guide you through setting up a simple timesheet, adding the necessary VBA, and installing the right code to check your system's daylight saving time status and calculate the current UTC.
Start by setting up your Excel sheet with columns for the employee name, clock in time, clock out time, and then two more columns for UTC in and UTC out. Your users will enter or select their names, and then you can provide buttons to clock in and out, recording both local and UTC times. If you like, you can enhance the spreadsheet with formatting and colors to improve readability.
Adding the clock-in and clock-out functions with buttons is straightforward. You'll need to enable the Developer tab in Excel, which is where you can access the VBA editor and insert form controls like buttons. Assign each button to a macro: one to enter the clock-in time, and one for clocking out. These macros record the local date and time and format them in a standard, unambiguous ISO format, which I strongly recommend since it's clear no matter where you or your users are.
For UTC calculations, you will need to add some VBA code, which you can find linked from my website. This code comes from Microsoft and determines whether your system is currently using daylight saving time, making the correct adjustments based on your computer's time zone and whether daylight saving is in effect. Once the code is added and compiled, you can update your macros to also record the UTC time in the appropriate columns.
After setting everything up, when an employee clicks the clock-in or clock-out button, the macro records both the local time and the UTC time in the right spots on your sheet. This way, no matter how local time shifts due to daylight saving or time zone changes, you always have a reliable, standard reference. Calculating hours worked becomes simple using UTC, since UTC does not adjust for daylight saving. In Excel, subtract the UTC in time from the UTC out time, multiply the result by 24, and you'll get the total hours worked. This ensures accurate reporting, even over time changes.
If you have existing time data and want to convert those to UTC, unfortunately, there is no straightforward, built-in way in either Excel or VBA. Your best bet is to use an online time server which can return the correct UTC time for a given date, time, and location. Several APIs are available for this, including WorldTimeAPI.org, which I demonstrate in more detail in the extended cut of my Access video on this topic. These APIs can take a local time and timezone and return the corresponding UTC time.
If you want to learn more about advanced Excel features like custom drop-down lists or more in-depth time calculations, feel free to check out my Excel courses, including those at the expert level where I cover these topics in greater detail. I am always adding new lessons, so keep an eye out for updates.
If you have ideas for new Excel TechHelp videos or want to see more content on this subject, make sure to let me know. As always, all of my TechHelp videos are free and will remain that way, but if you want extra perks or access to extended lessons, sample files, and the complete code vault, you can become a member at various levels and get everything from sample files to full-length courses on a range of Microsoft Office topics.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Building a time clock spreadsheet in Excel
Handling daylight saving time changes in timesheets
Storing and displaying UTC (Universal Time) in Excel
Adding local and UTC time fields to timesheet tables
Writing VBA macros for clock in and clock out actions
Assigning macros to form control buttons in Excel
Formatting date and time in ISO standard in Excel
Copying and pasting external VBA code modules
Integrating Microsoft's Daylight Saving Time VBA code
Compiling and debugging VBA code in Excel
Calculating current UTC using custom VBA functions
Populating UTC in and out columns using VBA
Calculating hours worked based on UTC times
Protecting the worksheet for better data integrity
Saving Excel workbooks as macro-enabled files (XLSM)
|
