UK date formatting in MS Excel 2007

5 pts.
Tags:
Date format
Excel Date
Microsoft Excel 2007
Microsoft Excel formatting
As I enter a date in UK format in Excel 2007 it is taken as text.  The date I entered is 23/12/2009. How to get it as a date.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following