Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dates in incorrect format

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Dates in incorrect format

Resolved · High Priority · Version 2007

Reply Wed 28 Jan 2009, 16:01Delegate Tom said...

Tom has attended:
Excel Advanced course

Dates in incorrect format

I have a list of dates that are in the incorrect (i.e. American) format e.g. 12/16/2008, that I need to convert into the British version e.g. 16/12/2008. Normally I would change to UK in the date format tab, however these entries have been formatted as text not date so doing this has no effect.

Furthermore, I need to have the corresponding year in a seperate column but for those dates that are the wrong way round, the YEAR function does not work!

Any ideas?

Cheers
Tom

For upcoming training course dates see: Pricing & availability

Reply Fri 30 Jan 2009, 13:20Trainer Amanda said...

RE: Dates in incorrect format

Hi Tom

Thank you for your question.

Would it be possible for you to send a small sample of your data so we can have a play around with it here and see if we can get something to work? If so please email to amanda

If it's not possible to email anything, please reply in kind to this post.

Kind regards
Amanda

Reply Mon 23 Feb 2009, 09:51Trainer Amanda said...

RE: Dates in incorrect format

Hello Tom

Thanks for sending through a sample of dates.

I've been able to extract the day, month and year from the original date using the MID function; then combine the date, month and year together again in UK format using the DATE function.

I've attached an example so you can see how the MID and DATE functions are used.

To transpose the dates from columns to rows, copy the dates then select the cell that will represent the starting point for the dates once they are pasted in. Go to the Home ribbon, click the dropdown arrow under Paste Special, and select Paste Special.

Select Values and Number Formats, and tick the Transpose box. Click OK.

I hope this helps.
Amanda

Attached files...

US to UK dates.xls

Mon 2 Mar 2009: Automatically marked as resolved.

Excel articles

· Tools From Advanced Excel Courses: Goal Seek and Solver
· How To Create A Real Line Graph In Excel 2010 Rather Than A Line Chart
· Sorting Birthdates Into Their Respective Months In Excel
· Using The Excel Forms Toolbar To Spruce Up A Workbook
· Microsoft Excel provides the building blocks of shared information

Please browse our web site to find out more about
advanced+excel+courses and other Microsoft training courses.

Excel tip:

Counting Non Number Cells (Text)

If you try to use the COUNT FUNCTION =COUNT(Cell range)with a range of cells with numbers and or containing text fields you wil find that that the text cells will be excluded from the the count. If you want to include them try the the COUNTA FUNCTION =COUNTA(Cell range). This counts both text and number cell values.

View all Excel hints and tips