|
Excel 2010-Now Excel 2007 Excel 2003 |
Tips & Tricks Excel Forum Course Index CIG Excel Book |
|
|||||
|
|
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 Keywordsexcel 2016, excel 2019, excel 365, microsoft excel, ms excel, ms excel tutorial, #msexcel, #microsoftexcel, #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 switch your Windows system to the ISO 8601 date format, which helps avoid confusion when working with international dates. I will show you where to find and change the date and time settings in Windows, explain the logic behind the ISO standard, and demonstrate how this affects Microsoft Excel and Microsoft Access. We will also discuss what to expect when entering and displaying dates after the change and cover some tips for smooth transitions with old and new data.TranscriptWelcome to another Fast Tips video brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.In today's video, I'm going to show you how to switch your system over to the ISO date format. That is a universally accepted global date format that will help you avoid confusion when talking to people in other countries. Instead of going day month year or month day year, you go year month day. This way, everyone is on the same level. I recorded this video for Windows Learning Zone because this is actually a setting that you change in Windows. However, I know most of you are Excel and Access users, so I'm also going to show you how it affects Excel and Access. So here we go. Welcome to another Fast Tips video brought to you by WindowsLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to teach you how to switch your computer over to the ISO standard date format. This is a universal date format that's 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's the same for everyone. When you have dates displayed like 4-2-22, that could mean April 2nd or February 4th depending on where you're located. This can be embarrassing on a personal level, like "my flight will be landing at noon on 2-3-22" if you send someone a text or an email. Or, it can be costly on a business level to avoid late fees or invoices due on 4-5-22. Is that April 5th or May 4th? Personally, with my computer classes, 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'm like, is this day month year, year month day, or whatever? So it's 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 3rd, 2022. You've probably heard of the ISO before; it's the International Organization for Standardization. Founded in Geneva, Switzerland, in 1947, they're responsible for everything from business practices and food safety to healthcare, technology, and of course, date 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's a method for providing a clear standard for international dates and times. Now, it's 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'll never see something like 2022-1-2. 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 don't have to have any special logic or functions to sort files that might have these as backup file names, for example. You can see how easily they're just sorted. If you go month, year, or even a month and year, you can't naturally sort that. So, how do you set the ISO date format? Well, it's in Windows. It's in your Windows settings under the Regional Settings. In Windows 10, and I'm pretty sure in 7 and 8, you go to Regional Settings, change data formats, and then go to ISO Short Date. I haven't upgraded to Windows 11 yet, but I'm getting there. Just go to your Start button, type in "Region" (R E G I O N), there's Region Settings, click on that, and come down here to Change Data Formats. You'll see right here, Short Date. I dropped this down before and I picked this one right there. That's the ISO Standard, year month day. For me, I'm in the US. It goes like this. I want that one. I'm going to leave the Long Date setting as it is. It's pretty much unambiguous as it already is. So this guy here, the Long Date, I'm going to leave that one. That's 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's hour, minute, seconds, and then fractions of a second if you want it. Now, 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's no AM and PM in the ISO Standard. So if you're familiar with military time, 2300 hours, for example, is 11:00 PM. This is probably the one reason why I won't yet switch to 24-hour time, the ISO Standard, because so many people are still used to a 12-hour clock. That's just 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 right there would be New York in Standard Time. If you're using Universal Coordinated Time (UTC Time), that's the format; you just simply put a Z at the end. That indicates that it's UTC. If you don't know what UTC is, I've got a video about it. You can go watch it right there. You'll 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 Excel or Access, each data point can be a day and time together in the same field. Now there are different standards. Here's 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's Greenwich Mean Time. That means there are zero hours plus or minus. This one indicates it's UTC. And here you might see it without any punctuation marks. This is perfectly valid. You can do year, month, day (notice they are all two digits), then a T, then hours, minutes, seconds, Universal. Don't worry if this all seems confusing. I'm just going over what the standard is. You don't have to worry about getting this complex with it yet. While I'm strongly in favor of a 24-hour UTC clock, I know a lot of people prefer the standard 12-hour AM/PM system. I'm not ready to make that change yet either, so baby steps. Although, I think for world commerce and communication, and as the world gets smaller, as we're all communicating over the internet and doing business in different countries, I think a 24-hour Universal clock would be perfect. So it's three o'clock on Tuesday everywhere. That's just me. And don't 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've already voted, the state voted, to get rid of daylight saving time, but we're still waiting on Congress. Since it involves interstate trade, we have to wait for Congress. So, sometime 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 saving time, there's the link right there. I'm 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's more in line with the ISO standard. So I'm going to change my short time. The default short time is this guy. I'm going to change it to this one. Same with long time. I like that double digit hour. Why am I doing that? You'll see exactly why when I talk about how this affects Microsoft Access in a few minutes, so hang in there. I'll explain why. Today's video is only scratching the surface with dates and times. If you want to learn more about the ISO standard, it applies to days of the year. There is formatting for weeks of the year, like 1 through 52, duration, time intervals, and all kinds of stuff. I'll put links to some valuable resources. There's the link on my web page you can go to, or I'll 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. So it won't 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're going to get January 2nd, just like this, just like you're expecting it to from the old standard. But if you type in 1/2/22, you're going to get 1/2/22. See that? So it's going to take this and make it year, month, day. Even though you're expecting the old format, it's going to convert it to that, so be careful. You can still type it in this format if you want to use the default year. 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. Unless you have any custom date formats, you'll be fine. So my short dates got changed to that, my long dates look like that, which is fine. And this one, I had a custom date in, which kept the old format, which was month, day, year. 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. Remember in Excel, when you type in 1/2, you still get January 2nd. In Access, if you type in 1/2, it's going to give you this format, though. The value is still the same, but what's 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. It's going to try to adapt the format to whatever you type in, to what it thinks you want. I don't 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're going to get 1/2/22 in that format. Here's what you type in, here's what you get, minus the quotes, of course. Dates are still stored internally as that same number, so you don't 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're importing data from spreadsheets or text files that are formatted differently. Be sure to check that any time you run an import. Also, if you have code where you manually assemble dates based on string concatenation, you've got to watch that too, so be careful. I know I used to do a lot of that in the old days before I knew all the date functions. I would take the left two characters of a string and make that the month and so on. You've got to be careful. Now here's the thing with that short time setting that I mentioned earlier, and why I'm switching to the two digit hour. If you don't 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's 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. It doesn't care. I personally think it's a bug, but it will convert this over to short time. So you have to make the changes at the Windows level. Instead of this, you get this. That's 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'm trying to make it pretty. Also, remember if you make these changes, you'll 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're going to have to close Access and restart it for those changes to take effect. A couple of times, as I was preparing these slides, I didn't do that. I forgot to close the database and wondered why it didn't change. The advantages of using the new ISO date: if you do any kind of international communication, I've already gone over the advantages. It's unambiguous. Everyone is on the same page. If someone is in England, someone else is in China, and 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's going to take you some time to get used to it, me included. I'm just doing this today. I'm switching over all my databases and my computers to this standard today. I finally decided to just pull the bandaid off and 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're still just typing in common dates like Monday, you just have to still type in 4/5. But if you're doing stuff with the years, you've got to remember to lead with the year. You've got to type in 22/4/5 at least. That will work. Practice and 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's the link right there. You'll find it down below in the link section. There's more to come. Like I said, I'm just making this change myself today. I finally decided over the last couple days I'm 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'm sure I'll have more tips and tricks and pointers coming up, and I'll put links on this page if I do any follow-up videos for this. So keep checking, keep watching my YouTube channel, you'll see something on there. Of course, I have to mention the guys over at xkcd.com, very funny cartoon series. Here's your public service announcement: the following formats are therefore discouraged. So there you go. There's 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'll see you again real soon. Thanks for watching. QuizQ1. What is the main advantage of using the ISO 8601 date format?A. It uses slashes instead of hyphens B. It is recognized only in the US C. It avoids international date confusion D. It displays dates in the DD/MM/YY format Q2. In the ISO 8601 standard, how are the components of the date ordered? A. Day, Month, Year B. Month, Day, Year C. Year, Month, Day D. Month, Year, Day Q3. Which organization is responsible for setting the international date standard ISO 8601? A. Microsoft Corporation B. World Date Association C. International Organization for Standardization D. Universal Time Council Q4. What happens to dates in Excel or Access that already have custom formats applied when switching to ISO date format in Windows? A. Custom formats are converted to ISO automatically B. Custom formats remain unchanged C. All formats are reset to default D. The program will show an error Q5. If you type "1 2 22" in Excel after changing to the ISO date format, how will it interpret the input? A. January 2nd, 2022 B. February 1st, 2022 C. February 22nd, 2001 D. January 22nd, 2002 Q6. What is a potential problem when importing date data from spreadsheets or text files into Access after switching to ISO format? A. All values convert to text B. Values may be misinterpreted due to different formats C. Data will be deleted automatically D. Files will be locked for editing Q7. Why is using the ISO 8601 date format beneficial for file sorting? A. It allows files to sort alphabetically only B. It creates naturally sortable strings for dates C. It randomizes file order D. It requires custom sort functions Q8. How should you change your date format to ISO 8601 in Windows? A. Change display resolution B. Go to Regional Settings and set Short Date to year-month-day C. Uninstall Windows updates D. Change system language Q9. What is the correct ISO 8601 representation for October 3rd, 2022? A. 03-10-2022 B. 10-03-2022 C. 2022-10-03 D. 2022-03-10 Q10. How are times formatted in the ISO 8601 standard? A. 12-hour clock with AM and PM B. 24-hour clock without AM or PM C. Roman numerals D. Day name first Q11. What do you need to remember if you make changes to the Windows short time setting for use in Access? A. No changes required B. Access immediately adopts new format without restart C. You have to close and reopen Access to apply the new setting D. Reinstall Access Q12. What is a main disadvantage when switching to the ISO date format? A. Loss of historical data B. Inputting new dates takes some adjustment C. Data cannot be exported D. Time zones become obsolete Q13. In ISO 8601, how can you indicate Universal Coordinated Time (UTC) in a timestamp? A. Add "GMT" at the end B. Put a "Z" at the end C. Use "U" at the start D. No special notation required Q14. How does Access treat short date fields when the Windows regional date setting is changed? A. Keeps old format regardless of settings B. Short date fields are automatically updated to the new format C. Deletes all date data D. Turns all dates into text Q15. What is recommended if you want to enforce the new ISO date entry format for users? A. Allow any input style B. Use input masks for date fields C. Disable date fields D. Use only text fields for dates Answers: 1-C; 2-C; 3-C; 4-B; 5-A; 6-B; 7-B; 8-B; 9-C; 10-B; 11-C; 12-B; 13-B; 14-B; 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 set your computer to use the ISO date format. I am Richard Rost, and I want to explain why this universal date format is important and how you can configure it in Windows. While this setting is changed at the Windows level, I know many of you are regular Excel and Access users, so I will also discuss how this change affects your spreadsheets and databases.The ISO date format, which is written as year-month-day, solves a lot of international confusion. In the United States, dates are usually formatted as month-day-year, but in many other countries, day-month-year is standard. This leads to ambiguity. For example, a date written as 4-2-22 could be interpreted as either April 2nd or February 4th, depending on where you are. It might be a minor embarrassment if you're telling someone about your travel plans, but in business, not using a clear, universal format could result in real financial losses. Because I have students from all over the globe in my classes, I encounter these issues all the time when working with their files and databases. It's not always obvious which date convention someone is using. That is why it makes sense to switch to a standard like ISO 8601. In this format, 2022-10-03 will always be understood to mean October 3rd, 2022. The ISO, or International Organization for Standardization, was set up in Geneva in 1947 and is responsible for many global standards, including the 8601 standard for dates and times, first published in 1988 and updated as recently as 2019. The idea behind this standard is to remove ambiguity and make international date and time communication clearer. The format orders date and time components from largest to smallest: year, month, day, hour, minute, and second. Each part has a set number of digits, with leading zeros as needed, so you would see 2022-01-02, not 2022-1-2. Sometimes you will also see these dates written without any separators, which was common with old text file-based systems. A helpful side-effect of the ISO date format is that it's naturally sortable as a string. Files named using ISO dates will always sort in chronological order, which is handy for organizing backups and reports. Using month-day-year or other local formats will not give you this convenience. You set the ISO date format through your Windows Regional Settings. In Windows 10 and earlier, you access this by searching for "Region" and then changing your data formats. For the Short Date option, select the format that shows year, month, and day in that order. I recommend leaving the Long Date format as is since it's typically already clear and is mainly used when you need a full written date for documents or reports. The standard also covers time. ISO time format uses a 24-hour clock and does not include AM or PM. In Access, you use "N" for minute because "M" stands for month. If you want to include a time zone, you add a plus or minus sign followed by the difference from Greenwich Mean Time. For example, New York is minus five hours. Using "Z" indicates Coordinated Universal Time (UTC). Dates and times can be combined, separated by a "T" (so you might see 2022-10-03T14:30 for October 3rd, 2022, at 2:30 PM). The time zone can also be specified here. There are various precise ways to write these combinations, and while you may not need to use all the advanced features yet, it's good to know they exist. Personally, while I support the move to a 24-hour universal time, I understand many people are still attached to the 12-hour format. For now, I'll keep using the 12-hour clock for most tasks but will make the switch to double-digit hours in my short time settings, which lines up more with ISO. This helps everything look consistent, especially in databases. Daylight saving time, in my opinion, is unnecessary and creates even more confusion. My own state, Florida, has voted to eliminate it, but congressional approval is still pending. If you are curious about UTC or daylight saving time, I have separate videos on those topics. When you change your Windows date and time settings, your existing dates in Excel and Access that use the standard date format will automatically update to the new ISO format. However, any fields or cells that you have given a custom date format will stay as they are. For example, if you previously formatted a cell to show the US-style date, that cell will not automatically update to the ISO style. Be careful when typing new dates in Excel—if you enter 1 2, it will still be recognized as January 2nd, but if you type 1 2 22 with your computer set to ISO format, it will be understood as the year 1, month 2, day 22. The system will interpret your input based on the newly assigned format, so you will have to adjust your habits and lead with the year in your entries. In Access, forms are more rigid. If you type in a date, the field will still display it in the format your system now expects. Databases are stricter about formatting than Excel is, which tends to do more guessing based on what you type in. Internally, both Access and Excel store dates as serial numbers, so your calculations and functions—like DateAdd, Year, Month, and Format—should still work consistently, provided you pay attention when importing from other sources. If you have custom queries or code that assemble date strings by pulling apart text, this is a good time to review your logic and make sure you are not assuming month comes first by position in the string. One quirk to watch for is that, even if you set your date display format to use two-digit hours, Access will defer to whatever your Windows Short Time setting is. If you want your times to consistently show two digits for hours, make sure to update that setting at the Windows level. Access sometimes ignores format settings in favor of Windows' defaults, which I think is a bug. Also, when making changes in the Control Panel for date and time, close and reopen your database to ensure the new settings take effect. I have occasionally forgotten this step myself and wondered why the format hadn't changed. The biggest advantage to switching to the ISO date format is that it removes all confusion about dates in international communication. Everyone, no matter what country they are in, can immediately recognize and use the same date format. The only real downside is having to get used to entering dates a new way. The transition takes a little time and practice. I am making this change myself and updating all my databases and systems so that going forward, all my materials use the ISO standard. Old records will generally update automatically to the new default, but remember to enter new dates starting with the year. To help yourself or your users adjust, you can set up an input mask in Access. This will force users to type in dates in the correct order, such as 2022-01-22. I have a separate instructional video on input masks if you want to learn more. Going forward, I will be using the ISO format in all new classes and videos to make sure everyone around the world is on the same page. As I discover more tips or run into further issues, I will post follow-up videos and updates to my website. That wraps up today's tip on how and why to set your computer to use the ISO date format. If you'd like to see a full step-by-step video tutorial covering all these instructions in detail, visit my website at the link below. Live long and prosper, my friends. Topic ListOverview of global date format issues and ambiguitiesExplanation of the ISO 8601 date format History and logic behind ISO date standards How to set ISO date format in Windows Regional Settings Differences between Short Date and Long Date in Windows ISO standard for time formatting, including 24-hour clock How to include time zones in ISO date and time Explanation of UTC and its notation in ISO dates Using ISO dates and times together in data fields Impact of changing date format on Excel data Impact of changing date format on Access data How custom date formats are affected in Excel and Access Best practices for entering new dates with ISO format Handling date input differences in Excel versus Access Considerations when importing dates from different formats Effects of Windows time settings on Access time display Requirement to restart Access after changing Regional Settings Advantages and disadvantages of switching to ISO date format Using input masks to enforce ISO date entry in Access |
||||
|
| |||
| Keywords: FastTips Fast Tips Excel iso 8601 advantages and disadvantages, global standard date and time format, international date standard, universal date format, short date, two digit hour iso excel PermaLink ISO Date Format in Microsoft Excel |
