Buzz
- NOTE: Download Now: 70+ Basic and Advanced Excel Exercises
Are you facing date and time issues with Excel? Have you set the date and time format as dd/mm/yy in the cell's format? However, when entering a date like 01/12/2013, it displays correctly. Yet, if you enter a date >12 (e.g., 20/1/2013), it doesn't conform to your desired format.
Guide to Fixing Date and Month Errors in Excel
The reason is that the date and month settings on your Windows system are configured as mm/dd/yyyy (default for English-speaking users). Consequently, Excel adopts this format for the new data you input, even if you've customized the cell's format.
Specifically, as shown below:
Or like this
How to Resolve:
Step 1: You can directly access the Control Panel on your computer using the shortcut on the desktop.
Or press the Windows + R key combination > type Control Panel and press Enter or click OK to access the Control Panel on your computer.
After accessing the Control Panel on your computer, navigate as follows: click on Clock, Language, and Region >Region.
Access Clock, Language, and Region
In the Region interface, switch Format from English (UK) or English (US) to Vietnamese (Vietnam) to display in the correct Vietnamese time format and numeral symbols.
In the Short date, Long date section, you can set the time format to dd/MM/yyyy (Day/month/year) for compatibility, or choose other formats if desired.
- The Customize Regional Options window appears, select the Date tab. Set the date format in the Short date format section.
+ yyyy-mm-dd: Year-month-day (Example: 2016-11-21)
+ dd-mm-yyyy: Day-month-year (21-11-2016)
+ dd/mm/yyyy: Day/month/year (21/11/2016)
+ d/m/yy: Example: 2/6/99 (Meaning June 2, 1999)
+...
Step 2: Customize the date and month format in Excel
- Select one or more cells that need a date and month format adjustment in Excel, right-click, and then choose Format Cells...
- The Format Cells dialogue box appears. In the Number tab, choose Date (date format) and select the date format type in the Type section that suits your usage purpose:
- Press OK
Above, we've guided you on how to fix date and month errors when inserting into Excel spreadsheets. This issue may arise from incorrect time settings on your computer and various other factors. Resolving this problem enhances your effectiveness in learning Excel. While using Excel, you are likely to encounter many different errors. Therefore, articles on tips for fixing date and month errors in Excel are crucial, especially for beginners. Dealing with Excel errors is a practical experience that helps you improve your computer and Excel skills.
Additionally, for many users, differences in versions and interfaces make it challenging to operate, not to mention distinct functions. For Office 2016, Excel users have additional functions like CONCAT, TEXTJOIN, IFS that its predecessors lack. To learn more, follow our article on Excel functions exclusive to Office 2016 that we introduced earlier.
If you encounter any issues related to this article or in Excel, feel free to share with us.
