Forum Discussion
Date format
I would like to know if someone can assist me with the date formatting in excel. I am in South Africa and the date system we use is day, month, year, however when I type, 1/2/23 it comes and January second and I need it to be February 1st.
Thanks so much for the assistance
RobynBird23 Check you regional system (i.e. Windows) settings and make sure that you choose the locale that suits your requirements.
On my W11 system the date format for the South African locale is yyyy/mm/dd. However, the UK local does use dd/mm/yyyy.
So, pick the UK one if it's the same on your system. Now, Excel should automatically use that date format. No need to fiddle with custom formats, every time you enter a date.
7 Replies
- Riny_van_EekelenPlatinum Contributor
RobynBird23 Check you regional system (i.e. Windows) settings and make sure that you choose the locale that suits your requirements.
On my W11 system the date format for the South African locale is yyyy/mm/dd. However, the UK local does use dd/mm/yyyy.
So, pick the UK one if it's the same on your system. Now, Excel should automatically use that date format. No need to fiddle with custom formats, every time you enter a date.
- RobynBird23Copper ContributorWhere would I go abouts changing this? Thanks for responding so quickly
- Riny_van_EekelenPlatinum Contributor
RobynBird23 Can only refer to W11.
Settings, Time & Language, Language & Region, Regional Format
Now choose a locale that suits your needs.
- ExcelonlineadvisorIron ContributorIn Excel, you can change the date format to display dates in the day, month, year format (DD/MM/YY) to match the South African date format. Here's how to do it:
1. Select the cell or cells containing the dates that you want to format.
2. Go to the "Home" tab in the Excel ribbon.
3. In the "Number" group, you'll find a drop-down list for different number formats. Click on the drop-down arrow.
4. From the list of number formats, scroll down or search for "More Number Formats."
5. In the "Format Cells" dialog box, go to the "Number" tab.
6. In the "Category" list on the left, select "Date."
7. In the "Type" list on the right, choose the date format that matches your preference. In your case, you should select "Date" or "Custom" and then use the format "DD/MM/YY."
8. Click "OK" to apply the new date format.
After performing these steps, the date "1/2/23" should now be displayed as "01/02/23," which represents February 1st, 2023, in the day, month, year format you require.
Keep in mind that changing the date format only affects how the date is displayed in the cell; it doesn't change the underlying date value. The underlying date value remains the same, and you can continue to work with it in calculations or use it for date-based functions.- RobynBird23Copper ContributorThank you so much for the above message. however, when I put it on date format dd/mmm/yy, and I type 1/5 and press enter , it displays as 5/Jan/2023/
it worked on my old laptop, now that I changed it I cant seem to correct it.- ExcelonlineadvisorIron Contributor
You need to change your Laptop date format then as suggested by RobynBird23.
Pls Follow
Setting >> time & language >> Region >> Change data formats >> Change "short date & Long date"
(external links removed by moderator)