how to change format of date from mm/dd/yyyy to dd/mm/yyyy in MS Excel

Question

The format is not changing from mm/dd/year (e.g 08/28/18) to dd-mm-year (e.g 28-08-2018) it is not updating, just staying the same. please assist.

Answer 1

Your date is not currently being stored as a date value and is most likely text. You can test this out in several ways:

1. Select the cell and change the format to general. If is now shows a number then the date stored in the cell is numeric in value and we be subject to date change formats. If it remains as is then is is text.
2. Assuming the date is in cell A1, ISTEXT(A1). A TRUE result means its text.
3. Assuming the date is in cell A1, ISNUMBER(A1). A FALSE result means its text.

You can use the DATEVALUE function to convert TEXT dates to excel date values. however you need to be very aware of the date value you are starting with. This also is conditional with your system date settings.

A more generic and guaranteed method is to strip out the text for day, month, year, and toss them into the DATE function. Your life will be a bit easier since you have leading 0s. Assuming a date in cell B2 your formula could look like the following:

Day:
MID(B2,4,2)

Month:
LEFT(B2,2)

Year:
RIGHT(B2,2)

Now that you have striped out all the specific text as needed, drop the appropriate formula into the DATE function

DATE(YEAR,MONTH,DAY)

DATE(RIGHT(B2,2),LEFT(B2,2),MID(B2,4,2))

Apply custom formatting to the cell where the is located:

dd-mm-yyyy