Buzz
When working with Excel spreadsheets, you may occasionally encounter incorrectly formatted dates, where the dates seem to jump unexpectedly despite your accurate data entry.
For example, you may intend to input dates like: 20/11/2018 or 06/12/2018... However, in Excel, when you enter them again, you notice they display as 11/20/2018; 12/6/2018
Fixing the automatic date jumping error in Excel
So, in this scenario, you need to fix the incorrect date format to overcome the issue and achieve the desired outcome. This article introduces 2 methods for you to rectify this issue when using Excel 2016 installed on Windows 10.
HOW TO FIX DATE JUMPING ERROR IN EXCEL
Method 1: Rectifying date format errors in Excel using Format Cells
Follow these steps to proceed with this method:
Step 1: Select the cell requiring formatting, then right-click and choose Format Cells
Step 2: On the Format Cells toolbar => select the Number tab => choose Date as shown below:
Step 3: Under Locale => select Vietnamese. In the Type section, click on 14/03/2012, then press OK as depicted below.
And here's the desired outcome after you reformat using the Format Cells feature.
Method 2: Fixing date format errors in Excel by changing the Region and Language settings in Windows.
Date formatting discrepancies in Excel stem from differences between Vietnamese and English-speaking countries such as the UK and the US. By adjusting the Region and Language settings in Windows, you can easily resolve this issue.
Step 1: Access the Control Panel by pressing the Windows + R key combination, then type Control Panel and press Enter or click OK as shown below:
Step 2: After accessing the Control Panel, you move the mouse and select Clock and Region:
You then proceed to select Region
Step 3: Within the Region interface, switch the Format from English (UK) or English (US) to Vietnamese (Vietnam) to display correctly according to Vietnam's time format and numerical symbols.
In the image above, under Short date and Long date, you can set the time format to dd/MM/yyyy (Day/month/year) to suit your needs, or you can also choose other formats if desired.
And the result will be displayed correctly as per Method 1 I just guided you through above.
So, I've just guided you on how to fix the date jumping issue in Excel. Simply follow the troubleshooting steps outlined in either of the two methods above, and you'll be able to swiftly address this basic Excel date issue. Wishing you success!
In addition, you can also refer to the method of fixing date changes when copying dates to another Excel file if you encounter this issue while copying data between Excel files.
