Dates are one of the trickier things to work with in Excel. The first thing to try is to change the format of the cell – select the cell and hit Ctrl+1 to bring up the “Format Cells” option, and then click the “Number” tab if it’s not already selected. You should then see a “Category” list. Click “Date,” and you’ll see a lot of different options for formatting in the “Type” menu. Below those options, you should also see a “Locale” dropdown menu – changing this to “English (U.K.) and selecting the top entry from the “Type” menu should convert the text to the date format you’re after.
Sometimes this doesn’t help though and the cell remains formatted as text. In this case, you can either use the “date” function to convert the text to date (which can be pretty tricky), or better still, use “Text To Columns.” You can find this in the Data ribbon. After selecting the column that contains the cell you want to convert, make sure “Delimited” is selected and click next. After that, uncheck any delimiters that are already selected. Click Next. Now you should see a screen with an area called “Column data format” – click “Date”, and select DMY (Day, Month, Year). Then click “Finish.” This should convert the entire column to the “Date” format. Note that you can only apply this to one column at a time and that all cells in the column will be affected. After you convert the text to date, you may still need to select the cell and go through the first set of steps (converting the format to UK) to get the date to display the way you want. Hope that helps.