|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Daylight Saving Time By Richard Rost 
6 years ago
Daylight Saving Time - Storing UTC (GMT) In this Microsoft Access tutorial, I will teach you how to deal with Daylight Saving Time calculations by storing UTC (Universal Time Coordinated, or Greenwich Mean Time) in your database. Paul from New York, NY (a Gold Member) asks: I run a 24-hour restaurant. Tonight, Daylight Saving Time ends and the clocks go back from 2am to 1am. This is going to mess up the time clocks for my employees who start at midnight and work until 8am. How do I adjust my Access database for this aside from manually editing their time cards twice a year? MembersI'll show you how to use an Internet Time Server to get the current UTC time regardless of what your system clock is set to or where you are located. This also prevents against employees manipulating their system clocks.
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! Links
KeywordsTechHelp Access #msaccess api utc, gmt, daylight saving time, daylight savings time, dst, get internet time, get time from internet, system clock, 24 hour time, time clock, time card, timeclock
IntroIn this video, we will talk about how to handle daylight saving time when calculating employee hours in Microsoft Access, especially for shifts that cross the time change. I will discuss best practices for storing both local time and UTC in your database to avoid errors caused by daylight saving transitions, and I will show you how to add the necessary fields and VBA code to your time clock database so your calculations are accurate without having to manually adjust time cards each year.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to talk about daylight saving time - how to calculate hours properly when shifts cross the daylight saving time change. Daylight saving time changes today here in the United States. I am in Florida, and technically at 2 a.m. tomorrow morning, we go back to 1 a.m. Just like Paul's question says, "I run a 24-hour restaurant. Tonight, daylight saving time ends and the clocks go back from 2 a.m. to 1 a.m. This is going to mess up the time clocks for my employees who start at midnight and work till 8 a.m. How do I adjust my Access database for this aside from manually editing their time cards twice a year?" If you are storing local time in your database, and like you said, Paul, they start at midnight and work till 8, the clocks are going to change in the middle of their shift. So when they clock out, if they work from midnight to 8, they actually worked 9 hours because they went from 2 a.m. back to 1 a.m. and worked that hour again. There are a ton of different ways you can handle this problem. Like you said, you can manually edit their time cards when the shift is over. That is okay. You have to do that twice a year when the time changes, which I personally hate. I wish they would get rid of daylight saving time. I would either like to just keep it year round or get rid of it year round. I hate changing clocks. Computers change, but as you will see in a second, you still have problems even if your system clock changes. All the other clocks you have, like in your car, your watch, if it is an old analog watch, your analog alarm clocks around the house, your stove, your microwave - you have to change all those. It is just a pain. And we do not need it anymore. Now, I have seen some VB code. In fact, when I was researching this question, there are a lot of different sites out there that have really cool programs, and they are long. There are tons of code, tons of different options and settings. It depends on where you are. Different countries and even different states and areas inside the United States have different rules. Some areas do not observe it. Some areas do. Some places use a half-hour increment for daylight saving. Some people use two hours. Only about 40 percent of the countries in the world still use it, and all those countries that do have different rules. England is different from the United States and so on. So what I think is, if you want to get rid of the complexity and you want to not have to worry about all the different rules or if they change - remember, they changed a few years back. I am looking at the Wikipedia entry for daylight saving time right now and they have all kinds of different rule changes in 1966 and 1972, all the way up to 2009. They keep changing what date it changes on and things like that. The solution I am going to show you is simple, and it totally gets rid of you having to worry about what the rules are. We will look at the system clock and let Microsoft worry about the daylight saving time changes. You will just have to know whether or not your clock is on daylight saving time. I am going to show you how to figure that out. The key to doing proper calculations is to store UTC, or Universal Time Coordinated, in your database for all of your time clock entries. I like to store both local time and the UTC time because then I can show the local time - like you clocked in at midnight, you clocked out at 9 o'clock - but I store the UTC in the table as well for doing calculations. That way I do not have to worry about whether or not that particular time was daylight saving or not because UTC does not observe daylight saving at all. It is meant to be a consistent, single format. Here I am in my time clock database. I built this database in a previous TechHelp video. I will put a link down below in the description so you can watch that video first if you do not understand how this was built. Look for the link to this time clock video. It is a free video on my YouTube channel and on my website. We basically have a time clock table. It has an employee ID from the employee table, a time in, and a time out. The time clock form opens up and it opens up in data entry so you can only add new records. Only a manager can go back to the table and view stuff, but you simply pick the person, clock in, clock out. There is a one-line VB code in here that handles setting that time. What we are going to do now, if daylight saving is messing up your calculations, is save UTC time in my table as well. Let us go to your table. Go to design view. We have our time in and time out - those are our local times. Now I am going to put time in UTC and time out UTC. Those will also be date/time fields. Save that. Close this. Go over to our form, design view, and add those fields in here. I will just copy these, copy and paste. All right, then we will put in here UTC in and UTC out and change the fields right over here, time in UTC. Make sure you change the name too - control source and the name - and do the same thing for UTC out. Copy and paste. You do not have to display these if you do not want to. You can make these invisible. We only need them for calculations. In fact, I will slide them down a little bit like that. For this next part, you are probably going to want to copy and paste the source code. Most of it is straight from Microsoft's website. Some of it I created myself. You can type it in if you want to off the video, but I made it available in my Code Vault, free of charge, on my website 599CD.com/DSTcode. I will also put a link down below the video so you can click on it instead of having to type that in. That URL will take you to my website, computerlearningzone.com. You are in my Code Vault right here. This page is free of charge. I want you to copy everything from where it says constant UTC offset. Copy that. Just click and drag. Copy everything from there right down to the bottom where it says End Function, right above that Usage there. All that code. Copy it - control C. Now come back to your database and create a module. Go to Create and then Module, and then paste that code in there. Go up to the top and take a look at it. If you have never done any programming before, do not worry. This is not that scary. You do not even have to know what all this stuff is. I am going to go over it real basically for those of you who know a little bit about programming. All you really have to know is to change this value right up here where it says UTC offset equals minus 5. I am in the Eastern time zone. I am UTC, or what used to be called Greenwich Mean Time, minus 5. When you are in standard time, what is your offset? This will be different based on whatever state you are in or whatever part of the world you are in. That is the only thing you have to change. This stuff here, these are just structures or types. Those come straight from Microsoft, as well as this guy here that is declaring a function called GetTimeZoneInformation. Now this comes from Windows. This here allows Access to go out to the Windows system clock and say, "Hey, are you in daylight saving time or not?" That is all it really gets. It uses this TimeZoneInformation type which in turn uses the SystemTime type, and you do not have to worry about all that. All we have to know is to call this guy. This is the function we are going to be using: CurrentUTC. It works just like the Now function or the Date function. You do not have to send it any information. It is just going to return what the current UTC is based on your system clock. In order to do that, it looks at IsDST, right down here. IsDST determines whether or not your current system clock is in daylight saving time or not. It will return either standard, daylight, or unknown, in which case it comes back up here and returns the proper time based on your offset. I know it seems complicated, but it is really simple. Once you have all this code that you just had to copy and paste, now come back over here, save that by the way. Control S, I am going to save mine as the DSTCodeMod. Call it whatever you want to call it. This name is meaningless. Once you have that DST code in here, you can go back into our time clock form and fill these values in here. If you watch the other video, the time clock clock in and clock out buttons basically set time in to Now, time out to Now. Here we are going to go time in UTC equals CurrentUTC. That is it. That is all you have to do. Time out UTC equals CurrentUTC. Save that. Close it up. Open it back up again. Now pick your employee. Clock in. Look at that. I am currently in daylight saving time because it is Halloween. It is the 31st of October at 6:10 pm here, 6:95 pm. UTC or Greenwich Mean Time is four hours ahead of me because I am still in daylight saving time, so my clock is an hour ahead. In a few hours, 2 a.m. Sunday, we are going to drop out of daylight saving time. So I will test this tomorrow and it should work. Then later on, you clock out. Boom. Now if you go to your table, your time clock table, you will see you have both sets of data in here. You can display the local time, but you can use this UTC time to calculate your hours worked because this might be incorrect. It will show you your local times. This over here will show you the UTC time. I think this is a much better solution than trying to calculate what the date is and whether we are in daylight saving time or not, and what year it is, because the rules have changed. If you are just worried about your local computer clocking in and out, tracking those kinds of times, then just taking a look and seeing what the system time is when the user clocks in and out is a much better solution than running through tons of code. It is a very complex problem. This is one of the reasons I hate daylight saving time. I cannot stand it. I think they should just get rid of it completely. We do not need it. It might have made sense 100 years ago, but now with indoor lighting and all that, we do not need it. So there you go. That is a pretty easy solution for setting universal time that takes into consideration daylight saving time. That is all you pretty much need right there. If you are entering in historic data, if you have a list of dates and times from last week and you have to type them all in, then you are going to have to figure out which ones are daylight saving or not. As far as employees logging in and out in real time, this will look at your system clock and determine whether or not it is daylight saving time. That was a pretty simple solution you can implement in about 10 minutes. Just copy and paste the code or type it in yourself. There you go. Want to learn more? Members, I have something extra special for you. Instead of calculating UTC based on the system clock and your time zone, I am going to show you where you can have your computer go out to the internet and ping a time server. In this case, we are going to use worldtimeapi.org, a free site, and have it tell you what the current UTC is. So you click on the button and the internet just tells you what the time is. It does not matter where you are. It does not matter what date it is. It does not matter what your system clock is set to in Windows. This will just automatically give you the current UTC. So even if the user does something to mess with your system clock - if they decide they want to back up the time an hour or move it forward an hour or whatever - once they click that button to clock in, it does not matter. This is going to go out to the internet and bring back the current time. How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. If you click the join button, you will see a list of all the different membership levels that are available, each with its own special perks. Do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more and they will always be free. QuizQ1. What primary issue does daylight saving time introduce for time clock systems in a 24-hour business?A. Employees forget to clock in during time changes B. Shift durations may be miscalculated when clocks change C. Employees do not work during daylight saving time D. System clocks stop working during the switch Q2. If an employee works a shift from midnight to 8 a.m. during the fall daylight saving change, how many hours do they actually work? A. 7 hours B. 8 hours C. 9 hours D. 10 hours Q3. What is the recommended approach for storing time entries in a database to avoid daylight saving issues? A. Only store local time B. Store UTC (Universal Coordinated Time) for all entries C. Store the time returned by the Now function only D. Manually adjust times during daylight saving periods Q4. Why is UTC preferred for time calculation in the context of employee time clocks? A. UTC automatically changes with daylight saving time B. UTC is always two hours ahead of local time C. UTC does not observe daylight saving time and remains consistent D. UTC only works in the United States Q5. What additional fields does the instructor suggest adding to the time clock table? A. Employee name and department B. Local time in and out in text format C. 'Time In UTC' and 'Time Out UTC' fields D. A notes field for daylight saving entries Q6. What is the main function of the provided VB code in the tutorial? A. To manually enter daylight saving dates B. To determine if the current time is in daylight saving time and return accurate UTC C. To change all local times to daylight saving automatically D. To print reports of employee hours Q7. Where does the provided code get information about daylight saving status? A. An online server B. Windows system clock C. Employee clock-in notes D. Microsoft Access database only Q8. What must be customized in the VB code to match your geographic location? A. The name of the time clock table B. The UTC offset value C. The employee ID format D. The company's business hours Q9. What is a limitation of only using local time for calculations across daylight saving changes? A. It requires converting to text for calculations B. It produces incorrect hour counts when shifts span the time change C. It makes reports look unprofessional D. It stops employees from clocking in Q10. For entering historic data prior to the UTC system, what extra consideration must be made? A. The employees' wage rates B. Whether the date was during daylight saving or standard time C. The type of shift worked D. Only the employee name Q11. What alternative method is suggested for members to get the current UTC regardless of the system clock? A. Contacting Microsoft support B. Using worldtimeapi.org to fetch UTC from the internet C. Sending employee emails D. Making manual calculations in Excel Q12. Why is it better to store both local time and UTC for time clock entries? A. To display the local time conveniently and calculate hours accurately B. To satisfy legal requirements C. To reduce database size D. To avoid using any VB code Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-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. SummaryToday's video from Access Learning Zone covers an important topic: how to calculate employee work hours accurately in Microsoft Access when shifts overlap the daylight saving time change. This comes up especially for businesses that operate late at night or around the clock, like 24-hour restaurants. When daylight saving time ends, clocks roll back an hour, so, for example, a shift might technically repeat an hour. If someone clocks in at midnight and clocks out at 8 a.m., and the clocks fall back at 2 a.m., they genuinely worked 9 hours, not 8, because the hour from 1 to 2 a.m. happens twice.You could handle this by manually editing the affected time cards after each daylight saving change, but that is not an ideal solution. It introduces extra work twice a year and is prone to mistakes. To avoid this, it is much better to build a method into your Access database to account for these clock changes automatically. One main challenge is that daylight saving rules are not only different in every country but also sometimes locally within states or provinces. The rules can change year to year. Only a minority of countries in the world still observe daylight saving, and those that do can have complicated and changing schedules. This means any hardcoded solution is likely to become outdated quickly. The best approach is to store UTC (Universal Coordinated Time) values in your database for all time clock entries, in addition to the local time that you might display for users. UTC does not observe daylight saving, so it's consistent no matter the time of year or where you are in the world. By calculating work hours from UTC values, you completely avoid the daylight saving problem. If you have a time clock database set up with fields for employee ID, time in, and time out, you just need to add two more fields: one for time in UTC and one for time out UTC. These new fields are for keeping the UTC equivalents of the local clock-in and clock-out times. You do not even need to show these on forms if you prefer—they can be hidden from view and used solely for calculations. To make this work, you need some VBA code that figures out your current UTC offset and whether daylight saving applies at the moment. There are ready-made pieces of code for this, and I have gathered some for you in my online Code Vault free of charge. All you have to do is copy and paste the provided code into a new module in your Access database. The only thing you will need to edit is setting your correct UTC offset for your particular time zone during standard time. The code then checks, using your Windows system clock, whether daylight saving is currently active and returns the proper UTC value at the moment of clock in or out. Once you have this code in place, all you need to do is update your time clock form so that, when an employee clocks in or out, you not only record their local time (using the Now function), but also record the UTC time (using the provided function, which will give you the current UTC value considering daylight saving automatically). This approach is simple, avoids the headache of having to track constant daylight saving rule changes, and keeps your calculations reliable. You end up with both the local and UTC times in your data. For calculating hours worked, you use the UTC timestamps. You can still show users their familiar local times. If you are entering historic data, you will have to investigate whether each entry was during daylight saving or not, but for normal day-to-day use, this solution will automatically handle everything, since it checks the system's clock at the moment employees log in or out. For those interested in taking this a step further, there is an extended lesson exclusively for members where I cover how to retrieve the current UTC directly from the internet using a free time server, such as worldtimeapi.org. This ensures you get the precise global time regardless of how your system clock is set, preventing possible errors even if someone has tampered with their system time. Membership gives you access to all these extended tutorials and more, but these standard TechHelp videos will always remain free to everyone. If you would like to see a full video tutorial with detailed, step-by-step instructions on everything discussed here, you can find it on my website at the link below. Live long and prosper, my friends. Topic ListExplanation of daylight saving time impact on time clocksStoring both local time and UTC in Access tables Modifying tables to add UTC fields for time in and out Updating forms to include and manage UTC fields Copying and pasting VBA code to handle UTC conversion Setting UTC offset based on your local time zone Using Windows API to detect daylight saving time status Implementing the CurrentUTC function in VBA Automatically recording UTC time during clock in and out Using UTC calculations to determine correct hours worked Comparing local time versus UTC for shift calculations Dealing with historic data entry and daylight saving time |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access api utc, gmt, daylight saving time, daylight savings time, dst, get internet time, get time from internet, system clock, 24 hour time, time clock, time card, timeclock PermaLink Daylight Saving Time UTC in Microsoft Access |
