Buzz
The reversed date format in Excel, where the month comes before the day, can be puzzling for Vietnamese users. Interestingly, it's not a glitch but a result of the distinct date writing conventions between foreign countries and Vietnam. To address this, follow the steps outlined below by Mytour.
The default mode for the Date format in Excel always arranges the date as month, day, and finally, year. This is also the way dates are written in English in Western European countries.
Because of this, when writing dates in Excel, you will notice the automatic reversal of the month and day.
Only in cases where you input a 'day' greater than 12 does your date remain unswapped. This is because Excel fails to recognize the cell as a date cell (since no month exceeds 12) and treats it as regular data instead.
Indeed, you can observe this by examining the cell type in the Home ribbon under the Number category. The cell type remains General instead of changing to Date like other cases.
To correctly input dates in the Vietnamese order, you have two options.
Option 1: Enter dates as character data, not as date cells.
With the default setting of date formatting in Excel, arranging the month, day, and then year, whenever you input numerical values in the format of day-month-year (with slashes or dashes in between) into a data cell, Excel automatically converts it to the Date data type, recognizing that the month precedes the day.
So, if you don't need to use that data cell as a Date type but only want to use it to jot down dates for recording purposes (not related to calculations), you can leave it as is in the General character data cell format.
To achieve this, you need to input a single quote ' before entering the date. With a ' at the beginning, Excel treats the content following it as characters, and the cell becomes a normal character data cell in the General format.
Option 2: Change Excel's Date Writing Convention
This method provides a thorough and practical solution for all Excel date usage scenarios. You can revert to the previous date writing style that suits you; here, we'll use the Vietnamese format, which is day-month-year.
During the process of altering Excel's date writing convention, Mytour employs the TODAY function for verification.
The TODAY function aids in extracting the date-month-year at the moment you use this function (it relies on the date-month-year in your computer system). The formula for the TODAY function is:
=TODAY()
Initially, when using the TODAY function, you'll observe that the default arrangement of the date is with the month preceding the day, and the year at the end (Example: December 25, 2019).
To modify the date writing method in Excel, click on the expanded icon of the Number section in the Home ribbon.
The Format Cells dialog box will appear, allowing you to actively change the data type in the cell.
Click on the Date row in the Category list, then change the current Locate setting to Vietnamese.
Next, in the Type list, you'll see various date formats with day preceding month, following the Vietnamese style. Choose a display format you prefer and click OK to confirm the settings.
Afterwards, try the TODAY function again, and you'll observe a distinct outcome compared to the initial result before resetting the date writing style.
However, a crucial note to remember is that this setting is purely personal. It only alters the style for the selected data cells, meaning that apart from those cells, the date writing style will remain in the original reversed fashion.
Thank you for reading the article from Mytour on fixing the reversed date issue in Excel. We hope the article provides sufficient information for you to understand this trick and apply it to your data files.
