Forum home » Delegate support and help forum » Microsoft Excel Training and help » Anthony, any luck on the date problem
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Anthony, any luck on the date problem
Resolved · Low Priority · Version 2003
Reply Wed 10 Feb 2010, 16:26Delegate Peter said...
Peter has attended:
Excel Advanced course
Anthony, any luck on the date problem
Anthony, any luck on the date problem
For upcoming training course dates see: Pricing & availability
RE: Anthony, any luck on the date problem
Hi Pete, thanks for your query. This is the formula you need:
=IF(LEN(A1)=6,(MID(A1, (LEN(A1)-5),2)) & "/" & (MID(A1, (LEN(A1)-3),2))& "/" & RIGHT(A1,2),(MID(A1, (LEN(A1)-4),1)) & "/" & (MID(A1, (LEN(A1)-3),2))& "/" & RIGHT(A1,2))
Paste into an empty cell, replace all references to A1 with the reference of the cell at the top of the dates and then Autofill down. The result will be the numbers in date format regardless of days with one or two figures. I found copying the results and then pasting them back in as values, then reformatting them as Dates worked well, but try reformatting into Long Date format. Excel will turn the last 10 into 2010. After that the data should be back in date format entirely.
Hope this helps,
Anthony
|