|
Windows 10 Coming Soon |
Tips & Tricks Windows Forum Course Index |
|
|||||
|
|
ISO Date Format By Richard Rost 
4 years ago
Switch Date Format to Avoid Confusion YYYY-MM-DD In today's video, I'm going to show you how to switch your Windows PC to the ISO 8601 standard date format. This will help you avoid confusion when communicating with people in other countries, and prevent international incidents! Links
More Information
Topics CoveredIf you communicate with people in other countries, whether for business or pleasure, you need a date standard that's the same for everyone. When you have dates displayed like 4/2/2022, that could mean April 2nd or February 4th, depending on where you're located. This can be embarrassing on a personal level. "My flight will be landing at noon on 2/3/22." This can be costly on a business level! "To avoid a late fee, your invoice is due on 4/5/22." I personally have students in almost every country of the world. When people ask me questions, and I reply, it can be very confusing for both of us. The ISO 8601 standard date format is unambiguous. Regardless of where you are, the date 2022-10-03 is always October 3rd, 2022. Date and time values are ordered from largest to smallest unit of time: year, month, day, hour, minute, second. Each date value has a fixed number of digits, padded with leading zeros. YYYY-MM-DD Dates may also be written without separators. This was more common with older computers storing date values in text files. YYYYMMDD In addition to being a logical and unambiguous date format, ISO dates are naturally sortable strings. No separate logic is required to sort them. In Windows, open Region settings, click Change Data Formats, Select the ISO Short Date. The ISO Time standard is (local time zone): HH:NN:SS.xx The ISO Combined Date/Time Standards: I am going to change my time settings from a single-digit HOUR to a double-digit one though. That’s more in line with the ISO standard. You’ll see why when we talk about Access shortly. EXCEL USERS: Be careful typing in new date values now. If you just type in "1/2" then you'll still get 02-Jan. But if you type in "1/2/22" you'll get 2001-02-22. ACCESS USERS: Typing in new date values a little bit different than Excel. The field on the form will always keep the same format, unlike Excel where the format may change based on what you type. Assuming Short Dates: Typing "1/2" will give you 2022-01-02. Typing "1/2/22" will give you 2001-02-22 Dates are still stored internally as a number, so you shouldn't have to worry. Any functions like DateAdd, DatePart, Year, Month, Format, etc. should all still work the same. The only time you may have a problem is if you’re importing data from spreadsheets or text files that are formatted differently. Be sure to double-check anything you import! If you don't change the Windows Short Time setting that I mentioned earlier to show two-digit hours, then even if you use hh:nn to display times, Access will still use the Windows single-digit Short Time format. Use an Input Mask to help your users get used to entering dates with the new format. https://599cd.com/InputMask I'm just making the change myself today, so I'm sure I'll have a lot of tips, tricks, and pointers in the coming weeks. Stay tuned for more! https://599cd.com/ISODates Keywordswindows 10, windows 11, microsoft windows, ms windows, ms windows tutorial, #mswindows, #microsoftwindows, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, iso 8601 advantages and disadvantages, global standard date and time format, international date standard, universal date format, short date, two digit hour
IntroIn this video, you will learn how to change your computer's date and time settings to use the ISO 8601 standard format in Windows. I will show you where to update these options in your Windows Regional Settings, explain the benefits of the ISO format for avoiding date confusion in international communication, and go over how these changes affect date and time formatting in Microsoft Excel and Microsoft Access. We will also discuss common challenges to watch out for when switching formats and tips for adapting to the new system.TranscriptWelcome to another Fast Tips video brought to you by WindowsLearningZone.com. I am your instructor, Richard Rost.In today's video, I am going to teach you how to switch a computer over to the ISO standard date format. This is a universal date format that is used all around the world, and you can use it to avoid confusion and prevent international incidents. If you communicate with people in other countries, whether for business or pleasure, you need a date standard that is the same for everyone. When you have dates displayed like 4-2-22, that could mean April 2 or February 4, depending on where you are located. This can be embarrassing on a personal level. For example, "my flight will be landing at noon on 2-3-22" if you said that to someone in a text or an email. Or it can be costly on a business level, to avoid late-fee invoices due on 4-5-22. Is it April 5? I have students in almost every country of the world. When people ask me questions, usually in their Excel sheets or their Microsoft Access databases, it can be very confusing for both of us. I get their information, I look at it, and I am like, is this day-month-year, year-month-day, or whatever? So it is important to have a standard date format. The ISO 8601 standard date format is unambiguous, regardless of where you are. The date 2022-10-03 is always October 3, 2022. You have probably heard of the ISO before. It is the International Organization for Standardization, founded in Geneva, Switzerland in 1947. They are responsible for everything from business practices and food safety to healthcare, technology, and of course, date and time formats. 8601 is the name for a specific set of standards for dates and times. It was first published in 1988. We should have been using it all this time. They recently revised it as of 2019, and it is a method for providing a clear standard for international dates and times. Now, it is the most logical way to format a date. Date and time values are ordered from the largest to the smallest unit, so it goes year, month, day, hour, minute, second. Each date value has a fixed number of digits padded with a leading zero, so you will never see something like 2022-1-2. It is always 2022-01-02. Dates may also be written without separators. This was more common in older computers when you were using text files to store dates. For example, you could just write it like that without any slashes or hyphens. In addition to being a logical and unambiguous date format, ISO dates are naturally sortable strings, so you do not need any special logic or functions to sort files that might have these as backup file names. You can see how easily they are just sorted. If you go month-year or even year-month, you cannot naturally sort that. How do you set the ISO date format? It is in Windows. It is in your Windows settings under the Regional Settings. In Windows 10, and I am pretty sure in 7 and 8, you go to Regional Settings, Change Data Formats, and then go to ISO Short Date. Yes, I have not upgraded to Windows 11 yet, so I am getting there. But just go to your Start button, type in REGION. There is Region Settings. Click on that. Come down here to Change Data Formats and you will see right here, Short Date. I dropped this down before and I picked this one right there. That is the ISO standard. Year-Month-Day. For me, I am in the US. It goes like this. I want that one. I am going to leave the Long Date setting as it is. It is pretty much unambiguous as it already is. So this guy here, the Long Date, I am going to leave that one. That is pretty much just something that I use for displaying in a Word document if you put the date and you want it to look pretty, or on top of a report. The ISO standard also includes times. If you want to change your Time Format, it is hour, minute, seconds, and then fractions of a second if you want it. I used N to represent minutes because in Microsoft Access, N is for minute in the format and M is for month. And yes, this is a 24-hour clock. There is no AM and PM in the ISO standard. If you are familiar with military time, 2300 hours, for example, is 11 o'clock PM. This is probably the one reason why I will not yet switch to 24-hour time, the ISO standard, because so many people are still used to a 12-hour clock. That is going too far. But let me show you a little bit more about the time standard. If you want to add a time zone, you can go plus or minus however many hours before or after Greenwich Mean Time you are. For example, minus five would be New York in standard time. If you are using Universal Coordinated Time, UTC time, that is the format. You just simply put a Z at the end that indicates it is UTC. If you do not know what UTC is, I have a video about it. You can go watch it right there. You will find a link down below in the link section. Of course, you could put them together. You could do a date value with a time value because as you know if you use Microsoft Access, each data point can be a day and time together in the same field. There are different standards. There is a date with a space and then a time, or you might see it with a T there. Sometimes they separate it with a T. This is the date portion. This is the time portion. This one adds a time zone. Actually, it is Greenwich Mean Time. That means there are zero hours plus or minus. This one indicates it is UTC. Here you might see it without any punctuation marks. This is perfectly valid. Month and day are all two digits. And a T, then hours, then minutes, seconds, universal. Now, do not worry if this all seems confusing. I am just going over what the standard is. You do not have to worry about getting this complex with it yet. While I am strongly in favor of a 24-hour UTC clock, I know a lot of people prefer the standard 12-hour AM/PM system. I am not ready to make that change yet either. So, baby steps. Although I think world commerce, communication, and everything as the world gets smaller as we are all communicating over the internet and doing business in different countries, I think a 24-hour universal clock would be perfect. So it is three o'clock on Tuesday everywhere. That is just me. And do not even get me started on daylight savings time. I absolutely hate daylight savings time. I cannot stand it. Changing clocks twice a year is dumb. Especially where I live in Florida, we have already voted, the state voted to get rid of daylight saving time. But we are still waiting on Congress. Since it involves interstate trade, we have to wait for Congress. Sometime probably in the next decade, we might be able to get rid of it. Again, if you want to learn more about either of these concepts, universal time or daylight savings time, there is the link right there. Now, I am going to leave time pretty much as it is, but I am going to switch from the single digit hour to the double digit hour. That is more in line with the ISO standard. So I am going to change my short time. The default short time is this guy. I am going to change it to this one. Same with long time. I like that double digit hour. Why am I doing that? You will see exactly why when I talk about how this affects Microsoft Access in a few minutes. So hang in there. I will explain why. Today's video is only scratching the surface with dates and times. If you want to learn more about the ISO standard as it applies to day of the year, there is formatting for weeks of the year (like 1 through 52), durations, time intervals, all kinds of stuff. I will put links to some valuable resources. There is the link on my web page. You can go to it or I will put some links down below in the description. Now, how does this stuff affect Excel and Access? When you make this change, the dates that you already have in your spreadsheet that are formatted as just standard dates or short dates will be switched to the ISO dates. But any custom date formats you have in place will not be changed. You can see over here, this was just a standard date and they all switched automatically. But this one I had a custom date format in the cell and it kept it the way that it was. It will not change any custom formatted cells that you have. You do have to be careful entering in new values. If you just type in 1/2, you are going to get January 2nd, just like this. Just like you are expecting it to from the old standard. But if you type in 1/2/22, you are going to get this, 1-2-22. So it is going to take this and make it year-month-day, even though you are expecting the old format, it is going to convert it to that. So be careful. You can still type it in this format if you want to use the default here. How does this affect Microsoft Access? Likewise, any existing short date fields, which is the default date format in Microsoft Access, will be changed to the new ISO date. So unless you have any custom date formats, you will be fine. My short dates got changed to that. My long dates look like that, which is fine. This one I had a custom date in, which kept the old format, which was month-day-year. So these are not going to change. These will change automatically. Typing in new date values in Access is going to be a little bit different from Excel. The field on the form will always keep the same format, unlike Excel, where the format can change based on what you type. So remember in Excel, when you type in 1/2, you still get January 2nd. In Access, if you type in 1/2, it is going to give you this format, though. The value is still the same, but what is displayed is still going to be the short date that Access has. Access is a lot more structured and rigid, whereas Excel lets you change things a lot more easily. So it is going to try to adapt the format to whatever you type into what it thinks you want. I do not like that about it. I like Access where you can define what the field is supposed to look like ahead of time. You can do that with Access and Excel, but Access is more rigid. If you type in 1/2/22, same problem, you are going to get 1-2-22 in that format. So here is what you type in and here is what you get, minus the quotes, of course. Dates are still stored internally as that same number, so you do not have to worry about the values in your data and your tables being wrong. Any functions that work with dates, like DateAdd, DatePart, Year, Month, Format, all that stuff, should all work just the same. The only time you might have a problem is if you are importing data from spreadsheets or text files that are formatted differently, so be sure to check that anytime you run an import. Also, if you have code where you manually assemble dates based on string concatenation, you have to watch that, too. I know I used to do a lot of that in the old days before I knew all the date functions. I would take like the left two characters of a string and make that the month and so on. So you have to be careful. Now, here is the thing with that short time setting that I mentioned earlier. Why I am switching to the two-digit hour. If you do not change the Windows short time setting that I mentioned earlier to show two-digit hours, then even if you use the HH:NN format to display times, Access will still use the Windows single-digit short time format. I actually did a video about this years ago. It is on my YouTube channel and my website. But basically, even if you use HHNN, even if you specify that as the format, Access will still convert that over to short time and use one digit for the hour. Does not care. I personally think it is a bug, but it will convert this over to short time. So you have to make the changes at the Windows level. So instead of this, you get this. That is why I like to do that. I like the two-digit hours. I want all my times to line up the same. The only time I would change this is if I am trying to make it pretty. Also remember, if you make these changes, you may have to close your database and reopen it. So if you have an Access database open and you make changes in the Control Panel and you change your regional settings, you are going to have to close Access and restart it for those changes to take effect. A couple of times, while I was preparing these slides, I did not do that. I forgot to close the database. I am like, why did it not change? Now, the advantages of using the new ISO date. If you do any kind of international communication, I have already gone over the advantage. It is unambiguous. Everyone is on the same page. Everybody knows what is going on. If someone is in England, someone else is in China, someone else is in the US, they are all on the same page as far as the dates go. The only major disadvantage I can think of for adopting the new format is getting used to inputting new dates. It is going to take you some time to get used to it, me included. I am just doing this today. I am switching over all my databases and my computers to the standard today. I finally decided to just pull the bandaid off and just do it. Old dates will generally adapt to the new standard automatically. Typing in new values is going to take some getting used to. If you are still just typing in common dates like month-day, sure, you just have to still type in 4/5. But if you are doing stuff with years, you have to remember to lead with the year. You have to type in 22/4/5 at least. That will work. Practice. Get used to it. If you want some enforcement and some help in getting used to typing in the new date format, set up an input mask in your fields. This way your users have to input the data that way. They have to type in exactly 2022-01-22 or whatever. I have a separate video on input masks. There is the link right there. You will find it down below in the link section. There is more to come. Like I said, I am just making this change myself today. I finally decided over the last couple days I am going to do it. All my new classes and videos that come out are going to be in this date format just because everyone around the world, I have people from pretty much every country on the planet that buy my lessons. So I want everyone to be on the same page. I am sure I will have more tips and tricks and pointers coming up and I will put links on this page if I do any follow-up videos for this. So keep checking, keep watching my YouTube channel. You will see something on there. Of course, I have to mention the guys over at xkcd.com. Very funny cartoon series. Here is your public service announcement. Following formats are therefore discouraged. So there you go. There is your fast tip for today on setting your computer to the ISO date format. I hope you learned something. Again, my name is Richard Rost from WindowsLearningZone.com and we will see you again real soon. QuizQ1. What is the primary benefit of using the ISO 8601 date format?A. It is preferred by Microsoft Excel B. It is unambiguous and avoids confusion internationally C. It supports only US English users D. It uses slashes instead of hyphens Q2. Which date format is guaranteed to mean October 3, 2022, everywhere according to ISO 8601? A. 03-10-2022 B. 10-03-2022 C. 2022-10-03 D. 22/10/03 Q3. Why can dates like 4-2-22 cause confusion? A. They are only recognized in Microsoft products B. Different countries use different orders for day and month C. Only ISO 8601 can be used in emails D. Slashes are not a valid separator Q4. In the ISO 8601 format, how are date and time units ordered? A. Day, month, year, hour, minute, second B. Month, day, year, hour, minute, second C. Year, month, day, hour, minute, second D. Year, day, month, hour, minute, second Q5. What characteristic makes ISO 8601 dates naturally sortable as strings? A. Use of slashes between numbers B. The order from largest to smallest unit C. Use of abbreviations for months D. All values use single digits Q6. Where in Windows do you change your date format to the ISO standard? A. Display Settings B. Device Manager C. Regional Settings under Control Panel/Settings D. Task Manager Q7. What is the main difference between the short date and long date settings in Windows regarding ambiguity? A. Long date is ambiguous B. Short date is ambiguous, long date is generally unambiguous C. Both are always ambiguous D. Neither can be changed Q8. How does the ISO standard represent times? A. AM/PM system only B. 24-hour clock without AM/PM C. 12-hour clock with AM/PM D. With hours and days only, no minutes Q9. How is Universal Coordinated Time (UTC) indicated in an ISO date-time string? A. By inserting a U at the end B. By placing a + or - before the numbers C. By placing a Z at the end D. By writing GMT after the time Q10. What must you do after changing Windows regional settings for the changes to take effect in Access? A. Nothing, changes are immediate B. Restart the database or Access application C. Reinstall Microsoft Access D. Update all table records manually Q11. What will happen to custom date formats in Excel or Access when you switch to ISO standard in regional settings? A. They will be converted to ISO format B. They remain unchanged C. They will be deleted D. Only long dates will change Q12. In Microsoft Access, why is it important to use double-digit hours in your short time Windows setting? A. Access ignores Windows time settings B. To ensure Access uses two digits for hours in time formatting C. Because single digits are faster to display D. It enables the use of AM and PM in ISO format Q13. What is the main disadvantage of switching to the ISO 8601 date format, according to the video? A. The dates become ambiguous B. Inputting new dates takes time to get used to C. It is not supported outside the US D. Only works with Excel Q14. What can you use to enforce ISO 8601 date entry in data input fields? A. Input masks B. Long date format C. Custom fonts D. DateAdd function Q15. When importing data from spreadsheets or text files into Access with ISO date settings, what should you be careful about? A. The year will be lost B. Data might be interpreted in the wrong order if formats do not match C. Access will refuse the import D. Only short times can be imported Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-C; 10-B; 11-B; 12-B; 13-B; 14-A; 15-B 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 Windows Learning Zone covers how to change your computer's date format to the ISO standard. I think this is a worthwhile adjustment for almost anyone who interacts with people or businesses in other countries. Adopting a consistent date format helps avoid confusion and mistakes, whether you are managing travel plans or handling invoices.One of the big challenges with dates is that the same numbers can be interpreted differently in different countries. For example, the date 4-2-22 could be April 2 or February 4, depending on which country you are in. That miscommunication can lead to anything from missed flights to late fees on invoices. Since I have students from all over the world who send me data for help with Excel or Access, even I sometimes have to pause and sort out what date format they are using. That is why a standard is so important. The ISO 8601 date format removes all ambiguity. When you see 2022-10-03, you know it is October 3, 2022, no matter where you are. ISO, which stands for the International Organization for Standardization, is responsible for this and many other standards. This particular date-related standard came out in 1988 and was updated in 2019. It provides a logical sequence: dates and times are written from the largest to the smallest value, so you go year, then month, then day, followed by hour, minute, and second. Every number has a set length, using leading zeros where necessary, so you always get something like 2022-01-02 and never 2022-1-2. Historically, computers sometimes stored ISO dates without separators, mainly for space-saving reasons. You might still see dates formatted like that, though using the hyphens is much more common now. A major advantage of the ISO format is that it sorts naturally as a text string. If you store files with ISO dates in their names, they line up in the correct order when you sort them. Formats like month-year or just year-month do not sort as intuitively. To set your computer's date format to the ISO standard, you need to adjust your Windows regional settings. In Windows 10, for example, you open Regional Settings, locate the option to change data formats, and set the short date to the option that uses the year-month-day structure. I am still running Windows 10 myself, but the process is similar for Windows 7 and 8. There is also a long date setting, but I recommend leaving that as is, since it is usually unambiguous already and more of a cosmetic preference for documents or reports. The ISO standard includes rules for time as well. Hours, minutes, and seconds are presented in a 24-hour format without AM or PM. In Access, the format uses 'N' for minute (because 'M' is reserved for month), and it supports fractions of a second if you need them. If you have used military time, you are familiar with the 24-hour arrangement; for example, 2300 hours means 11 PM. Personally, I am sticking with a 12-hour clock for now, since most people are used to that, but I do think a universal 24-hour system would be ideal for international communication. If you need to indicate time zones, the ISO format lets you add plus or minus offsets from Greenwich Mean Time. For Universal Coordinated Time (UTC), you just put a 'Z' at the end. If you are not familiar with UTC, I have additional videos and links to explain it further. ISO dates and times can be combined as well, such as including both the date and the time in one value. Sometimes you will see the date and time separated by a 'T', which is also acceptable. The important thing is that everything remains consistent and clear. Do not be overwhelmed by the variety of ways the ISO standard can be used. If you are not ready for all the details, you do not need to go beyond the basics just yet. There is also the matter of daylight saving time. I am not a fan. It causes added confusion, and even where I live in Florida, we are trying to eliminate it, but until Congress acts, we have to follow existing federal rules. Now, when you make these changes, existing short date formats in programs like Excel or Access will automatically switch to ISO. Custom date formats that you might have set, however, will remain unchanged. For Excel, that means columns with the standard date will update, but custom-formatted cells will not. Also, be aware when entering new dates. If you type in a partial date, Excel may interpret it based on your locale's old format, which can cause some confusion until you get used to inputting dates the ISO way. The situation is similar in Access. Regular short date fields will change to ISO once you update your system settings, but custom fields keep their formatting. Access interprets what you type in forms a little differently than Excel. In general, Access keeps things more rigid and consistent. Internal storage for dates remains the same, so functions like DateAdd, DatePart, Year, Month, and Format will all still operate properly. The only concern is when importing data that might be formatted differently, or if you build dates using text manipulation in your code. You need to pay attention to those details, especially if you handle input that was originally set up in a different country or using an older standard. One subtlety worth mentioning is how Access handles time formatting. If your Windows short time setting is still set to single-digit hours, Access will continue to use that format even if your Access field is set up to show two digits. Access mirrors the Windows setting here, sometimes ignoring what you specify in Access itself. If you care about having times displayed with two digits for the hour, be sure to change your system settings accordingly. Whenever you adjust your regional settings in Windows, remember to close and reopen Access to let your database pick up the new formats. It does not update live. The main benefit of switching to the ISO format is how it simplifies international communication. Everyone uses the same convention, so there is far less chance of misunderstanding. The only real downside is getting comfortable typing dates in a new way, but it just takes some practice. If you want to enforce the new format for your users, consider setting up an input mask in your forms so that dates have to be entered in the correct order. I have another video on input masks with links available for more details. This is something I am actively working on for my tutorials and courses, so all my new materials will use ISO dates going forward. I will keep sharing new tips and tricks as I encounter them. So that covers getting your computer set up with the ISO date standard and some important notes about how this affects Excel and Access. If you want a detailed, step-by-step demonstration of everything I discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListExplanation of traditional date formats and ambiguitiesIntroduction to ISO 8601 date format History of the ISO 8601 standard Benefits of using ISO 8601 for international communication ISO 8601 date formatting rules (YYYY-MM-DD) Explanation of fixed digit and zero-padded dates Dates without separators in ISO format Sorting advantages of ISO date strings Locating Windows Regional Settings Changing short date format to ISO in Windows Leaving long date format unchanged in Windows Overview of ISO time format (24-hour clock, HH:MM:SS) Understanding time zones in ISO 8601 Using UTC and 'Z' notation for time zones Combining date and time in ISO format Examples of formatting combined date and time in ISO Effect of ISO date setting on Excel spreadsheets Custom date format behavior in Excel after changes Important considerations when entering new dates in Excel Effect of ISO date setting on Microsoft Access Custom date format behavior in Access after changes How date and time input differs between Excel and Access Impact on stored values and internal date storage Handling date functions after switching formats Risks with importing data and manual date string concatenation Adjusting Windows short time to two-digit hours for Access Necessity of restarting Access/database after changes Advantages of standardizing on ISO format Input adjustment period and retraining for users Using input masks to enforce ISO date entry in forms |
||||
|
| |||
| Keywords: FastTips windows 10 windows 11 iso 8601 advantages and disadvantages, global standard date and time format, international date standard, universal date format, short date, two digit hour PermaLink ISO Date Format in Microsoft Windows |
